February 10, 2011 at 2:20 am
Hi all,
I'm having some issues when executing the following SQL;
SELECT BulkColumn
FROM OPENROWSET(
Bulk '\\192.XXX.X.XX\Shared\Archive\manifest.xml',
SINGLE_BLOB
) AS BLOB
It's a bit of an odd one, I have the following machines taking part;
MachineA (is the file server where the above xml file is located - Windows 2008).
MachineB (is the SQL Server where the database resides - Window 2008)
MachineC (is a client PC, running SSMS and connecting to MachineB - Windows XP).
When I log on to the MachineB and run the above it works perfectly, but when I run the command from the client machine (C), connected to MachineB then I get the error;
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\192.XXX.X.XX\Shared\Archive\manifest.xml" could not be opened. Operating system error code 5(failed to retrieve text for this error. Reason: 15105).
Could of points;
MachineB is running as the local system account
The share on MachineA is set to allow 'everyone' to read
Any help that anyone can provide would be great as I'm at a lose end, I was wondering if it was the interaction between XP and Windows 2008.
Thanks,
Nic
February 10, 2011 at 12:29 pm
Did you try using the machine name or DNS Alias instead of the IP Address? You should use the IP Address if possible, since they can change. If you use a DNS alias, then when/if the server changes, just change the DNS, flush the cache and you're all set.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 2, 2011 at 3:49 pm
Hi Nic,
I ran into this issue today. As this was a top search result in Google for the error message, I figured I'd share.
The issue is that in 2005+, SQL Server will perform bulk operations using either:
1. Your Windows account
2. The SQL Server service account, if you are logged in as a SQL account.
If you have not configured delegation on your domain, then SQL Server will be unable to pass the credentials from your machine (C), to the file server (A). As you discovered, you can run the query if you log onto on the database server (B). This is because B->A is a single hop, whereas C->B->A is a double hop.
The solution here is to either run the bulk operation as a SQL account, or configure delegation to trust B to delegate to A.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply