A fellow peer was looking at an error when trying to configure another developer's database. All of us are working on the same application, but our install doesn't seem to be working correctly, which causes the DB to not be properly installed, thus this guy is trying to configure it manually. We are using SQL 2005 (either Express or the regular versions) for our DB servers.
He's using SQL Management Studio, logged in using Windows Authentication and was trying to bulk insert some records (BULK INSERT sometable FROM '\\servername\share name\path\file.ext')and came up with the error:
Cannot bulk load because the file "\\servername\share name\path\file.ext" could not be opened. Operating system error code 5(Access is denied.).
The connection setup that causes the above error is as depicted below:
We're sure that this is because of security, because the same operation worked when we tried it on the database running on the local machine. Trying to bulk insert from a file in the SQL Server's local drive also works, so we tried figuring out how to solve it. Spending some 20 minutes fiddling around (we changed the SQL Server's service to use a domain account, we tried sharing on a different computer, etc.) wasn't fruitful. My interest was piqued enough where I promised myself I need to find out why.
I got some answers to that; per SQL Books Online - as delineated in the Security Considerations sub heading - apparently the SQL security profile used to execute is different whether you're logging in to it as a Windows Authenticated user, or as a SQL user. If you're connecting to SQL Server as a SQL user, it will use the SQL Server process account security profile; if you're connecting as a Windows Authenticated user, it'll use that Windows' user security profile. However, it is only valid for that particular SQL Server only.
In the diagram above, if the client connects using SQL user login, then when executing the BULK INSERT statement it will use the SQL Server process account. In most typical development deployment, this would be NETWORK SERVICE; if the file accessed resides in a typical network share (administered by MIS), most of the time this will result in access denied. This is because the share has most users/groups to have access, but not Everyone and developer's machines are usually not added as well. You can simulate this by creating a share locally and if you only add specific users, the SQL statement would fail.
If the client connects using Windows Authentication, then when executing the BULK INSERT statement it will use that particular Windows user's security profile. However, the security profile is only used IF the statement only operates on the local computer. Based on the diagram above, the file resides on another server in the network, thus the BULK INSERT statement will still fail. To have the security credentials to be forwarded, the system has to be configured for Delegation / Impersonation, which needs to be done at the Active Directory level. Since I don't have a local test domain, I can't quite test this, but the steps to do this is depicted at this blog. It's pretty involving; since I don't quite specialize in DB, I'm trusting the procedure will work .
I hope this helps others; it doesn't quite help in our case, but researching this allows me to better understand how and why this problem occurs. At least I have an answer to our client if someone hits this.
As another tip (at least for me), there's this nice xp_cmdshell stored procedure that we use to see if we can access the network directories from the server side. It's essentially a sproc that allows executing typical command prompts command - so we use it simply by entering: EXEC xp_cmdshell 'dir \\servername\sharename' and it allows us to see immediately whether the system can see that directory or not.