bulk insert fails

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 1
Cannot 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 (this would work on the 2000 server, but not 2005).

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

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?

Thanks!

Can you access the file using thew full path \\FILESERVERNAME\sharedfolder\filename.txt ?

of course, with the user who execute the bulk insert.

Did you alread verify the share and NTFS permition?

|||Yes, I can access file. I have given both that user and the sql service user account full access.

Note that this user has no problem running the same bulk load from my old 2000 server.

I'm pretty sure that my user is not being delegated through to the file server.
Is there a way to catch the user information that BULK INSERT is using to access a file? A SQL trace doesn't catch it.

I'm not a windows system admin, so forgive my ignorance, but what's an ntfs permission? I thought NTFS was just the hard drive file system format.
.
Do you know how to setup delegation for users?

|||

When a told NTFS permission I,d mean the file system permission.

|||Yes, the user has full network and local permission to the file.
|||

Can you try to make lower the authentication level of NTLM protocol?

I saw this posts, try to verify your solution: http://forums.microsoft.com/msdn/showpost.aspx?postid=270868&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1