共享文件夹的 OPENROWSET BULK 权限

目标

使用 OPENROWSET 功能在针对文本文件的查询中加入数据.

错误

利用@gbn 对这个问题的回答 我正在尝试像 OP 一样打开一个行集；虽然文件的格式有点不同.但是，我在尝试访问共享文件夹时遇到以下错误:

<块引用>

消息 4861，第 16 级，状态 1，第 1 行

无法批量加载，因为文件MACHINENAMEShareEC04.txt"不能打开.操作系统错误代码 5(访问被拒绝.).

背景

请理解，我不也不会有权访问 SQL 服务器，因此我不能在那里放置文件.p>

文件位于 Windows 7 x64 机器上.

该文件夹已与 Everyone 共享为 Read/Write.

问题

谁能帮助我了解我需要提供哪些其他安全措施才能允许 SQL 服务器访问此文件夹?

解决方案

如果您以 SQL 登录名登录，那么您必须 为此登录创建凭据，并且此凭据必须具有足够的权限才能读取共享.

如果您以 Windows 登录名登录，那么您必须为 SQL Server 服务帐户启用 Kerberos 约束委派.

现在您似乎正在使用 Windows 登录，并且由于模拟上下文无法通过双跳"，身份验证解析为 ANONYMOUS LOGON，它不是每个人的成员，因此访问被拒绝.这一切正是预期的行为.请咨询您的网络管理员，了解如何为目标共享的 SQL Server 服务帐户设置约束委派.

OBJECTIVE

Use the OPENROWSET feature to JOIN data in a query against a text file.

ERROR

Leveraging the answer from @gbn on this question I am trying to open a row set just like the OP; though the format of the file is a bit different. However, I'm getting the following error trying to access a shared folder:

Msg 4861, Level 16, State 1, Line 1

Cannot bulk load because the file "MACHINENAMEShareEC04.txt" could not be opened. Operating system error code 5(Access is denied.).

BACKGROUND

Please understand, I do not and will not have access to the SQL server and so I cannot place a file there.

The file resides on a Windows 7 x64 machine.

The folder has been shared as Read/Write with Everyone.

QUESTION

Can somebody help me understand what other security I need to give to allow the SQL server to access this folder?

解决方案

If you are logged in as a SQL login then you must create a credential for this login and this credential must have sufficient privileges to read the share.

If you are logged in as a Windows login then you must enable Kerberos constrained delegation for the SQL Server service account.

Right now it seems you're using a Windows login and because the impersonated context cannot flow through the 'double hop' the authentication resolves to ANONYMOUS LOGON, which is not member of Everyone, hence the access denied. All this is exactly the expected behavior. Consult your network administrator about how to setup constrained delegation for the SQL Server service account targeting your desired share.