Sql-server – Kerberos Delegation for SQL Bulk insert (access denied)

delegationkerberossql server

I have a problem when trying to bulk insert to SQL under the following situation:

  • Running management studio on Workstation A
  • SQL Running on Server B
  • File to bulk upload from located on Server C

When ever I try and bulk upload I get the error

Cannot bulk load because the file <filename> could not be opened. Operating system error code 5(Access is denied.).

Now I am aware we have a double hop issue here and need to sort out delegation. SPN's have been setup for SQL as follows (SQL is running on a different port). SQL is running as a domain user and the SPN's are on that account. 

command: setspn -l domain\sqluser

result:
MSSQLSvc/WIN-D04V1IOTESN
MSSQLSvc/WIN-D04V1IOTESN.domain.local
MSSQLSvc/win-d04v1iotesn.domain.local:55037
MSSQLSvc/WIN-D04V1IOTESN:55037

I have also setup a delegation from teh SQL user account to the file server for Cifs and HOST, but to no avail.

I have enabled Kerberos logging and am seeing the following event in event viewer:

 A Kerberos Error Message was received:
     on logon session 
     Client Time: 
     Server Time: 14:44:10.0000 8/9/2011 Z
     Error Code: 0xe KDC_ERR_ETYPE_NOTSUPP
     Extended Error: 
     Client Realm: 
     Client Name: 
     Server Realm: domain.LOCAL
     Server Name: krbtgt/domain.LOCAL
     Target Name: krbtgt/domain.LOCAL@domain.LOCAL
     Error Text: 
     File: 9
     Line: efb
     Error Data is in record data.

So, any thoughts on what I am missing here? I have had this sort of delegation working before but always with SQL on the default port, could that have an impact?

Edit

I am also now seeing this Kerbors error alongside the first one:

A Kerberos Error Message was received:
 on logon session 
 Client Time: 
 Server Time: 15:4:10.0000 8/9/2011 Z
 Error Code: 0xe KDC_ERR_ETYPE_NOTSUPP
 Extended Error: 
 Client Realm: 
 Client Name: 
 Server Realm: domain.LOCAL
 Server Name: krbtgt/domain.LOCAL
 Target Name: krbtgt/domain.LOCAL@domain.LOCAL
 Error Text: 
 File: 9
 Line: efb
 Error Data is in record data.

Best Answer

From the comments, you're connecting to SQL using a domain login so SQL is trying to impersonate you when connecting to the file share. If you don't have the delegation set up for this for your domain account then it fails.

When running the stored proc connected as a SQL login SQL will attempt to use the domain service account that it is running as, for which you say you already set up the delegation.

If you connect your query window using the domain service account SQL is running as it should work since that delegation is already configured. Set up a delegation trust to the file server for your own domain account and it should start working.

Related Topic