Bulk Insert from a Share (sql server 2005)

Question

Bulk Insert from a Share (sql server 2005)

govindan

Mr or Mrs. 500

Points: 579
More actions
July 3, 2007 at 3:11 am

#117243

HI,
I am using a bulk insert (SQL Server 2005) from a network share. using the following command
bulk
insert test.dbo.bulk_test from '\\ServerName\shareName\test.txt' with (fieldterminator=',')
When I login to Management studio using a SQL login and execute the above command, it is successfull.
But if I login to management studio using my windows credentials it gives me the following error
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\Servername\sharename\test.txt" could not be opened. Operating system error code 5(error not found).
Both my windows account and sql service account has got full privillege on the share.
Can sombody help me on this please.....

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

Is your sql services running under doamin account if not you will not be able to access the network resources. If its domain account check both the NTFS and share permissions. OS error 5 corresponds to permission issue.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Rich Hansell SSCrazy Points: 2346 More actions · Answer 2

How about if you login locally to the SQL server and open up SSMS, run it from there using windows authentication.. It will probably work. I am guessing the share is hosted by another machine. This may be an issue of the server not passing your credentials through correctly from your PC to the third spot(network share)

noeld SSC Guru Points: 96590 More actions · Answer 3

try first:

exec master..xp_cmdshell 'Dir \\servername\sharename\' and test if that can "reach" the file.

* Noel

govindan Mr or Mrs. 500 Points: 579 More actions · Answer 4

Yes I am able to view the file in the network share using

exec master..xp_cmdshell 'Dir \\servername\sharename\' and test if that can "reach" the file.

But the Bulk insert is giving the problem only when I use my windows login in the Sql Server management studio

-------------------------------------------------------------------

Also I can see on the other server's (where the shared file licated) security event viewr

NT AUTHORITY\ANONYMOUS LOGON during this time.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 5

The first time that you use xp_cmdshell do you use your windows and succedd. If so bulk insert should go without any problem. If not the id that you use for the first and bulk insert may differ. Check whats the account your sql server services are running.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

govindan Mr or Mrs. 500 Points: 579 More actions · Answer 6

I have used my windows account to do the xp_cmdshell which succeeded. but using the same account for bulk insert it failed

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 7

Somewhere it impersonating and fails. Check if you are accessing anyother sql server or resources other than the network path in the bcp command.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

govindan Mr or Mrs. 500 Points: 579 More actions · Answer 8

any answer for this prblem?

Any idea on how to resolve this issue as in the shared server the login is coming as NT AUTHORITY\ANONYMOUS LOGON using NTLM

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 9

So got the caught. You are using NTLM and this problem occurs hwen usiong NTLM because of HOPPING issue. Better force ans use Kerebros authentication or use sql user id or password instead of windows login. That should work for you.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

govindan Mr or Mrs. 500 Points: 579 More actions · Answer 10

SQL Logins works as I mentioned earlier. But I cannot use SQL logins as it depends on some other stuffs.

So when I use windows login, how do i make it to use kerbros? rather NTLM?

cmille19 SSCertifiable Points: 5970 More actions · Answer 11

I had the same issue using bulk insert via share, since the application uses a SQL login I just tested and used the SQL login. If you have to use NT authentication, you might have some success by trying EXECUTE AS a SQL login in your BULK insert procedure and still call the procedure through NT authentication.

noeld SSC Guru Points: 96590 More actions · Answer 12

Read "Delegation Requirements" from BOL

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/9e302c29-639d-4509-ba1e-cf452582c5c3.htm

To allow for impersonation.

I am assuming your server is using a DOMAIN USER account, right ?

* Noel

Balmukund SSCertifiable Points: 6648 More actions · Answer 13

This is by Design and is documented very clearly on Books online.

BULK INSERT <http://msdn2.microsoft.com/en-us/library/ms188365.aspx>

Security Account Delegation (Impersonation)

If a SQL Server user is logged in using Windows Authentication, the user can read only the files accessible to the user account, independent of the security profile of the SQL Server process. When executing the BULK INSERT statement by using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a

data_file on third computer by using a UNC path, you may receive a 4861 error.To resolve this error, use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help.

Balmukund SSCertifiable Points: 6648 More actions · Answer 14

In order for it to work with Windows Authentication on remote machine you need to turn on “Security Account Delegation”

Security Account Delegation

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_2gmm.asp

OR

Use SQL Server Authentication