Connection to a Linked Table

[scking]

I have an app that is migrating from Access .mdb to a linked connection with SQL Server. The security workgroup still requires a logon but the linked tables use a DSN connection with their own UID and PWD (ODBC). How does the Access front end with security work with the SQL Server links. When I log in as 'guest' on the front end does the linked table connection use that username/password combination in any way?

In Access the front-end and back-end used Access security where the group inherited permissions to manage links, queries, tables, modules, etc. Now I can't seem to make the front-end/back-end security transparent.

Any help would be appreciated.

Steve King

---------------------
scking@arinc.com
---------------------

 

[MinusM]

The Dsn uses a UID and PWD which is completely indepedent of the Access Security.They are entirely different entities.

The question is more of a SQL Server Security issue than Access one. So the posting here may not be appropriate.I can tell you what I know
Your SQL Server connection must be getting authenticated by the WINNT Authentication. That is if you are logged on to Windows as a user who has authentication to the SQLServer all those permissions are carried to the linked tables.

You will have to play with Windows user permissions. Or a workaround is have SQL Server users same as access users provided SQL Server has mixed Mode Authentication. Remove permissions for WINXp Users and use DSN without Trusted Connection. Tedious Business :-(

Best of luck

 

[scking]

Yes, I know its tedious. Combine that with the lack of documentation covering the subject of:

Access Security (Login) >> ODBC Connection Security (Login) >> SQL Server Security (Login)

I would upgrade to an .adp project quickly but there is a great deal of DAO recordset management which doesn't operate in the .adp environment.

Thanks for your comment.




---------------------
scking@arinc.com
---------------------

 

[cmmrfrds]

The easiest way to link (if you must) sql server tables in access is to have the dba create 1 NT account/login and password for the access app, then when linking the tables there is a checkbox option to save the password. For an ADP NT authentication is the best way to go.