SQL Server LOGIN USER issue (1 Viewer)

[tt1611]

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

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.

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]

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]

damn connection pooling, no wonder IT hates it

 

[cheekybuddha]

Not sure what it has to do with connection pooling?

 

[tt1611]

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]

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]

Did you enter new details after closing and re-opening Access (not just the app) and still the previous details persisted?

Yeah - the login form is presented on every log in.

 

[tt1611]

Heres this trace.

Heres me clicking Save on the form

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

 

[cheekybuddha]

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.

 

[cheekybuddha]

Note the login user switch to someone else for the save transaction

Isn't that what you wanted?

 

[tt1611]

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]

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.

 

[cheekybuddha]

The app is quit with a docmd.quit button on my main menu

OK, so that only quits the current app, but not Access if another instance of Access is open.

 

[tt1611]

So i only do have the only one instance open but does application.quit do it as a safer option?

 

[cheekybuddha]

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]

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]

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]

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]

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.