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

Question

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?

Ophite

Answer 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.

Lamella

This is really the best solution. the beauty is you don't even have to include the user + password in the connection string. A small logon box at startup, execute the small pass though, and PRESTO- all linked tables now work. So not even a table re-linked is required. Of course a DSN less connection and some table linking code is required for the first time link or if one needs to switch between test and production server – Domino

I must add that the step 3 in your answer you must provide also UID parameter, not only PWD. The ODBC-linked tables do not store both UID and PWD, so those must be provided by VBA code, or the user will be faced with SQL Server Login dialog. – Ophite

Answer 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.

Lebensraum

It's a sound advice, but for now it's likely I'll have to change the password to one character. They feel like setting up a central domain server is too much a hassle to enable just a vanilla use of SQL server data from Access app on another PC. And I tend to agree, after all, I have a full control over the priveleges of that "unsafe" SQL server user, and the connection can also be limited by the local firewall on the server. – Ophite

The firewall issue went over my head. If the firewall allows connections from Bob's pc to SQL Server, how do it know Bob is the one sitting at Bob's pc, and not some shady character who sat down when Bob was called away unexpectedly and forgot to log out of his Windows session, or lock it? – Lebensraum

No, I was assuming you originally thought about some shady character using another PC in the LAN. Of course, the scenario you mentioned is a potential risk, but then again, the company has got more serious problem if shady characters are sneaking up unattended users' computers. – Ophite

I tested your proposed connection string, and it appears that Access 2007 doesn't store the uid and pwd parameters of the linked table ODBC connection string property. I can set them up, but when I read the property, those parameters are removed. So, I end up at even more miserable state, when user is prompted to enter both user name and password, and now it happens not only when starting the application but at every single recordset retrieval, whenever that might happen. If the connection string refers to certain DSN, then the password is stored in memory for the rest of application session. – Ophite

Answer 3

I have had this issue, with Access 2010, linking to SQL Azure, but it was very simple. When linking the tables, there is an tick box option with each table to save the password.

Relinking your tables and checking this option would sort the issue. It does give you warnings that this may not be secure, but not all databases contain confidential data.

Glidden

I have no Save Password tick box. And, when I try to update the connection string manually (TableDef.Connect = connectionString), it seems to ignore the pwd setting. – Cy

Answer 4

Just ran into this problem connecting remotely to my workplace sql server using Access. I have Access 2013, but I don't think it has made any changes to something so basic as as ODBC connection from 2010. Since it is not a trusted connection, yes, you will have to log into the server each time you connect to the database. This is just basic security; can't think why you would ever want an app to just connect from an untrusted network unchallenged. So, I expect to have to log in when opening the database.

What was driving me crazy, however, was that each and every time I tried to open a table, I was asked for the password, and not just once, but twice, and I have to use a 13 character password that was randomly generated at creation! So, needless to say, that was completely unacceptable.

Access keeps the connection information in the sys table MSysOBjects, but I doesn't store the password, at least not there. I use an access DB stored on a cloud server synced with my desktops so I can open a local copy rather than having to remote in to my work desktop. It's much faster this way.

But, using the db in Access as a local file means that I have keep a close eye on the DSN connection names. As long as they are absolutely identical on all computers, it works great. So, if named my DSN "ProductsDBIII" when I created it at work in the ODBC32 Windows tool, then I need to use that same name when creating it at home. The actual connection string will be different, but Access doesn't care about that. However, here's the trick: when I first the DB from home, for instance, after a day at work, then I have to refresh the connections in Access's Linked Table Manager. Just check the tables/views you need, or "Check All", and go. Access will make the connection--probably prompting you for the log in--and then quickly refresh the "connect" string field in the MSysObjects table because they will be different, at least if switching from trusted access.

Voila, no more single or double challenges every frickin' time I open a table. I will get asked once when I first make a connection the first time I open a table from the remote DB, but that's it.

Hope this helps someone.

Jim

Cundiff

Answer 5

Re the use of Passthrough QAuery to set ODBC connection.

The form quoted as the startup form in the Database options will fire BEFORE autoexec. So that form cannot/should not quote linked tables, or Leave that none; and set the form in autoexec.

Otherwise you will still be prompted for pwd for the ODBC connection

A typical problem scenario is using a Switchboard form with the table in the linked database

Flatting