Bulk Insert -- Access denied issues - 2

Hi All

Same situation as described here , same issue.

SQL Server(SQL2005 on Windows2003) uses domain account. This domain account enabled to be trusted for delegation. Client connects to server using Windows auth. Client issues BULK INSERT with UNC path. Statement returns error:

Cannot bulk load because the file "\\Server\pub\file.txt" could not be opened. Operating system error code 5(Access is denied.)

SQL 2000 runs this statement successfully so statement and file are OK. Everyone has all permissions on network share. Domain account granted all permissions explicitly so there is no access troubles.

Audit show anonymous connections.

Question is - how to put delegation in work?

Thank you in advance,

Alexander Sinitsin

I don't think this is a problem with delegation. How does the client connect to SQL Server and issues the BULK INSERT? Is it SQL or Windows authentication? If it is Windows authentication then the service account credentials will not be used (change from SQL Server 2000) so the connected user / login needs to have the permissions to access the file. For SQL logins, the service account credentials will be used. So could you please describe your scenario?

1. What is the SQL Server service account?

2. Where is the data file located?

3. How does the user connect to SQL Server? SQL or Windows Auth?

4. Who has permissions on the data file? If the user connects using Windows auth then check for the login else check the service account.

|||

Hi Umachandar Jayachandran!

Thanks for your answer.

>How does the client connect to SQL Server and issues the BULK INSERT? Is it SQL or Windows authentication?

Client connected using Windows authentication.

>service account credentials will not be used (change from SQL Server 2000)

Sure. Server should use client's credential to access file.

>so the connected user / login needs to have the permissions to access the file.

Connected user has full permissions to access the file. Even more, this user is a file owner-creator.

>1. What is the SQL Server service account?

SQL Server run under domain account. This domain account enabled to be trusted for delegation.

>2. Where is the data file located?

Data file located on some PC in network. Not on SQL Server locally.

>3. How does the user connect to SQL Server? SQL or Windows Auth?

Windows Auth.

>4. Who has permissions on the data file? If the user connects using Windows auth then check for the login else check the service account.

Everybody has all permissions on the data file. Client connected has all permissions. Even SQL Server's domain account has all permissions. I can access this file under client's credentials without any problems.

As far as I can see, in this scenario SQL Server 2005 should use client credentials to access file. But when I run BULK INSERT, access audit show anonymous access, not client's access.

Best regards,

Alexander Sinitsin