Sql-server – Windows logins are not supported in this version of SQL Server

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

Are you running the SQL Server on Windows Server 2003? This error 10054, means that the client connection reset the connection.

To work around this problem, disable the TCP Chimney Offload feature. To do this, follow these steps:

• Click Start, click Run, type cmd, and then press ENTER.

• At the command prompt, type the following command, and then press ENTER:

  Netsh int ip set chimney DISABLED

More information can be found here: http://support.microsoft.com/kb/942861/en-us