calling a uid and pwd file into an access db for a system dsn

Question

I have 5 different MS Access 2013 db files which connect to system dsn files(Linked Server) The machine/system dsn's are Oracle based but the issue I am running into is 1 my password isn't encryped and has to be changed every 90 days and 2 I have to open each file and save my password multiple times when its changed.

I want to have a secure location which has the credentials stored and pass this into access so that they are not visible to other users and not having to save my password on each access file and relink it.

I have googled for over 2 days but can't find anything other than looking at connection strings which still doesn't solve the problem. What do I need to look at to solve this?

Sorry I dont have code as I just use the linked tables wizard inside of ms access.

@Albert D I couldn't get your code to work but I have done the following which fixes the issue on the tables but not the Pass Through queries

Created a File DSN and linked some tables to the access database. Created an Excel File to store my UserName And Passwords but my credentials in the pass-through query still show which I am stuck on?

Option Compare Database

Function Connections()
On Error Resume Next
'delete query if exists
DoCmd.DeleteObject acQuery, "PTQ"
Err.Clear
On Error GoTo 0 '"on error" statement here

'GET EXCEL LOGIN DETAILS
Set xlsApp = CreateObject("Excel.Application")
Dim WkBk As Excel.WorkBook
Set WkBk = xlsApp.WorkBooks.Open(FileName:="C:\folderlocation\filename.xlsx")

Dim USERLIST As String
Dim PWDLIST As String

USERLIST = WkBk.Sheets(1).Range("A2").Value
PWDLIST = WkBk.Sheets(1).Range("B2").Value

If Not (xlsApp Is Nothing) Then xlsApp.Quit
'end excel stuff
Dim db As DAO.Database
Dim qdExtData As QueryDef
Dim strSQL As String
Set db = CurrentDb
'passthrough query statement
strSQL = "SELECT * FROM table"
Set qdExtData = db.CreateQueryDef("PTQ")

ServerName = "Server1"
qdExtData.Connect = "ODBC;DRIVER={Oracle in OraClient11g_home1};Server=" & ServerName & ";DBQ=Server1;UID=" & USERLIST & ";Pwd=" & PWDLIST & ""

qdExtData.SQL = strSQL
qdExtData.Close
db.Close
Set db = Nothing
End Function

I then Set up a runcode macro called AutoExec

Answer 1

Ok, first up? I would avoid (not use) a system, or a user DSN. The reasons are MANY, but these types of DSN's require not only a external reference, but also often require elevated registry rights. And worse yet, your password will be in plain view.

The best solution is to use what is called a DSN-less connection. And you can create these connections even without code!

And even better? If the server + database name is NOT changed, but ONLY the password? You can change the userID + password WITHOUT having to re-link the tables (ideal for you with a changing password, but NOT the server + database being changed).

Even better better? If you adopt a wee bit of "log on" code, then you don't have to store the password in the table links! What this means if someone decides to fire up access, and say import your linked tables? Well, for one, they will not be able to open the tables, and EVEN better yet the uid/password is NOT part of, nor is it stored in the connection string for each linked table. As noted, because of this, you can change the UID, and not have to re-link the tables.

The first step: First up, ALWAYS (but ALWAYS!) when you link the tables, use a FILE dsn. this is imporant since when using a FILE dsn in access, they are automatic converted to DSN-less connections for you.

In other words, if you link your tables with a FILE dsn, then once the tables are re-linked, then you can even delete or toss out the DSN. And this means you can deploy the linked database (front end) to any workstation. You will not have to setup a DSN on that workstation, and in fact don't have to setup anything at all. (you will as before of course require the oracle database driver).

What the above means is when a password is updated/changed, you could then simply roll out a new front end. In fact, you likely have some auto updating ability for your application. (and if you don't', then you could (should) cobble together a bit of code to do this for you). So, you should have some means to roll out a new version of your software. After all, you REALLY have to install your "application" on each workstation LIKE you do with all other software, right???

So, there are two parts here: Adopting a FILE dsn for the table links. As noted, once you do this, then you don't need the DSN anymore. This is great for distribution to each workstation.

Also MAKE SURE when you do link, you do NOT check the box to save the password. This is to ensure that uid/password is NOT saved in the connection strings.

So, if tables don't have uid/password, then how will they work? Well, what you do is execute a "logon" to the database in your startup code. Once you execute this logon, then all linked tables will work! It is this "small" bit of code that can read the uid/password you place in an external file. Where you place this uid/password is up to you. It could be embedder in the code, or even some external text file that you read on startup. And it could even be a local table in the front end. (this idea would work well if you have some kind of automatic update system for when you roll out the next great version of your software.

So, with the ability to execute a logon, and not having to re-link the tables, then we have a EASY means to change the password.

So, you have to: go dsn-less. Thankfully, access does this by default, but ONLY if you use a FILE dsn.

Get/grab the code to execute a logon to the database. In fact, you likly have to delete all your table links. Exit Access, then re-start Access. Now, run your logon code, and THEN re-link your tables (using a FILE dsn you make).

The code to execute a logon is this:

  Function TestLogin(strCon As String) As Boolean

     On Error GoTo TestError

     Dim dbs          As DAO.Database
     Dim qdf          As DAO.QueryDef

     Set dbs = CurrentDb()
     Set qdf = dbs.CreateQueryDef("")

      qdf.connect = strCon

      qdf.ReturnsRecords = False

      'Any VALID SQL statement that runs on server will work below.

      qdf.sql = "SELECT 1 "
      qdf.Execute

      TestLogin = True

      Exit Function

 TestError:
      TestLogin = False
      Exit Function

  End Function

The above of course requires a correctly formed conneciton string.

So, to make ALL of this work, you can adopt the FILE dsn, and use above. You will likly cobbile together some code to build you a connection string. Then add to your code some code to read an external ext file, or have the UID/password in some table. This logon code as per above has to run BEFORE any form or linked table is used or touched.

The whole process is simple, but only if you break down this into the correct steps.

How this logon trick, and example code works is not complex, but a full article explaining this approach is outlined here

Power Tip: Improve the security of database connections

https://www.microsoft.com/en-us/microsoft-365/blog/2011/04/08/power-tip-improve-the-security-of-database-connections/