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?