SQL data connections ODBC linked tables vs OLE DB (1 Viewer)

TraceSL

Registered User.
Local time
Today, 11:06
Joined
Sep 12, 2017
Messages
51
Hello:
We use Access as a report writer for one of our enterprise systems. The enterprise software vendor delivers their reporter software in Access in MDB format with ODBC linked tables. We began to use the save passwords option when linking the SQL tables via ODBC to bypass the need for the end users to enter passwords when running reports. However we have discovered the passwords are stored in plain text in a system table. Therefore we are reviewing the setup of the Access system using the SQL tables for "secure" practice with SQL connection passwords.

What is the preferred method for connecting a Access system to SQL tables for SQL password security. I understand we can great connection strings in VBA using ODBC or OLE DB driver references. I have begun to work on VBA scripts to use OLE DB but concerned about the impact to all the complex reporting already built in the database and the VBA code in use by the system written and delivered by the vendor. Also VBA may still show the password in plain text so the 'security issue' may not be resolved.

Can someone give me some insight about the SQL connections and best practices for this type of use?

Thanks Tracy
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:06
Joined
Oct 29, 2018
Messages
21,358
Hi Tracy. Have you seen this article?
 

TraceSL

Registered User.
Local time
Today, 11:06
Joined
Sep 12, 2017
Messages
51
DBguy: This is exactly what I need to address this issue, I was aware of the other security issue with the ODBC settings/registry entries on computers as well but yesterday when I looked it appears Microsoft may of fixed this registry problem. I am going to review this information now. Thanks for the quick response this is very helpful. Tracy
 

TraceSL

Registered User.
Local time
Today, 11:06
Joined
Sep 12, 2017
Messages
51
Is anyone familiar with using Windows active directory for the SQL connections, this is what we are interested in utilizing. Tracy
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:06
Joined
Oct 29, 2018
Messages
21,358
Is anyone familiar with using Windows active directory for the SQL connections, this is what we are interested in utilizing. Tracy
Basically, in the Connection string, you would have something like Trusted_Connection=Yes.
 

Users who are viewing this thread

Top Bottom