sql server - BULK insert error with UNC path and windows Authentication

Question

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 "ServernameFoldernamefile1.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

Answer 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.