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)

Public Con As ADODB.Connection

Public Sub OpenMyConnection() As Boolean
   Set Con = New ADODB.Connection
   Con.ConnectionString = _
     "Provider=sqloledb;Server=myserver.com;Database=mydatabase" & _
     "...." 'additional parameters as appropriate
   Con.Open
   'Note the changes made after Con.Open to the Connection String
   Debug.Print Con.ConnectionString
End Sub

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:

Public Sub ShowIt()
  Dim rs As ADODB.Recordset

  Set rs = New ADODB.Recordset
  rs.Open "SELECT 1 FROM dbo.MyTable", Con
  Debug.Print rs.ActiveConnection.ConnectionString
  rs.Close
  Set rs = Nothing
End Sub

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:

  con.ConnectionString = _
    "Persist Security Info=false;" & _
    "Provider=sqloledb;Server=myserver.com;Database=mydatabase;" & _
    "...." 'additional parameters as appropriate

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:

Const strConnectionString = _
 "Provider=sqloledb;Server=myserver.com;Database=mydatabase;" & _
 "uid=foo;pwd=bar;"

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.