Wednesday, August 20, 2008 3:27 PM
I'm attempting to use OPENROWSET BULK to select from an XML file that is located on a network share.
The query I'm using is:
SELECT * FROM OPENROWSET(
BULK '<\\network_path>\<xml_filename>.xml', SINGLE_BLOB) AS x
I'm logged into Mgmt Studio via windows authentication using my domain login id that has access to the share and the XML file present in it.
I get an error:
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "<\\network_path>\<xml_filename>.xml" could not be opened. Operating system error code 5(Access is denied.).
If I attempt to open the file using <\\network_path>\<xml_filename>.xml within Windows Explorer on my computer, the file opens up fine.
From reading through Books Online, topic - "Importing Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) " - Security considerations:
"In contrast, if a SQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL Server process.
For example, consider a user who logged in to an instance of SQL Server by using Windows Authentication. For the user to be able to use BULK INSERT or OPENROWSET to import data from a data file into a SQL Server table, the user account requires read access to the data file. With access to the data file, the user can import data from the file into a table even if the SQL Server process does not have permission to access the file. The user does not have to grant file-access permission to the SQL Server process.
Based on the above, since I have access to the file using my windows login, and I'm attempting access to the file when logged in to SQL via Windows authentication, this should work, right?
Thursday, November 05, 2009 2:05 PMI'm having the exact same problem. The details of my issue are literally identical to the issue listed above. Have you found a resolution to this?
Thursday, November 05, 2009 7:35 PMModerator
From your description, I am guessing the SQL Server client is on a separate machine from SQL Server, correct? If my assumption is correct, most likely the root case for this failure is a double-hop, the Windows credentials used to authenticate to SQL Server cannot be reused to access the shared folder on a third machine without constrained delegation. To verify if my theory is correct, you can take a look to the security log on the machine hosting the shared folder, there should be a corresponding File Share\access deny entry for anonymous coming from the machine hosting SQL Server.
For more information, I would recommended the following links:
* How to: Configure an Application to Use Constrained Delegation (http://msdn.microsoft.com/en-us/library/aa528759.aspx)
* How to Implement Kerberos Constrained Delegation with SQL Server 2008 (http://msdn.microsoft.com/en-us/library/ee191523.aspx, this one if for using constrained delegation for linked servers, but it is a good resource)
I hope thsi information helps,
- Raul Garcia
SQL Server Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
- Proposed As Answer by Raul Garcia - MSModerator Thursday, November 05, 2009 7:35 PM
Friday, January 27, 2012 11:21 PM
Regardless of your credentials, in this case the SQL instance requires Network Credetials.
Use the SQL Server Cronfiguiration manager or Services.msc and set the service to start with Domain Credentials.