批量插入返回错误“访问被拒绝”。 [英] BULK INSERT returns error "Access is denied"

问题描述

运行批量插入

BULK INSERT MyDatabase.dbo.MyTable 
FROM '\\Mylaptop\UniversalShare\SQLRuleOutput.csv' 
WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

在远程SQL Server上，我收到此错误：

on a remote SQL Server I get this error:

无法批量加载，因为文件
\MyLaptop\UniversalShare\SQLRuleOutput.csv无法打开。
操作系统错误代码5（访问被拒绝。）。

"Cannot bulk load because the file "\MyLaptop\UniversalShare\SQLRuleOutput.csv" could not be opened. Operating system error code 5(Access is denied.)."

• 共享向所有人开放。


• 我已经在连接到同一共享的那个SQL Server上运行了PowerShell Invoke-SQLCMD 脚本，因此服务器可以看到


• 此大容量插入语句失败，并出现相同的错误，无论我是否直接在SQL Server上以T-SQL身份运行它，或通过PowerShell Invoke-SQLCMD （这是我的预期方法）。

• The share is open to all.
• I have run PowerShell Invoke-SQLCMD scripts on that SQL Server that where it connects to that same share, so the server can see the share.
• This Bulk Insert statement fails with the same error whether I run it directly as T-SQL on the SQL Server, or through PowerShell Invoke-SQLCMD (which is my intended method).

这个问题类似，无法批量加载。操作系统错误代码5（访问被拒绝。），但是就我而言，共享是向所有人开放的，因此权限不应该成为问题，共享肯定在

This question is similar, Cannot bulk load. Operating system error code 5 (Access is denied.), but in my case the share is open to all, so permissions shouldn't be an issue, and the share is definitely in a different place (my laptop for POC development) than SQL Server.

有人知道这个错误的发生原因或解决方法吗？

Does anyone know why this error is happening, or how to get around it?

谢谢

Conrad

推荐答案

运行MSSQL服务的Windows帐户必须可以访问该共享；通常是本地系统或网络服务（但可以是任何东西）。

The share must be accessible to the Windows account that the MSSQL Service is running under; typically Local System or Network Service (but could be anything). Its unlikely you have explcitly granted access to MYSERVER\LOCAL SERVICE to your local file share.

运行Powershell Invoke-SQLCMD并不能证明MSSQL服务有权访问MYSERVER rightsLOCAL服务。您的驱动器-这取决于您以何种身份运行Power Shell。

Running Powershell Invoke-SQLCMD doesn't demonstrate that the MSSQL service has rights to your drive - it depends what account you are running power shell as.

这篇关于批量插入返回错误“访问被拒绝”。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！