Solved Managing SQL Server Logins in MS Access Frontend

[Spalle]

Hello everyone,

I am currently working with a SQL Server backend that is connected to a Microsoft Access frontend. The frontend dynamically creates tables and queries as needed, so these objects are typically not stored permanently.

I set it up this way to ensure that not all users share the same SQL Server login. However, I've encountered a problem: once the Access frontend is connected to the SQL Server, it uses a single SQL Server login for the entire session. I am unable to change the login without closing the frontend.

Is there a way to resolve this issue? Perhaps a method to remove or change the connection through VBA?

Any guidance or suggestions would be greatly appreciated!

Thank you in advance for your help.

 

[cheekybuddha]

Is there a way to resolve this issue?

Yes, annoyingly Access caches the credentials for an ODBC connection.

The only solution (I have found) is to close Access completely and re-start.

Just closing the db within Access and restarting the db is not sufficient.

 

[cheekybuddha]

That said, your users shouldn't share the same copy of the frontend, so it's normally OK that they log in with their credentials on their own copy of the FE, and use it for their own session.

 

[Pat Hartman]

I guess I don't understand the problem unless you are allowing multiple users to share the same physical copy of the FE. Does each user have his own personal copy of the FE?

 

[cheekybuddha]

I guess I don't understand the problem unless you are allowing multiple users to share the same physical copy of the FE.

I guess it can be an issue if multiple users can use the same machine, and each machine has its own FE.

Also, I have an app to run ad hoc queries against different ODBC databases for testing purposes - I have to close out of Access to use different credentials, and I can't hit two different backends (with different credentials) during the same session at the same time.

 

[Pat Hartman]

I guess it can be an issue if multiple users can use the same machine, and each machine has its own FE.

How would they be open at the same time?

What happens if you have tbl1 linked using credentialsA and tbl2 linked using credentialsB? Will both tables be accessible to both users if the password is saved with the connection string.

 

[SQL_Hell]

Move to windows authentication with AD groups, then you won't have this issue.

 

[cheekybuddha]

How would they be open at the same time?

They wouldn't.

But, if you don't know about the caching issue requiring a full restart of Access, then the developer may code in a 'Disconnect' and 'New Login' style thinking that entering new credentials would work.

So User1 finishes session, 'logs out', but then leaves application open. User2 comes along and thinks they are logging in afresh. Just a possible scenario ...

 

[Spalle]

Okay, I see I'm not the only one with this problem. Thank you all for your help!

I think I'll try to find a workaround, or I might just close the frontend.

 

[GPGeorge]

Yes, annoyingly Access caches the credentials for an ODBC connection.

The only solution (I have found) is to close Access completely and re-start.

Just closing the db within Access and restarting the db is not sufficient.

I have to admit that I think credential caching is a plus not a negative. It allows us to create the link at startup without exposing credentials in the connection strings of linked tables and passthru queries.

I see not having to save SQL Server login and password in the linked table connection strings as a good thing.

If the credentials are in VBA in an accde or encrypted in VBA in an accdb, a start up routine can create the initial connection and users simply aren't able to see the log in or password. I realize that there are advanced methods that can bypass even that. For a typical group of users, though, it's a step more secure.

And I also agree that the "problem" is only going to appear if two or more users are able to use the same accdb Front End without ever closing it after themselves. And that is hardly a recommended practice in any scenario I can think of.

A video on using this technique to avoid exposing SQL Server credentials to casual users.

 

[cheekybuddha]

I have to admit that I think credential caching is a plus not a negative.

I agree.

The idea is good ...

... but the implementation is poor (not unusual!)

If the cache was cleared/reset upon the entering of new credentials everything would be as any rational developer/user would expect.

But no, you're stuck with something unintended until you close out of the whole program

 

[cheekybuddha]

or I might just close the frontend.

Note that just closing the front-end and leaving Access running is not enough.

You have to fully close Access as well

 

[GPGeorge]

I agree.

The idea is good ...

... but the implementation is poor (not unusual!)

If the cache was cleared/reset upon the entering of new credentials everything would be as any rational developer/user would expect.

But no, you're stuck with something unintended until you close out of the whole program

Hm. I never considered trying to reset the cache by linking to the same tables with a different set of credentials without closing Accss. Or linking to a different database during a session. Both are scenarios that probably have some real-world relevance. <Sigh> Another side project to explore.

 

[AHeyne]

Maybe the Access team would add a procedure like 'ClearCredentialCache'?

 

[GPGeorge]

Maybe the Access team would add a procedure like 'ClearCredentialCache'?

Give it a try. It can't hurt.

 

[AHeyne]

Done. But we all should do that, or second my request.

 

[AHeyne]

Forgot to post the link: https://feedbackportal.microsoft.com/feedback/idea/051eedaa-5068-ef11-a4e6-000d3a02a3a1

 

[AHeyne]

It looks like my suggestion has been completely deleted. Thank you Microsoft.

 

[Josef P.]

It looks like my suggestion has been completely deleted.

This does not appear to be an exception:

Another Day, Another Deleted Feedback Portal Suggestion!

Discussing how yet one more Feedback Portal Suggestion simply vanished along with all the votes it had accumulated. Is this Microsoft's engagement to improve.

www.devhut.net

 

[AHeyne]

Thanks @Josef P. , thats annoying.