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.