Como eu crio um Servidor Vinculado no SQL Server 2005 para um banco de dados Access 95 protegido por senha?

Question

Eu preciso criar um servidor vinculado com o SQL Server Management Studio 2005 para um banco de dados do Access 95, que por acaso é protegido por senha no nível do banco de dados. A segurança no nível do usuário não foi implementada.

Não consigo converter o banco de dados do Access para uma versão mais recente. Está sendo usado por um aplicativo de terceiros; então modificá-lo, de qualquer forma, não é permitido.

Eu tentei usar o provedor OLE DB do Jet 4.0 eo provedor ODBC OLE DB. O aplicativo de terceiros cria um DSN do sistema (com a senha correta do banco de dados), mas não tive a menor sorte em usar nenhum método.

Se eu estivesse usando uma string de conexão padrão, seria mais ou menos assim:

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

Tenho quase certeza de que preciso incorporar Jet OLEDB:Database Password na configuração do servidor vinculado, mas ainda não descobri como.

Eu postei os scripts que estou usando junto com as mensagens de erro associadas abaixo. Qualquer ajuda é muito apreciada. Vou fornecer mais detalhes, se necessário, basta perguntar.

Obrigado!

Método 1 - usando o provedor do Jet 4.0 Quando tento executar essas instruções para criar o servidor vinculado:

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

Eu recebo este erro ao testar a conexão:

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)

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

Método 2 - Usando o provedor ODBC ...

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

Eu recebo este erro:

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)

    

Answer 1

Depois de horas lutando com esse problema, eu finalmente encontrei não uma, mas duas soluções! Estou usando o SQL Server 2008 e o Access 2000, mas acho que a solução é a mesma.

Solução 1: usar OPENDATASOURCE

Use essa abordagem quando você acessará os dados no servidor vinculado com pouca frequência:

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

Para que isso funcione, você também precisa ativar a opção Ad Hoc Distributed Queries :

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

Solução 2: usar servidor vinculado

Como você já declarou, o servidor vinculado parece não funcionar. No entanto, há uma pequena pegadinha que faz tudo funcionar sem problemas; você precisa especificar a senha do banco de dados na string do provedor como ;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

Agora você pode consultar seu banco de dados do Access como um servidor vinculado:

select * from TestLinkServer...MyTable

    

Answer 2

Você parece não estar distinguindo entre a senha do banco de dados (introduzida pela primeira vez no Jet 3.0, a versão do Jet em A95) e um nome de usuário / senha de segurança em nível de usuário do Jet. Seus exemplos são todos os últimos, mas você menciona a senha do banco de dados em sua pergunta. Apenas OLEDB suporta que (ODBC não), então você precisa verificar link para a seqüência de conexão OLEDB com a senha do banco de dados.