Forcing Access to forget a username and password for a linked table

Question

I have an MS Access database that is connected to SQL server via linked servers.

The linked tables are added using a modified AttachDSNLessTable procedure:

stConnect = "ODBC;DRIVER=" & stDriverName & ";SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
Set td = CurrentDb.CreateTableDef(stLocalTableName)
td.SourceTableName = stRemoteTableName
td.Connect = stConnect

CurrentDb.TableDefs.Append td

I have a facility within the application to change the logged in user, this will remove all the tabledefs:

For Each td In CurrentDb.TableDefs
    If td.Name = stLocalTableName Then
        CurrentDb.TableDefs.Delete stLocalTableName
    End If
Next

Then it will re-add using the procedure above.

Now this appears to work however if I log in as user1 then change user to user2 without closing Access the connection is made using the user1 credentials, running a view that includes SUSER_NAME() shows user1 is the logged in user.

Is there any way to force the connection to be reset or to force the user to be changed?

Edit

My entire login function:

Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stDriverName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String

    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
            Exit For
        End If
    Next

    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=" & stDriverName & ";SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=" & stDriverName & ";SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If

    Set td = CurrentDb.CreateTableDef(stLocalTableName)
    td.SourceTableName = stRemoteTableName
    td.Connect = stConnect

    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = ""
    Exit Function

AttachDSNLessTable_Err:

    AttachDSNLessTable = err.Description

End Function

@AndyG There is a 'Change User' option ont he Navigation panel, this re-opens the login form. When the user logs in it removes the tabledef objects using the second block of code and re-adds them using the new User/Pass however it's actually opening the connection using the original user/pass. — bendataclear, Nov 15 '18 at 10:29
Also, is a general user able to use this facility? Should they be able to switch username without first exiting? — Andy G, Nov 15 '18 at 10:29
Access is a poor fit for this, but, in my opinion, your general User / Joe should not be able to switch user without exiting Access. Otherwise, as mentioned, it is during the process of changing user that the connection can be reset. — Andy G, Nov 15 '18 at 10:32
@AndyG I agree, Access is a poor fit but it's not possible to change it. Do you know how to reset the connection? — bendataclear, Nov 15 '18 at 10:35
I don't see where the issue is: you have code above for AttachDSNLessTable that uses `stUsername`. Effectively, you would run the code, or similar code, again but with the new username. Good luck. — Andy G, Nov 15 '18 at 10:39
@AndyG I am running the code again, the tabledef is being successfully removed and re-added however Access decides to use the original connection details not the new provided details. — bendataclear, Nov 15 '18 at 10:43
I would investigate `CurrentProject.CloseConnection`. Maybe someone else will contribute. Good luck. — Andy G, Nov 15 '18 at 10:55
After your code has run deleting the table links look in the system tables to see if there is something remaining in there, this may only be removed by closing/compacting maybe. `SELECT MSysObjects.Name, MSysObjects.Database, MSysObjects.Type FROM MSysObjects WHERE (((MSysObjects.Type)=4 Or (MSysObjects.Type)=6)); ` — Nathan_Sav, Nov 15 '18 at 11:11
I've had a very similar issue. Access maintains ODBC connections in the background, and my attempts at closing them remain unsuccessful thus far. I've also noted version differences, in Access 2010 the connection seems to close if no objects use it while in Access 2016 it remains open until a timeout passes. You can use a second ADODB connection to identify the connection Access is using and kill it, but I deemed that too dirty to properly implement it. — Erik A, Nov 15 '18 at 11:26
@Nathan_Sav The objects don't appear in the list after the table is deleted so the delete seems to be working. If I stop the procedure and close the database at that point then the table is no longer there. — bendataclear, Nov 15 '18 at 11:34
After login, you should change the connectionString followed by updateing `.connect` and `.RefreshLink` for all linked tables. This forces new connectionString to be used. — Krish, Nov 15 '18 at 12:11
i would also modify login or logout function when called, all opened objects are closed with or without saving. — Krish, Nov 15 '18 at 12:13
@krishKM Wouldn't I need to supply a valid connection string for it to be saved? — bendataclear, Nov 15 '18 at 12:14
You already have the updated connectionstring after login. Relinking process would mean `tdf.connect = UpdatedConnectionString: tdf.RefreshLink` — Krish, Nov 15 '18 at 12:15
@ErikvonAsmuth This seems to be what I'm discovering sadly, do you know what this timeout is? If it's small I could wait for it to pass then re-add the connections? — bendataclear, Nov 15 '18 at 13:34
@bendataclear It's quite long, I know the refresh interval is 1500 seconds by default. I don't think you can make that work. — Erik A, Nov 15 '18 at 13:37
@ErikvonAsmuth Yes that's pretty unworkable, thank you anyway. — bendataclear, Nov 15 '18 at 13:42

score 1 · Answer 1 · edited Nov 15 '18 at 19:28

EDIT

Took me a while to realise it. Your issue is that Access caches connections per {server,database} base. There is no way of clearing this cache (as far as i know of)

However there is a wayaround: The answer is to make the connection unique even if the server or db details haven't changed.

Using DSN Files You cannot change database name or server name to make a connection unique, but you can change DSN file name which Access will see the connection as "unique" / new connection.

If you use the same DSN file, the connection becomes cached again, so you must use different DSN file for each login attempts.

Here is a general function which allows a user to logIn or logOut of your system. Each login will produce a new DNS file and make a new connection to your DB => allowing your logged in user to be the new user.

Below functions are just conceptual. Change according to your needs and add error trappings.

Public Function FN_CREATE_DNS_FILE()


    Const Server    As String = "" ' Server
    Const Driver    As String = "" ' Driver
    Const Port      As String = "" ' Port
    Const Database  As String = "" ' Database

    Dim DsnFileName As String
    Dim Fso         As Object
    Dim DnsFile     As Object

    Set Fso = CreateObject("Scripting.FileSystemObject")
    DsnFileName = VBA.Environ$("temp") & "\" & VBA.Format(Now(), "yyyy-mm-dd_hh_mm_ss") & ".dsn"

    Set DnsFile = Fso.CreateTextFile(DsnFileName)
    DnsFile.WriteLine "[ODBC]"
    DnsFile.WriteLine "DRIVER=" & Driver
    DnsFile.WriteLine "PORT=" & Port
    DnsFile.WriteLine "DATABASE=" & Database
    DnsFile.WriteLine "SERVER=" & Server

    'Close file and clean up
    DnsFile.Close
    Set Fso = Nothing
    Set DnsFile = Nothing

    FN_CREATE_DNS_FILE = DsnFileName

End Function

Public Function LogOut()

    Dim Qdf         As QueryDef

    For Each Qdf In CurrentDb.QueryDefs
        If (VBA.InStr(Qdf.Connect, "ODBC")) > 0 Then
            Qdf.Connect = "ODBC;" 'Either delete if you don't require this object or set to blank connection string
        End If
    Next Qdf

End Function



Public Function LogIn(stUsername As String, stPassword As String)

    Dim Tdf         As TableDef
    Dim Qdf         As QueryDef
    Dim stConnect   As String
    Dim ConForQuery As String
    Dim I           As Integer: I = 0

    Dim DsnFileName As String

    On Error GoTo AttachDSNLessTable_Err
    'Produce new DNS file with new filename to make Acces Connection unique
    DsnFileName = FN_CREATE_DNS_FILE()

    stConnect = "ODBC;AUTO_RECONNECT=1;NO_PROMPT=1"
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = stConnect & ";Trusted_Connection=Yes"
        ConForQuery = stConnect
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = stConnect & ";UID=" & stUsername & ";PWD=" & stPassword
        ConForQuery = stConnect & ";UID=" & stUsername
    End If

    ConForQuery = ConForQuery & ";" & "FILEDSN=" & DsnFileName
    stConnect = stConnect & ";" & "FILEDSN=" & DsnFileName

    On Error GoTo ERROR_Invalid_login
    'Update all linked tables
    For Each Tdf In CurrentDb.TableDefs
        If (VBA.InStr(Tdf.Connect, "ODBC")) > 0 Then
            Tdf.Connect = stConnect & ";TABLE=" & Tdf.Name
            If (I = 0) Then Tdf.RefreshLink 'Refreshing one table is enough as long as table definition hasnt changed
            I = I + 1
        End If
    Next Tdf

    'update all passthrough queries
    For Each Qdf In CurrentDb.QueryDefs
        If (VBA.InStr(Qdf.Connect, "ODBC")) > 0 Then
            Qdf.Connect = stConnect
        End If
    Next Qdf

    LogIn = ""
    Exit Function

AttachDSNLessTable_Err:
    LogIn = Err.Description
    Exit Function

ERROR_Invalid_login:
    LogIn = "Login failed"
    LogOut 'Delete or set blank for all pass through queries
End Function

if a user logs in you would simply call

LogIn(Username, password)

which will update all linked tables as well as passthrough queries.

here is a screenshot.

QryCurrentUser executes MySQL command select user(); which shows the owner of the current connection. As you can see, each login shows now correct logged in usernames.

If login fails, you have two options. Delete All linked in tables and passthrough queries. or set them to a blank connectionstring.

PS I have added NO_PROMPT & AUTO_RECONNECT to the connectionstring which prevents showing database connection window when login fails and re-connects automatically (MySQL command, not sure if it works for MSSQL) remove them if not compatible.

Let me know if you were able to achieve this.

I've added in my login function to the question to clear things up. — bendataclear, Nov 15 '18 at 12:50
I'm removing the tabledef and re-adding to avoid saving the username and password into the database. Otherwise when it's closed and re-opened, the file tables retain the last connected user, this is a security risk. — bendataclear, Nov 15 '18 at 12:52
@bendataclear you don't need to provide username and password for all active connections. You can connect only one table, rest of the tables don't need username and password. — Krish, Nov 15 '18 at 12:57
Sorry I don't follow, will calling `Tdf.RefreshLink` use the new username and password? Because removing and re-adding the Tdf doesn't. — bendataclear, Nov 15 '18 at 13:10
@bendataclear. yes. by default linked in tables don't save password. Close your db without deleting the tables. open access and don't login. now check the .connect property of a linked tables. you won't see password or userid. — Krish, Nov 15 '18 at 13:18
however, passthrough queries do save passwords. you can prepare 2nd conSring without username and password and update all passthrough queries this will use cached connection from your active tables. — Krish, Nov 15 '18 at 13:20
This doesn't work, I've changed this over to set the `Connect` for the TableDef then call `RefreshLink`, I've checked the TableDef is showing the new user after however the connection is still using the original credentials when tested with `SUSER_NAME()`. — bendataclear, Nov 15 '18 at 13:33
`TableDef is showing the new user after` via .connect? or when you hover over the table? @bendataclear — Krish, Nov 15 '18 at 13:39
how strange.. it works for me. I just added `;TABLE=&stRemoteTableName` in your stConnect and tried with two different database in one server. I get correct database result according to the username and pw @bendataclear — Krish, Nov 15 '18 at 13:58
[screenshot](http://puu.sh/C2bw0/4c7b1b66d3.png) using your own function just added Table=&stRemoteTableName to the end of stConnect @bendataclear — Krish, Nov 15 '18 at 14:10
Here's what i get [screenshot](https://imgur.com/a/yWq0Ggm). Note - I'm not changing the database, this might force a reset of the connection settings. — bendataclear, Nov 15 '18 at 14:35
I had an access database with tables linked from the same DB2 database. One group with a system DSN with one credentials and the other group with the same system DSN with other credentials. When I tried to open them, it would open the tables on the first group but not on the second (it caches the username / password). The only way I was able to make them work was to create one DSN for each group of tables. I can now open both tables in the same session. — Jose R, May 08 '19 at 02:13

score 0 · Answer 2 · answered Nov 15 '18 at 19:18

A couple ideas to try. I only suggest these because they are easy to check.

Try renaming the old table first, then create the new table, then delete the old one. If you trick Access into thinking you are adding rather than replacing, it may cooperate.
Try adding another element to the connection string (other than UID, which Access treats in a special way) to make it unique per user. I believe you can add an arbitrary tag/value pair to an ODBC connection string and it gets ignored. For example
```
"ODBC;Driver=SQL Server;MyUniqueTag=" & stUserName & ";UID=" & stUserName
```
ODBC connection pools are unique per connection string, so this may fool Access into using a different connection pool because it doesn't know that MyUniqueTag isn't used by the ODBC driver.

I tried this approach for a DB2 database where I needed two sets of permissions but Access would remove the unique tag upon linking the table. — Jose R, May 08 '19 at 02:14

Forcing Access to forget a username and password for a linked table

2 Answers2

Linked