Issue with a cached connection from Access to SQL

NNOAM1
NNOAM1 used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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.

Author

Commented:
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?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
The other option (and a better one), is to use trusted connections to SQL.   That way, you avoid the whole username/password problem.

Jim.

Author

Commented:
In this company, we're allowed to use SQL Server Authentication only.
John TsioumprisSoftware & Systems Engineer

Commented:
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...
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
<<In this company, we're allowed to use SQL Server Authentication only.>>

 That;'s too bad....is there a specific reason they do this?    Generally unless your working with multiple domains, your better off with trusted connections.   The problem with multiple domains is that you then need to setup trust relations between them, so most then rely on SQL server logiins.

 If your stuck with that, then I would do as John suggests and just stick the UN/pswd in the connection string.  

 Jim.

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial