views:

636

answers:

1

I have an Sql Server database that includes a linked server to an MS Access mdb database. The Access database has workgroup security and requires a valid user name and password. This is easily set up in the linked server dialog in sql management studio. The problem is that the Sql Server needs to be able to find the MS Access mdw file to be able to login.

The only way I have managed to get this to work is by setting the Registry value SystemDB of the registry key :

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines

to the path of the workgroup file but this causes other problems (like with other MS Access apps or Excel).

Is there a better way of doing this?

+1  A: 

I haven't done this for a while, nor in recent versions of SQL server, but I seem to remember that when you add a link to a Jet database it provides the opportunity to define the workgroup, username and password. Certainly if you're using ODBC, that's part of the basic ODBC driver for Jet.

--
David W. Fenton
David Fenton Associates

David-W-Fenton
Thanks! The problem was I used the JET OLEDB provider which doesn't have an option for workgroup. When I switched to the "Microsoft OLE DB provider for ODBC Drivers" I could specify an ODBC DSN and in the DSN I could specify the workgroup file. Thanks a lot!
BTB