Link to home
Start Free TrialLog in
Avatar of NNOAM1
NNOAM1Flag for Israel

asked on

Issue with a cached connection from Access to SQL

Hello dear friends.
Recently I've tried to follow the instructions regarding "Cached connection", described in this article:
https://www.microsoft.com/en-us/microsoft-365/blog/2011/04/08/power-tip-improve-the-security-of-database-connections/
and indeed - the "miracle" did happen:
All my linked tables from Access to SQL Server now have a connection string which doesn't reveal anything about the username & password. Tables use the cached connection which has been prepared when the application was opened.

Now I have to include in this Access application some tables which reside in another database, on the same SQL Server. They won't use the cached connection.
1. Is there a way to create another cached connection for these new tables? without "harming" the first cached connection?
2. If these tables use a different connection string (so they won't use that cached connection) - is it going to "harm" the cached connection used for the old tables in any way?

Thank you very much!
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

1. You can create DSN-less connections (which is what this old blog is about) to one or more databases
2. They don't "see" each other.
Avatar of NNOAM1

ASKER

Yes, Gustav, from the beginning this was done DSN-less. This isn't a problem. I've been creating DSN-less connections for years.
But this time the idea of Cashed Connection was implemented for the first time. All tables connect to SQL via this cashed connection. What will happen when we want to have another cashed connection for the tables linked to the other SQL database?
I guess that Access, of those connections created, will use that cached connection that matches the server address/hostname and the database name. Should be easy to check out.
The other option (and a better one), is to use trusted connections to SQL.   That way, you avoid the whole username/password problem.

Jim.
Avatar of NNOAM1

ASKER

In this company, we're allowed to use SQL Server Authentication only.
Maybe its better to use DSN-less connections with UserName/Password and focus more on the user Permissions/Accessibility...if you tighten up which user has access to which table and work on this you could have all the tables without trying to trick the system...
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of NNOAM1

ASKER

Yes, multiple domains. Thank you all for your help.