Go to Top

Securing your ADO connections

A while ago, I wrote about securing the ODBC connections which was published on Access team’s blog. Also, Juan recently wrote about the alternate method of deleting/recreating linked tables in a four part article. But what about ADO connections? A typical approach for creating an ADO connection may look like this:

(simplified for example)

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

We may supply the connection parameters to the ConnectionString property and whenever we do use SQL Server authenication, the username and password are also embedded in the ConnectionString property. The biggest problem with this is that once the connection is opened, those username and password are still easily accessible through even unrelated objects. To provide the example, we’ll use a recordset to obtain that information:

So though we reused an ADODB connection and didn’t refer the original connection string, it was still implicitly available once we assigned the existing ADODB connection object to the recordset and could read the full connection string, with user id and password included. That’s a big security hole. The connection string may have had changed slightly from what you had put in, displaying additional information such as using more specific version of driver, additional parameters of the connection but what really matter is that user id and password is still in there.

Don’t persist the sensitive information

Fortunately, this is easy to rectify. Simply add this parameter to all your ADO connections:

The effect of including “Persist Security Info=false” instructs ADO library to discard the user id and password from the connection string after the connection has been successfully opened. Thus, in the OpenMyConnection, the user id and password is embedded in the connection string but only until the line “Con.Open”. Afterward, the connection string will not display that information so it cannot be extracted later.

Thus, using “Persist Security Info=false;” for all your ADO objects in conjunction with the suggestions provided for managing linked objects and ODBC objects, you can achieve a database application that won’t store the sensitive authentication information anywhere.

But what about connection string being stored?

I definitely would not recommend storing the user id and password in the database application. A syntax like this:

is vulnerable because it can be read, even when you use a MDE/ACCDE file, store it as a private variable in a private module. If at all possible, have the user log in with a dedicated login so it does not have to be stored. However, that is not always practical, and sometime you may use a shared SQL login. In the latter case, borrowing Patrick Wood’s approach may be more preferable. He discusses using a function to build the authentication information. This way, the connection string isn’t directly stored and must be constructed, which makes it a bit harder to extract.

4 Responses to "Securing your ADO connections"

  • Ryan
    November 3, 2016 - 12:40 am Reply

    This doesn’t appear to work via a SQLOLEDB or SQLNCLI10.0 provider in Access 2003. I can still see the User ID in the connect string but not the password (the password didn’t show even without the Persist Security Info flag).

  • Jude
    June 28, 2013 - 8:41 pm Reply

    Wonderful goods from you, man. I’ve understand your stuff previous to and you are just extremely wonderful. I really like what you have acquired here, certainly like what you are saying and the way in which you say it. You make it enjoyable and you still care for to keep it smart. I cant wait to read far more from you. This is actually a tremendous website.

  • Giorgio Rovelli
    June 4, 2012 - 3:28 pm Reply

    Hi Ben, when you use Patrick’s procedure to produce passwords, how do you use it in the procedure arguments, as Patrick says? And since it’s not advisable to store the password, how do you retrieve it once it’s created?

    • Ben Clothier
      June 4, 2012 - 3:54 pm Reply

      You’d probably do this:

      con.ConnectionString = PartColor(““)

      As for storing the password – that’s basically why I recommend against it because in the end of day, it’s security through obscurity. The idea is that you wouldn’t have a constant or function that’s obviously named “strPassword” or “MyConnectionString” but rather that you have a function named “PartColor” and a constant named say, “strSerialNumber”. By making them apparently unrelated, it’s not as obvious how you would obtain the password and may just frustrate enough to protect the application. Of course, a more dedicated & savvy hacker can simply work around that problem.

      You can further obscure the connection string password by using an algorithm that pulls together data from random bits of application (e.g. form’s name, lastupdated property of unused local table, etc.) then hashing it so you don’t have to hardcode it anywhere inside the VBA module or even on form properties. (Note: Tag property is perhaps even worse place since it can be not only read but also edited).

      I hope that helps.

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
  • This field is for validation purposes and should be left unchanged.