无法查询SQL链接服务器->远程MS Access，仅适用于服务器 [英] can't query SQL linked server -> MS Access remotely, only works on server

问题描述

我正在研究一个C#应用程序，该应用程序查询远程服务器(LAN)上的SQL Server 2008 Express实例.

I'm working on a C# application that queries a SQL Server 2008 Express instance on a remote server (LAN).

当我在工作站/dev机器上时，我可以打开SQL Server Management Studio并通过Windows身份验证连接到SQL实例，并且出于所有目的和目的，体验与在服务器"上的SSMS中工作是相同的.据我所知，我可以在工作站上执行服务器上可以执行的任何操作.

When I am at my workstation/dev machine, I can open SQL Server Management Studio and connect to the SQL instance through Windows Authentication, and for all intents and purposes the experience is identical to working in SSMS "on" the server. As far as I can tell, I can do anything from my workstation that I'd be able to do on the server.

同一台服务器托管一个Access 2003 .mdb文件，该文件在SQL实例中设置为链接服务器.

That same server hosts an Access 2003 .mdb file, which is set up as a linked server in the SQL instance.

有问题的查询返回结果集，这些结果集是通过联接SQL db和Access db中的表来编译的.

The queries in question return result sets compiled by joining tables from a SQL db and the Access db.

当我在服务器上执行查询时，也就是说我正在服务器计算机上运行SQL Server Management Studio时，一切都会按预期进行.但是，当我尝试从开发机上运行相同的查询时，无论是在SSMS中还是在尝试运行集成测试或调试应用程序时，对于链接的服务器我都无法执行任何操作.我可以在SSMS中看到它，但是任何尝试执行任何操作的结果都是:

When I execute a query "on" the server, which is to say that I am running SQL Server Management Studio on the server machine, everything works as expected. When I try to run the same queries from my dev machine, however, whether in SSMS or when attempting to run integration tests or debug the application, I can do nothing at all with the linked server. I can see it in SSMS, but any attempt to execute anything results in:

无法为链接服务器'>'初始化OLE DB访问接口提供程序'Microsoft.Jet.OLEDB.4.0'的数据源对象.

Cannot initialize the data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0' for linked server '<DatabaseName>'.

我浏览了许多与此错误相关的线程，并尝试了许多提供程序设置的排列，但无济于事.

I have looked through many threads relating to this error and have tried many permutations of provider settings, all to no avail.

• Access DB没有密码(我尝试将安全性设置为使用不带密码的名称"Admin")
• 工作站用户帐户对Access DB所在的服务器目录具有完全权限
• 服务器与Win XP Pro SP3经历了相同的行为，现在与Win Server 2003经历了相同的行为
• 客户端与Win XP Pro SP3经历了相同的行为，现在与Win 7 Pro x32经历了相同的行为

在服务器上运行良好，永远不会远程运行.

Works great on the server, never remotely.

不胜感激.

推荐答案

按照Burnsys的建议，使用SQL Server身份验证而不是NT Auth似乎可以解决问题.问题的 为什么 似乎是一个悬而未决的问题.

Using SQL Server authentication instead of NT Auth, as suggested by Burnsys, seems to do the trick. The why of the matter seems to be an open question.

这篇关于无法查询SQL链接服务器->远程MS Access，仅适用于服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！