SQL Server LOGIN USER issue (1 Viewer)

tt1611

Registered User.
Local time
Today, 10:43
Joined
Jul 17, 2009
Messages
132
Hi all
Wondering if I could get some help here for this very bizarre issue. I have created a login form for my FE Access App with a SQL Server BE. The user passes in their SQL Login and password and its authenticated against the server. I am using their ID for table audit purposes. When the user logs, in, their connection string is build dynamically using the connection string below
Code:
cs = "DRIVER=SQL Server Native Client 11.0;SERVER=SERVER\INSTANCE;UID=" & struser & ";PWD=" & strpw & ";APP=Microsoft Office;DATABASE=DBNAME;"
after which i reconnect to all linked tables in a loop and refresh the connection strings as below.

Code:
Set db = CurrentDb
            For Each tdf In db.TableDefs
                ' Only make a change if the table is a linked table
                If Len(tdf.Connect) Then
                    tdf.Connect = "DRIVER=SQL Server Native Client 11.0;SERVER=SERVER\INSTANCE;UID=" & struser & ";PWD=" & strpw & ";APP=Microsoft Office;DATABASE=DBNAME;"
                    tdf.RefreshLink
                End If
            Next

I have verified using SQL Profiler that all the linked tables and passthrough queries have their connections updated.
Problem im now encountering is when one of the main forms in the app is opened, when the save command runs either through a docmd.runcommand accmdsaverecord or by tabbing away from particular control, SQL sees the transaction (update) running through a previously logged in user account is using that ID as part of the audit. Even though i have verified that User A is logged into the app and all tables have had their connection strings updated, the transaction to update the form/record is being passed to SQL as User B. I have checked all the connections I'm using in the app through ADODB and DAO ensuring each one of them is closed and set to nothing after use so I'm really not sure how or where User B is getting cached in the system. I have found that after a couple of attempts logging in and out of the app, the correct user is passed to SQL but the cycle starts all over again when i log in as another user.
 

cheekybuddha

AWF VIP
Local time
Today, 15:43
Joined
Jul 21, 2014
Messages
2,387
Hi,

Annoyingly, Access caches the user/pw details for ODBC connections, so you can't just switch users within the app.

Once connected, those user/pw credentials will be re-used for any subsequent connections.

The only solution is to close Access completely and re-open 😖 😖 😖
 

Isaac

Lifelong Learner
Local time
Today, 07:43
Joined
Mar 14, 2017
Messages
8,967
damn connection pooling, no wonder IT hates it
 

tt1611

Registered User.
Local time
Today, 10:43
Joined
Jul 17, 2009
Messages
132
Hi,

Annoyingly, Access caches the user/pw details for ODBC connections, so you can't just switch users within the app.

Once connected, those user/pw credentials will be re-used for any subsequent connections.

The only solution is to close Access completely and re-open 😖 😖 😖
Which again after testing or quitting the app, I see the audit logout transaction in profiler but the user ID persists even after the app is reopened. Its like it stays persisted then clears out after a set time. So you're saying theres no workaround for this?
 

cheekybuddha

AWF VIP
Local time
Today, 15:43
Joined
Jul 21, 2014
Messages
2,387
I see the audit logout transaction in profiler but the user ID persists even after the app is reopened.
Did you enter new details after closing and re-opening Access (not just the app) and still the previous details persisted?
 

tt1611

Registered User.
Local time
Today, 10:43
Joined
Jul 17, 2009
Messages
132
1698775621245.png

Heres this trace.
1698775655278.png


Heres me clicking Save on the form
1698776207305.png




and heres the trace. Note the login user switch to someone else for the save transaction.
1698775754236.png
 

Attachments

  • 1698775705704.png
    1698775705704.png
    40.9 KB · Views: 81
  • 1698775929768.png
    1698775929768.png
    63.7 KB · Views: 71
Last edited:

cheekybuddha

AWF VIP
Local time
Today, 15:43
Joined
Jul 21, 2014
Messages
2,387
OK, sorry to ask again, but it's not 100% clear from your responses above: you do close Access completely (ie no Access in the taskbar or in Task/Process Manager), not just close your application within Access?

I have never seen the credentials persist after closing Access completely, however, maybe something has changed - I haven't used Access newer than Acc2007.
 

tt1611

Registered User.
Local time
Today, 10:43
Joined
Jul 17, 2009
Messages
132
Isn't that what you wanted?
No the user saving the record should be consistent with whos logged in (ie current user). Im going to be using that userID to audit changes to this table in an audit table. RoThomas is not the currently logged in user. That account logged in about 4 logins ago.
 

tt1611

Registered User.
Local time
Today, 10:43
Joined
Jul 17, 2009
Messages
132
OK, sorry to ask again, but it's not 100% clear from your responses above: you do close Access completely (ie no Access in the taskbar or in Task/Process Manager), not just close your application within Access?

I have never seen the credentials persist after closing Access completely, however, maybe something has changed - I haven't used Access newer than Acc2007.
No its ok and thanks again for all your help. Yes. The app is quit with a docmd.quit button on my main menu. Its after that quitting and relogging in back in using the login FE that im seeing this issue in SQL.
 

tt1611

Registered User.
Local time
Today, 10:43
Joined
Jul 17, 2009
Messages
132
So i only do have the only one instance open but does application.quit do it as a safer option?
 

cheekybuddha

AWF VIP
Local time
Today, 15:43
Joined
Jul 21, 2014
Messages
2,387
Application.Quit does the same thing as DoCmd.Quit

Are you passing any arguments to DoCmd.Quit (eg acQuitSaveAll) ?

I'm grasping at straws as to why the credentials cache isn't cleared when Access is closed completely.
 

cheekybuddha

AWF VIP
Local time
Today, 15:43
Joined
Jul 21, 2014
Messages
2,387
I have just re-read your first post and see that you re-link your tables.

I think you will need to actually delete them and re-add them rather than just changing the connection string and calling .RefreshLink

See here for an implementation
 

tt1611

Registered User.
Local time
Today, 10:43
Joined
Jul 17, 2009
Messages
132
Im starting to think this might be SQL caching the login credentials rather than Access. Im noticing that the queries running under the "wrong" USER ID are all service side system sprocs
 

cheekybuddha

AWF VIP
Local time
Today, 15:43
Joined
Jul 21, 2014
Messages
2,387
OK, you will have to try and narrow down what the exact issue is, or if there is more than one issue!!

Keep us updated with your progress.

Isaac may have been on to something - perhaps there is some connection pooling going on from the SQLServer end?
 

tt1611

Registered User.
Local time
Today, 10:43
Joined
Jul 17, 2009
Messages
132
HI. I wanted to come back and post a reply to say thank you and that i ended up deploying the app as is. There wasnt going to be any chance that a user would be logging into the app who wasnt already logged into the laptop. This scenario was only happening on my dev laptop. I plan to lock the usernames down and read it from environ("username") as this is the same ID that the SQL login is setup against.

Thanks again.
 

Users who are viewing this thread

Top Bottom