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.
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).
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.
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?
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.