BULK insert error with UNC path and windows Authen

问题:

I have two servers, One is application server which has webservice and other server is a DB server,which has SQL server 2005 DB. Webservice is a vb.net app and all the input files will be on app server. When my application calls(through webservice) DB server to execute BULK insert statement with UNC path(of app server) using Windows authentication(Integrated security) then it fails with error -

System.Data.SqlClient.SqlException: Cannot bulk load because the file "\Servername\Foldername\file1.txt" could not be opened. Operating system error code 5(error not found). at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

I m admin on both servers and webservice is running on my user credentials and also I m SA on SQL DB. SQL server is running on Local system account. I changed it to run on my User account but no luck! However the same statement works when I use SQL authentication in connectionstring.

Can anyone please shed some light on this issue.

Thanks in advance

Sai

回答1:

Your client authenticates with SQL Server, then SQL Server impersonates the client and tries to access the UNC path. That is Delegation and is implicitly forbidden. You must take the explicit steps to enable constrained delegation for the SQL Server service account. See this article explaining the details: http://msdn.microsoft.com/en-us/library/ms998355.aspx The article is shows constrained delegation for an ASP service accessing the back end database, but in your case it would be the SQL Server in the middle accessing the back end UNC share.

回答2:

Some ideas for troubleshooting, in random order:

Does the bulk insert work when you run it from Sql Server Management Studio?

Changing the account under which the MSSQLSERVER service operates should help, but did you restart Sql Server after changing the credentials?

Create a command shell that runs under the same user as Sql Server:

runas /noprofile /user:domain\username cmd

And then see if you're allowed to read the network share:

net use \\machine\share