How do I create a Linked Server in SQL Server 2005 to a password protected Access 95 database?

Question

I need to create a linked server with SQL Server Management Studio 2005 to an Access 95 database, which happens to be password protected at the database level. User level security has not been implemented.

I cannot convert the Access database to a newer version. It is being used by a 3rd party application; so modifying it, in any way, is not allowed.

I've tried using the Jet 4.0 OLE DB Provider and the ODBC OLE DB Provider. The 3rd party application creates a System DSN (with the proper database password), but I've not had any luck in using either method.

If I were using a standard connection string, I think it would look something like this:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Test.mdb';Jet OLEDB:Database Password=####;

I'm fairly certain I need to somehow incorporate Jet OLEDB:Database Password into the linked server setup, but haven't figured out how.

I've posted the scripts I'm using along with the associated error messages below. Any help is greatly appreciated. I'll provide more details if needed, just ask.

Thanks!

Method #1 - Using the Jet 4.0 Provider When I try to run these statements to create the linked server:

sp_dropserver 'Test', 'droplogins';
EXEC sp_addlinkedserver @server = N'Test', @provider = N'Microsoft.Jet.OLEDB.4.0',
    @srvproduct = N'Access DB', @datasrc = N'C:\Test.mdb'
GO

EXEC sp_addlinkedsrvlogin @rmtsrvname=N'Test', @useself=N'False',@locallogin=NULL,
    @rmtuser=N'Admin', @rmtpassword='####'
GO

I get this error when testing the connection:

TITLE: Microsoft SQL Server Management Studio
------------------------------

"The test connection to the linked server failed."

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Test" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Test".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Test" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.". (Microsoft SQL Server, Error: 7399)

------------------------------

Method #2 - Using the ODBC Provider...

sp_dropserver 'Test', 'droplogins';
EXEC sp_addlinkedserver @server = N'Test', @provider = N'MSDASQL', 
    @srvproduct = N'ODBC', @datasrc = N'Test:DSN'
GO

EXEC sp_addlinkedsrvlogin @rmtsrvname=N'Test', @useself=N'False',@locallogin=NULL,
    @rmtuser=N'Admin', @rmtpassword='####'
GO

I get this error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

"The test connection to the linked server failed."

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "Test".
OLE DB provider "MSDASQL" for linked server "Test" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "Test" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "Test" returned message "[Microsoft][ODBC Microsoft Access Driver] Cannot open database '(unknown)'.  It may not be a database that your application recognizes, or the file may be corrupt.". (Microsoft SQL Server, Error: 7303)

Solution

After hours of struggling with this problem I finally found not one, but two solutions! I'm using SQL Server 2008 and Access 2000, but I guess the solution is the same.

Solution 1: Use OPENDATASOURCE

Use this approach when you will be accessing data on the linked server infrequently:

select * from 
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0', 'Data Source=C:\MyAccessDB.mdb;User ID=Admin;Password=;Jet OLEDB:Database Password=MyDBPassword;')...MyTable

For this to work you also need to turn on the Ad Hoc Distributed Queries option:

exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Solution 2: Use Linked Server

As you already stated, the linked server doesn't seem to work. However, there's a small gotcha that makes everything work smoothly; you need to specify the database password in the provider string like ;PWD=password.

exec sp_addlinkedserver 
    @server = 'TestLinkServer', 
    @provider = 'Microsoft.Jet.OLEDB.4.0', 
    @srvproduct = 'Access',
    @datasrc = 'C:\MyAccessDB.mdb', 
    @provstr = ';PWD=MyDBPassword'

exec sp_addlinkedsrvlogin 
    @rmtsrvname = 'TestLinkServer',
    @useself = 'FALSE',
    @locallogin = null, 
    @rmtuser = 'Admin', 
    @rmtpassword = null

GO

Now you can query your Access database as a linked server:

select * from TestLinkServer...MyTable

Comments

[Admin]

I can't answer the question because I've never created a linked server in SQL Server. However, the code in Method #1 is not using a database password, but a Jet user-level security password, which is inapplicable. That should be user=admin and no password at all (the default for all Jet/ACE connections). Secondly, ODBC cannot deal with database passwords at all. You need to find the correct syntax within SQL Server for assigning properties to the connection that would include the database password. Another alternative is to remove the database password, which is of no real value, anyway.

[shiraz]

I can't see how your second answer works, since PWD is the parameter for Jet user-level security, not for database passwords.

[MarioVW]

@DavidW.Fenton I really can't explain why this works, all I know is that it does work. You should give it a try if you are skeptical ;)

[shiraz]

Well, it obviously means that there is no database password involved in the first place.

[MarioVW]

@DavidW.Fenton the database does have a database password (no user-level security). I can manage to successfully connect to the same database-password protected .mdb database using BOTH methods. Seriously, you should give it a try and convince yourself; it shouldn't take more than 5 minutes of your time.