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

Question

I am responsible for porting an old Access 2007 project to MS SQL Server 2008 Express. The first step is to move all the data from the MS Access database to the SQL server, saving the access forms and reports on the client.

So now the data is being moved, the SQL server user (to access only that particular database) has been created, and the tables are connected to the Access database through an ODBC connection. However, there is one nuisance that must somehow be resolved: Access regularly asks for the user's password when opening the Access database.

Users on the server PC and client PC are registered on their local computers, that is, their users are not checked on an independent domain server.

I see how this can be solved in several ways:

• 1) Configure the integrated security model so that the user can log into the system, automatically log in using his Windows login (ie use a "trusted connection"). I am not sure how this can be done, given that the server PC does not recognize the user from the client PC. If I try to do this now, I get an error that the user is connecting from an untrusted domain.
• 2) Save the SQL Server user password on the client side. I am not sure if this is possible. I know that storing a password in a configuration file or saving it in an application launched during configuration should be considered as a security decrease, but this is acceptable for this setting.
• 3) Perhaps in some other way to associate SQL server tables with Access?

Answer 1

The best solution, obviously, is to use Windows security.

If this doesn't work, here is a possible alternative trick using the fact that Access remembers all open connections until the program is closed:

• copy the connection string of one of your tables
• create ptqConnect passthru queries and enter any fast SQL statement in it, for example SELECT 1
• insert the connection string into the PTQ Connect property and make sure that you add PWD=something; .
• in the procedure for starting your application, make sure that you call this PTQ. Something like DCount("*", "ptqConnect") will do.

What is it. Because Access remembers open connections until you close them, even if you close db, your other tables will open without any problems, even if no password is stored in the linked tables. Connect string.
If you do not want to open the connection string, which includes PWD, you can also initiate a connection to VBA and hide the code by providing MDE or just a password that protects the code.

You can find an explanation of this behavior here .

Answer 2

Tell users that your organization’s security policy prohibits storing passwords. Therefore, they must provide their password each time the database is opened. Explain that this policy prohibits an unauthorized user from opening a database from an authorized user machine. If the password was saved in any way, the attacker could simply sit on the machine unattended and open the database.

Since you cannot use a reliable connection, this is the safest way to do this. Yes, users will need to provide their password each time they open the database, but this is what is required to ensure data security.

Change Since your option # 2 is acceptable, you can simply store uid and pwd in connection strings for ODBC-related 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 one line of line to display the browser. You will also need to determine which table each of the links points to; view your current link join strings to find out how.

uid and pwd will be plain text and will be visible to anyone who can view the connection properties. But I did not see any indication that this concerns you.

Answer 3

I had this problem with Access 2010 related to SQL Azure, but it was very simple. When linking tables, each table has a checkmark option to save the password.

Reusing your tables and checking this option will sort the problem. This gives you warnings that this may be unsafe, but not all databases contain sensitive data.

Answer 4

Just ran into this problem connecting remotely to my workstation sql server using Access. I have Access 2013, but I don’t think that it has made any changes to something as basic as the ODBC connection since 2010. Since this is not a reliable connection, yes, you will have to connect to the server every time you connect to the database. This is just basic security; cannot think why you will ever want the application to simply connect from an untrusted network without an answer. So, I expect that you will need to log in when opening the database.

What bothered me, however, was that every time I tried to open the table, I was asked to enter a password, not once, but twice, and I should use a 13-character password that was accidentally created when I created it! Therefore, of course, this was completely unacceptable.

Access stores connection information in the sys MSysOB files, but I do not store the password, at least not there. I use an access database stored on a cloud server that is synchronized with my desktops, so I can open a local copy and not delete it on the desktop. It is much faster.

But using db in Access as a local file means that I carefully monitor the names of DSN connections. As long as they are exactly the same on all computers, it works great. So, if I called my DSN "ProductsDBIII" when I created it at work in the Windows ODBC32 tool, then I need to use this 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 start the database from home, for example, after a working day, I have to update the connections in the Access Linked Table Manager. Just check the tables / views you need, or "Check All" and go. Access will lead to a connection - perhaps it will offer you to log in and then quickly update the line of the "connect" line in the MSysObjects table, because they will be different, at least if you switch from reliable access.

Voala, no more lonely or double problems, every time I open the table. They will ask me once when I first make a connection when I first open a table from a remote database, but what is it.

Hope this helps someone.

Jim

Answer 5

Repeat using Passthrough QAuery to establish an ODBC connection.

The form specified as the startup form in the database parameters will be triggered before autoexec. Thus, this form cannot / should not indicate related tables, or Do not leave it; and set the form to autoexec.

Otherwise, you will be prompted to enter pwd to connect to ODBC

A typical problem scenario uses a Switchboard form with a table in a linked database