Use Trusted Connection on SQL Link table

[Guest]

I have a SQL Server database that only has one user/password to allow access
to the tables in the db. When creating the DSN ODBC linkage to the SQL
Server, I use the WITH SQL SERVER AUTHENTICATION instead of the USE WINDOWS
AUTHENTICATION, and enter the username/password to test the connection, works
fine. However, in access, after I select to LINK a table from this SQL
Server database and create the link, whenever a form in the database tries to
open the connection, it gives an error stating the {DOMAIN}\{USER} password
is invalid. The 'Use Trusted Connection' box is defaulted to checked (YES)
in this box, so access is trying to use windows authentication on this DSN,
even though it is setup to use SQL Server Authentication. Is there any way
to tell access NOT to use the trusted connection? Is this maybe a windows
Registry setting that can be changed?
TIA for any help!

 

[Brian]

I have a SQL Server database that only has one user/password to allow access
to the tables in the db. When creating the DSN ODBC linkage to the SQL
Server, I use the WITH SQL SERVER AUTHENTICATION instead of the USE WINDOWS
AUTHENTICATION, and enter the username/password to test the connection, works
fine. However, in access, after I select to LINK a table from this SQL
Server database and create the link, whenever a form in the database tries to
open the connection, it gives an error stating the {DOMAIN}\{USER} password
is invalid. The 'Use Trusted Connection' box is defaulted to checked (YES)
in this box, so access is trying to use windows authentication on this DSN,
even though it is setup to use SQL Server Authentication. Is there any way
to tell access NOT to use the trusted connection? Is this maybe a windows
Registry setting that can be changed?
TIA for any help!

When you create a linked table, there is a "Save password" check box. Tick
it.

 

[Guest]

This does work, thanks! However, a question. Does this password being used
to create the connection get saved in an encrypted form? Are there any other
security concerns to doing it this way? Most every other place where we use
Windows Authentication, the "save password" option is by company policy not
to be used.

 

[Brian]

This does work, thanks! However, a question. Does this password being used
to create the connection get saved in an encrypted form? Are there any other
security concerns to doing it this way? Most every other place where we use
Windows Authentication, the "save password" option is by company policy not
to be used.

1. No, it isn't encrypted, it is clearly visible in the MSysObjects
table.

2. Whether this is a security issue depends on how secure you want to be.

3. It isn't a Windows password that's being saved, it's a SQL Server
password.

4. You can of course use Access security to prevent users reading the
MSysObjects table.

5. Bearing in mind, of course, that Access security is not industrial
strength! It will keep out the casual user, but it will not keep out the
determined!

6. One of the biggest problems with Access security is that it's not
difficult to hack an MDW file to get all the user names and passwords from
it. Tools that do this can be purchased very cheaply.

7. It is possible, however, to create an MDW file wherein none of the
users can read the MSysObjects table, and also none of them is able to grant
such privileges. This file would be used live, with another one being kept
under lock-and-key for maintenance/administration purposes. I do recollect,
however, that it is somewhat fiddly to do (as is everything to do with
Access security!) and I can't recall the details!