| Author |
Topic |
|
pug2694328
Posting Yak Master
166 Posts |
 Posted - 2007-09-24 : 15:50:21
|
| I'm setting up a new 2005 server and bulk insert from a client workstation (using windows authentication) is failing with:Msg 4861, Level 16, State 1, Line 1Cannot bulk load because the file "\\FILESERVERNAME\sharedfolder\filename.txt" could not be opened. Operating system error code 5(Access is denied.).Here's my BULK INSERT statement (though I'm pretty sure there's nothing wrong with it): BULK INSERT #FIRSTROW FROM '\\FILESERVERNAME\sharedfolder\filename.txt' WITH ( DATAFILETYPE = 'char', ROWTERMINATOR = '\n', LASTROW = 1 )If I run the same transact SQL when remote desktopped into the new server (under the same login as that used in the client workstation), it imports the file without errors.If I use the sa client login from the client workstation (sql server authentication) the bulk insert succeeds.My old SQL 2000 server lets me bulk insert the file without errors even from my client workstations using windows authentication.I have followed the instructions on this site: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=928173&SiteID=1, but still no luck and same error.I'm pretty sure it is being caused by the increased constraints on bulk insert in 2005. Hoping someone can help. The more specific the better. If you need more info, let me know.Oh and I've also made sure that the SQL service uses a domain logon account rather than the local system account.Note that the file server (source file resides there) is a DIFFERENT machine than the 2005 SQL server. If I move the source file to the sql server machine the error goes away (not a preferred solution though).Thanks! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-24 : 15:54:31
|
From BOL:quote:
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.
Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
pug2694328
Posting Yak Master
166 Posts |
Posted - 2007-09-24 : 16:08:13
|
| Thanks for your FAST response TKISER!I'd rather our users be able to use windows authentication.I followed the instructions on that site I noted in the first post to enable my user account for delegation.When I use the setspn -L SQLAccount from the command prompt I get the expected Registered ServicePrincipalNames for CN=myname,CN=Users,DC=domainname,DC=local: MSSQLSvc/newservername.domain.local:1433I have read the 2005 doc you copied and unfortunately need more detailed steps. Can someone detail exactly what must be done? Also, is there a way to enable bulk insert for an entire group or only one user at a time? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-24 : 23:10:26
|
| If sql login is domain group, you can grant bulk insert rights to group of users. |
|
|
|
pug2694328
Posting Yak Master
166 Posts |
Posted - 2007-09-25 : 16:34:33
|
| I'm almost positive that what I need to do is make sure that the SQL server is setup for delegation when a windows authenticated user attempts to bulk load a file from a second server.Can someone provide instructions? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-25 : 22:55:46
|
| HAve to start sql service with domain account that has permission to access file on second server. |
|
|
|
pug2694328
Posting Yak Master
166 Posts |
Posted - 2007-09-26 : 10:45:46
|
| rmiaoYup, I did that a while ago.This would work with a 2000 sql server, but with 2005 it isn't enough. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-26 : 22:43:28
|
| Not really. |
|
|
|
MYCATDIDIT
Starting Member
1 Post |
Posted - 2007-10-15 : 15:36:46
|
| pug2694328 or rmiao, what was the final outcome of this? Did you get it too work and if yes, what finally resolved issue? I am having same problem when we moved our application from one dead computer to a larger computer using vmware where we were generiously given some "slices". same error. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-15 : 22:19:17
|
| What's sql service startup account on your server? |
|
|
|
|
client workstation initiated bulk inserts fail