使用 SQL 凭据通过 OpenRowSet 打开文件 [英] Using SQL Credential to Open a file with OpenRowSet

问题描述

我正在尝试运行以下语句:

I am attempting to run the below statement:

INSERT INTO table SELECT * FROM 
OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;
Database=//server/folder/file.xls;
HDR=YES;',
'SELECT * FROM [Sheet1$]')

但是，我收到以下错误:

However, I am receiving the below error:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine cannot open or write to the file '\\server\folder\file.xls'. It is already opened exclusively by another user, or you need permission to view and write its data.".

Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

我在 32 位机器上运行 SQL Server 2005.正在执行的代码最终将来自 IIS6 Web 服务器上的 c# 代码.但是，目前我只是想让它在 SQL Server 上运行.我使用 SQL Auth 登录到 SQL Server，但必须使用特定于共享驱动器(我们网络上的 AD 帐户)的 Windows Auth 访问该文件.为了允许 OPENROWSET.

I am running SQL Server 2005 on a 32 bit machine. The code being executed will eventually be from c# code behind on a IIS6 web server. However, currently I'm just trying to get it to run on the SQL Server. I'm logged into the SQL Server using SQL Auth, but the file must be accessed using Windows Auth specific to the share drive (an AD account on our network). Ad-Hoc priveleges have been given to the SQL Auth account in order to allow OPENROWSET.

我尝试将 UID=user;PASS=pswd 添加到 OPENROWSET 代码中，如下所示:

I have tried adding UID=user;PASS=pswd into the OPENROWSET code as below:

INSERT INTO table SELECT * FROM 
    OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;
    Database=//server/folder/file.xls;
    HDR=YES;
UID=user;
PASS=pswd',
    'SELECT * FROM [Sheet1$]')

这产生了同样的错误.我还设置了一个 SQL CREDENTIAL，其中包含用户 ID 和 Windows auth Active Directory 帐户的通行证，可以访问该共享驱动器，但错误仍然存​​在.

This yielded the same error. I also set up an SQL CREDENTIAL with the user id and pass of the windows auth Active Directory account with access to that share drive, but the error remained.

我考虑过使用代理，但这不是 sql server 代理工作.这是一个临时电话.

I considered using a proxy, but this isn't an sql server agent job. It's an adhoc call.

我还使用 Windows Auth 登录到 SQL Server，该用户拥有共享权限，但遇到相同的错误.

I have also logged into the SQL Server using Windows Auth with a user that has rights to the share, and get the same error.

我对此进行了大量研究，但似乎找不到有效的答案.通过我的搜索，这似乎是很多人都有的问题.任何帮助将不胜感激.我被难住了.

I've researched this a lot, but can't seem to find an answer that works. Through my searches, it seems to be a problem many people have. Any help will be greatly appreciated. I'm stumped.

推荐答案

我很确定，当您使用 OpenRowSet 时，SQL Server 的服务用户需要访问该文件.

I'm quite sure that when you use OpenRowSet that it's the Service user for the SQL Server that needs access to the file.

而且由于您似乎正在尝试访问另一台服务器上的文件，如果 SQL 服务器作为本地系统或其他本地用户帐户运行，则要使其工作可能是一项挑战.

And since it appears that you are trying to access a file on another server, it can be a challenge to get it to work, if the SQL server is running as Local System or another local user account.

我通过将 SQL 服务器更改为以 AD 用户身份运行(具有安全隐患)，然后让该用户访问网络上的文件，或放置外部文件(在此案例 Excel)与 SQL 服务器在同一台计算机上.

I have several times solved this by either changing the SQL server to run as a AD user (with the security implications it has) and then give that user access to the file on the network, or placed the external file (in this case the Excel) on the same computer as the SQL server.

这篇关于使用 SQL 凭据通过 OpenRowSet 打开文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！