Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    San Jose
    Posts
    2

    Question Unanswered: Security for MS Access Frontend & SQL Server Backend?

    Hi, this is my first post (hopefully of many) on this board. Just wanted to say a quick hello before I dive into my question.

    As the title suggested, I have to develop a MS Access form app (yes, it has to be Access - I know it sucks) that will post and query data to and from a remote SQL Server db. While I have no problem linking the two through the default ODBC drivers, my question is security. Some (actually most all) of the data being passed back and forth is sensative information, and I would like to know the best way to keep it safe.

    If anybody has any suggestions, instructions, or can redirect me to a good FAQ site on the subject, I would be most appreciative. I have already done a search on these forums for an answer, but have sadly come up short.

    Thanks to all in advance!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Access doesn't suck. It is quite a powerful tool for rapid application development.

    Make your application an Access Data Project (.adp file) rather than a straight Access database. It will save you a lot of linking, reduce the amount of data transfered over your network, and allow you to use SQL Server's security. In my opinion, Access's biggest drawback is it's klunky security.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53

    Lightbulb Re: Security for MS Access Frontend & SQL Server Backend?

    Unfortunately an Access ADE is still not secure. We found that a simple text viewing tool "Quickview Plus" would display the connection string with the clear-text password (embedded in Access). When we contacted Microsoft their solution was to have us redevelop all of our applications in VB.

    My solution was to create a user table with encrypted passwords (using PWDENCRYPT and PWDCOMPARE) and a user priviledge level in SQL Server. Also created in SQL Server is an encrypted stored procedure that verifies the user's login/password combunation and returns the appropriate connection string (Read-Only, Read-Write...) as determined by the role.

    The initial connection string in Access is limited to include read-only priviledge to the user table and execute priviledge the encrypted stored procedure.

    The stored procedure will return to the Access application the appropriate connection string (as the returned value of the SQL statement) where it is passed as the baseConnectionString value.

    When the user exits the application the Access connection string is reset to Nothing and all access to the SQL database is removed.

    It works well for us.

    *note: We will likely be moving to an MD5 hash from PWDENCRYPT / PWDCOMPARE

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Good, because PWDENCRYPT / PWDCOMPARE are undocumented functions and are not guaranteed to work identically in future releases. Developers who have used PWDENCRYPT / PWDCOMPARE in the past have found that new releases use new encryption algorythms, and thus all the old stored passwords suddenly no longer work and have to be reset.

    I don't know what MD5 is, but custom one-way encryption algorythms are not difficult to write. I have one you can use, if you would like. I'm happy to share the code, because even if you know the code there is no way to decipher the encrypted passwords.

    If SQL Server's security is not suitable for your needs, then your solution sounds good. What I have done in the past for web-based applications where it was not feasable for each use to have a SQL Server login was to store UserNames and encrypted passwords, and then when the user logs in successfully they are given a unique token (using SQL Server's newid() function), and all the database stored procedures require that the token be passed as a parameter in order to execute. The token is stored in a logins table, and can be deleted when the user logs in again and receives a new token, or after a specified period of time.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •