Save password for ODBC connection to MS SQL server from MS Access 2007

I am in charge for migrating old Access 2007 project to MS SQL server 2008 Express. The first stage is to move all the data from MS Access database to SQL server while keeping the Access forms and reports at the client.

So, the data are now moved, an SQL server user (for accessing only that particular database) created, and the tables are linked the Access database via ODBC connection. However, there's one nuisance that should be somehow solved: the Access regularly asks for the user password, when opening the Access database.

The users on server PC and client PC both log onto their local machines, ie their users are not verified on independent domain server.

I see there are several ways how to solve this:

• 1) Configure the integrated security model so that the user can log on, being automatically authorized by his Windows login (ie, use "trusted connection"). I am not sure how this could be done, given that the server PC does not recognize the user from client PC. If I try to do this now, I get error that the user is connecting from untrusted domain.
• 2) Store the SQL server user password at client side. I am not sure this is possible, though. I am aware keeping password in some config file, or stored obfuscated in application configuration should be considered lowering security, but this is acceptable for the given setup.
• 3) Perhaps some other way how to link the SQL server tables into Access?

Solution 1:

The best solution is obviously to use Windows security.

If that is not suitable, here is a possible alternative trick, exploiting the fact that Access remembers all opened connections until the program is closed:

1. copy the connect string of one of your tables
2. create a passthru queries "ptqConnect" and enter any fast SQL statement in it, like SELECT 1
3. paste the connect string of into the PTQ Connect property, and make sure you add the PWD=something; in it.
4. in the startup procedure of your app make sure you call that PTQ. Something like DCount("*", "ptqConnect") will do.

That's it. Since Access remembers opened connections until you close it, even if you close the db, your other tables will now open without any fuss, even if no password is stored in the linked tables Connect string.
If you don't want to expose the connection string that includes the PWD, you could as well initiate a connection from VBA and hide the code by delivering a MDE or just password protecting the code.

You can find an explanation of this behaviour here.

Solution 2:

Inform the users your organization's security policy prohibits storing passwords. Therefore they must provide their password each time they open the database. Explain that this policy will deny an unauthorized user the ability to open the database from an authorized user's machine. If the password were stored in any fashion, a miscreant could simply sit down at an unattended machine and open the database.

Since you can't use a trusted connection, this is the safest way to do it. Yes, the users will have to supply their password each time they open the database, but that's what it takes to keep the data safe.

Edit: Since your option #2 is acceptable, you can just store the uid and pwd in the connection strings for the ODBC-linked tables.

Here is an example copied from connectionstrings.com

Driver={SQL Server Native Client 10.0};
Server=myServerAddress;
Database=myDataBase;
Uid=myUsername;Pwd=myPassword;

I split the single line string for browser display. You'll need to also identify which table each of the links points to; examine your current link connection strings to see how that is done.

The uid and pwd will be plain text, and visible to anyone who can view the connection properties. But I didn't see any indication those are concerns for you.