查询“Microsoft.ACE.OLEDB.12.0"时出错来自 SQL Server 的提供程序

2021-09-10 00:00:00 tsql sql-server

这不是问题，但这篇文章可能对社区有用，因为我在互联网上找不到答案.

This is not a question, but this post may be useful for community because I could not find the answer on the Internet.

经过测试

  • Windows 2008 R2 x64 和 Windows 2003 x64
  • SQL Server 2008 SP3 x64
  • 提供程序 Microsoft.ACE.OLEDB.12.0 x64

SQL Server 在非本地计算机管理员的域帐户下运行.SQL Server 连接身份验证是 Windows.

SQL Server is running under domain account that is not admin of local machine. SQL Server connection authentication is Windows.

问题是:

在使用此提供程序创建和使用链接服务器或临时查询时，您可能会收到如下错误:

when creating and using a linked server or adhoc querying using this provider, you may receive errors like these:

OLE DB 提供程序MSDASQL"用于链接服务器xxx"返回消息[Microsoft][ODBC 驱动程序管理器] 驱动程序的 SQLSetConnectAttr 失败"

OLE DB provider "MSDASQL" for linked server "xxx" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed"

OLE DB 提供程序MSDASQL"用于链接服务器xxx"返回消息[Microsoft][ODBC dBASE 驱动程序]您的网络访问被中断.要继续，请关闭数据库，然后再次打开.

OLE DB provider "MSDASQL" for linked server "xxx" returned message "[Microsoft][ODBC dBASE Driver] Your network access was interrupted. To continue, close the database, and then open it again.

无法初始化 OLE DB 提供程序MSDASQL"的数据源对象；对于链接服务器xxx"

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "xxx"

找不到可安装的 ISAM

Could not find installable ISAM

OLE DB 提供程序MSDASQL"用于链接服务器xxx"返回消息[Microsoft][ODBC dBASE 驱动程序]一般错误无法打开进程 0x16f4 线程 0x2728 DBC 0x1ce64ae8 Xbase 的注册表项临时(易失性)Ace DSN'

OLE DB provider "MSDASQL" for linked server "xxx" returned message "[Microsoft][ODBC dBASE Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x16f4 Thread 0x2728 DBC 0x1ce64ae8 Xbase'

原因是提供程序使用用户连接的凭据在 SQL Server 域帐户的临时文件夹中创建了一个临时文件.普通用户没有这样的权限.

The reason is that the provider creates a temporary file in SQL Server's domain account's temporary folder, using user connect's credentials. Ordinary users does not have such permissions.

推荐答案

解决方案是
授予 Sql Server 域帐户临时文件夹上的 sql server 用户修改"权限.这个文件夹通常是

The solution is
grant "Modify" permission to sql server users on Sql Server's domain account's temporary folder. This folder ordinarily is

  • Windows 2003:c:\Documents 和Settings\sql_server_account_name\Local Settings\Temp\"
  • Windows 2008:C:\Users\sql_server_account_name\AppData\Local\Temp\"

相关文章