ansaurus

Question

BULK insert error with UNC path and windows Authentication

Answer 1

A:

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

Andomar 2009-05-12 15:31:40

Thanks for replying..When i ran the Bulk Insert on SQL mgmt studio(logged in using windows authentication) it worked fine!! After I changed the account under which MSSQLSERVER service runs to domain user(myself as am admin), I restarted the service. But still it did not work..What suprises me is SQL authentication works but window auth is not..!I will try that command shall option and see if that works!!

2009-05-12 15:43:47

Yes..am able to accesss the shared folder from DB server using command shell..I think this may be a bug, for Bulk insert statement to not to work using windows auth given a UNC path

2009-05-12 15:52:59

Can you show the VB code that does the bulk insert? On rereading, your error message seems to be missing a backslash at the start, "\Servername\Foldername\file1.txt" should start with \\Servername.

Andomar 2009-05-12 20:24:14

Yes..my Bulk insert statement is fine..mistakenly I wrote on the post with one"\" missing.

2009-05-13 09:40:53

Answer 2

+1 A:

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.

Remus Rusanu 2009-05-13 03:18:23

Thanks for replying. Yes I think enabling contrained delegation would solve the problem. But its a long shot, for now I have made changes to my application to copy file onto DB server and run bulk insert without any problems.

2009-05-13 09:39:53

+1 Super, here's some extra info: "If a user uses a SQL Server login, the security profile of the SQL Server process account is used. 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." http://msdn.microsoft.com/en-us/library/ms175915.aspx

Andomar 2009-05-13 10:05:49

ansaurus

tags:

views:

answers:

BULK insert error with UNC path and windows Authentication

related questions