This post about making ODBC connections more secure is provided by Access MVP Ben Clothier.
Now and then, we come to a point where we decide to upsize an Access database to a server-based RDBMS. The reasons for upsizing can vary, but a common theme is security. With a server-based RDBMS, we have more options for security than when we are working with a file-based data source. Even if security wasn’t the reason for upsizing, we do have an obligation to ensure that we develop a “good citizen” application and protect the company’s assets contained in the RDBMS. When our RDBMS supports Windows authentication (e.g., SQL Server, Oracle, FireBird), we have it easy – no username or password needs to be stored. But unfortunately, we don’t always have Windows authentication available, and even when it is supported, it may not be practical. This article focuses on the case where we have to pass in a username and password as a part of the connection string and we want to do so in a secure manner. The article also assumes we are using ODBC and DAO.
Holes in Data Source Names
By default, Access offers to set up a Data Source Name (DSN) when we want to create a new linked table. As a matter of development, the DSN is quite convenient, giving us a graphic method to quickly build and specify an ODBC connection string without having to remember all of the syntax and parameters. Unfortunately, when the time comes for deployment, using DSNs has a host of problems, especially security. Let’s quickly review a few of the security holes associated with using DSNs.
Figure 1
When we link a new ODBC table, Access defaults to not saving the password when we select a DSN. We have to select Save Password to do so, as seen in Figure 1. Recent versions of Access warn that the password will be saved as plaintext. If we ignore the warning and save the password anyway, the password is saved in the Access system table, MSysObjects, as plaintext. You can see an example of this in Figure 2.
Figure 2
Not saving passwords is not good enough
So, let’s elect not to save the password. Does that resolve the problem? Unfortunately, not necessarily. When we create a DSN, the data used to build connection strings is stored in a registry entry. Let’s see how the DSN for a MySQL database is structured. You can find it in the registry node shown in Figure 3.
Figure 3
Here, the password is stored in the registry as plaintext, so even though we didn’t check the Save Password option, and our Connect wouldn’t list Password anywhere in the database itself, it’s still there for the taking. Yikes. Even worse, storing passwords is a vendor-specific implementation. The PostgreSQL ODBC driver also stores passwords as plaintext, but the Firebird ODBC driver always encrypts the password. SQL Server ODBC drivers will never try to store a SQL Server authentication password, no matter what. Considering the large number of different ODBC drivers, including third-party drivers for the same RDBMS, I’d rather not have to track the specific oddities of each ODBC driver and accommodate each one of them as I move from RDBMS to RDBMS.
We can say, “Let’s not define the password in the DSN, requiring the user to complete the connection at runtime.” Indeed, we can do that, but we now run into a new problem that also plagues the SQL Server ODBC driver. Every time a user opens an Access object that either is, or depends on, an ODBC object, the user will be prompted to enter their password for that ODBC connection. This does not make for a great user experience, not to mention several additional issues raised by showing users the dialog box for configuring the ODBC driver, which exposes options you may not want users to tinker with.
Using DSN-less connection instead
So in short, using DSNs may be convenient, but it can be problematic to implement in a secure manner. Moreover, because the specifics of implementation vary by ODBC driver, there is no good general solution for designing a secure DSN. Therefore, we should consider DSN-less connection strings. Though DSN-less connections per se are not necessarily more secure, the additional security comes from the fact that they can be disposable, which becomes very important, as we’ll see shortly.
Access MVP Douglas J. Steele has posted a sample of building a DSN-less connection in VBA for a SQL Server backend. Doug recently updated the material, incorporating modifications suggested by George Hepworth, another Access MVP, to support both trusted connections and SQL Server authentication. This document provides us an excellent starting point to learn how we can build DSN-less connections. We’ll also learn how we can ensure that all linked tables will be updatable by adding a unique index local to Access.
Cached connection
There is an interesting behavior in Access we want to take advantage of. When Access opens an ODBC connection, it caches that connection. Any subsequent ODBC objects that happen to match on three parameters—ODBC driver, server, and database—will reuse that cached connection. This means we don’t have to specify the full connection string for all ODBC objects each time. We only need to supply the complete connection string once at startup and store only the incomplete connection string. We can then leave it up to Access to match subsequent ODBC objects to that cached connection string. This helps immensely in simplifying the security setup.
During application startup, we want to call a routine that will create a temporary query that contains the complete connection. Then we can discard that query at end of the routine. Procedure InitConnect demonstrates this crucial step.
Public Function InitConnect(UserName As String, Password As String) As Boolean
' Description: Should be called in the application's startup
' to ensure that Access has a cached connection
' for all other ODBC objects' use.
On Error GoTo ErrHandler
Dim dbCurrent As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
'<configuration specific to MySQL ODBC driver>
strConnection = "ODBC;DRIVER={MySQL ODBC 5.1 Driver};" & _
"Server=" & ServerAddress & ";" & _
"Port=" & PortNum & ";" & _
"Option=" & Opt & ";" & _ 'MySql-specific configuration
"Stmt=;" & _
"Database=" & DbName & ";"
Set dbCurrent = DBEngine(0)(0)
Set qdf = dbCurrent.CreateQueryDef("")
With qdf
.Connect = strConnection & _
"Uid=" & UserName & ";" & _
"Pwd=" & Password
.SQL = "SELECT CURRENT_USER();"
Set rst = .OpenRecordset(dbOpenSnapshot, dbSQLPassThrough)
End With
InitConnect = True
ExitProcedure:
On Error Resume Next
Set rst = Nothing
Set qdf = Nothing
Set dbCurrent = Nothing
Exit Function
ErrHandler:
InitConnect = False
MsgBox Err.Description & " (" & Err.Number & ") encountered", _
vbOKOnly + vbCritical, "InitConnect"
Resume ExitProcedure
Resume
End Function
Even though we discard the query at the end of the procedure, Access holds onto the connection it created behind the scenes. As far as I know, there is no programmatic access to this cached connection. However, once it exists, you can open any other ODBC objects that match on driver, server, and database parameters and interact with it as if it did have the complete connection string. Figure 4 shows a table opened without any additional prompt after we run the procedure at startup. Even though it does not have the complete connection string, Access used the cached connection created by InitConnect so there’s no need to prompt the user for missing information.
Figure 4
With this technique,
Windows 7 Professional, you can now implement a custom login form to have users enter their username and credentials at runtime and call the InitConnect procedure. That information is passed to the code snippet illustrated above to create the connection. You now have an application that does not store the password anywhere, inside or outside the file. Although that won’t stop your users from penciling their password on a note taped to their monitor, you can be confident that, if the file was copied and taken outside the building, it would be useless; more so if the connection requires being within a certain network. (Certain IP addresses such as 192.168.*.* are not world-accessible and require a physical connection to a particular DHCP server or at least VPN or similar tunneling software.)
Passthrough queries have a connection string, too
Figure 5
As Figure 5 shows, we should remember that linked tables aren’t the only objects to have a Connect property –passthrough queries can have a Connect property that also needs to be maintained. Fortunately, those queries can also share the cached connection so there should be no reason for us to store a complete connection string for them. We only need to ensure that all passthrough queries have the minimum of three required parameters and we’re done.
Closing one more hole
There is a hole we need to close – the cached connection does not close when we close the database but do not quit Access. In other words, if a user closed your application and opened their own databases, they wouldn’t be prevented from accessing the same ODBC objects using the cached connection. Fortunately, that is fairly easy to remedy – when the last form closes, forcing Access to quit will usually ensure that the cached connection is properly disposed of at the end of your application’s session. This guarantees that users will be always required to explicitly log in when they open Access and attempt to access the ODBC sources.
When you’ve implemented an incomplete connection string, the file no longer can be simply copied around. If someone attempts to bypass the login routine, the tables and queries will not be able to connect. Double-clicking on those objects would simply give them the ODBC driver’s dialog box asking them to complete the connection. Because the password isn’t stored anywhere, they can’t just look under the doormat. Though encrypting the password is an option, it’s much more preferable not to store any password and require the user to supply it just in time. Encrypting the password and storing it in the file is analogous to putting your confidential information in a small safe and leaving it out in your front yard. Anybody can just take your small safe somewhere else and take as much time as they want to crack the password. True security comes in not having anything available for taking and blocking access rather than making access harder.
Extra reading For additional security options, you may want to look into creating an MSysConf table on your RDBMS server to ensure that Access won’t offer to save passwords locally. For those who do not want to have users log in and enter a password, Access MVP Tom van Stiphout’s example of using Active Directory to provide user-level access control for Access forms can be used as a model for implementing authentication via third-party server (e.g., Active Directory). If you're just getting started with connect strings, you can go to Carl Prothman’s site and ConnectionStrings to find excellent repositories of complete connection strings, though you may want to go to your ODBC driver’s vendor for the most up-to-date and complete documentation. Finally, check out George Hepworth's site for lots of tips, demos, and downloads.
Note: If we create a File DSN instead of a System or User DSN, the data will be stored in a file rather than in a registry entry. However, File DSNs still store the data in plaintext, similar to what we saw in the registry. So the concerns for security apply equally to File DSNs and System/User DSNs.
--Ben Clothier
Ben Clothier has been an Access MVP since 2009. He is an independent contractor working with J Street Technology and Advisicon and was Technical Editor for the Access 2010 Programmer's Reference.
<div
Windows 7 Professional Power Tip Improve the secu
Linear Mode
