Home / server / Questions / 114384
In Process
Brad Knowles
Asked: 2010-02-19 12:05:56 +0800 CST

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

  • 772

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)
sql-server-2005 microsoft-access linked-server

2 Answers

  1. 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
    • 3

  2. You appear to not be distinguishing between the database password (first introduced in Jet 3.0, the version of Jet in A95) and a Jet user-level security username/password. Your examples are all the latter, but you mention database password in your question. Only OLEDB supports that (ODBC does not), so you need to check http://www.connectionstrings.com/access for the OLEDB connect string with database password.

    • 0