Home / server / Questions / 299254
Accepted
Sam Cogan
Asked: 2011-08-10 06:56:31 +0800 CST

Kerberos Delegation for SQL Bulk insert (access denied)

  • 772

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/[email protected]
     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/[email protected]
 Error Text: 
 File: 9
 Line: efb
 Error Data is in record data.
sql-server kerberos delegation

3 Answers

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

    • 1

  2. The SPN's look correct for SQL Server. Are the correct SPN's registered for the file server?

    The other thing that can mess up kerberos authentication to SQL Server is DNS problems. I read somewhere that the sql client will do a reverse DNS lookup on the address of the server and use that name to form the SPN.

    I know you have already done many of these steps but this should be everything you need to do.
    Ensure DNS resolution is working correctly for both the SQL server and the file server.
    Register SPN's for the SQL Server. Ensure there are no duplicate SPN's. setspn in SQL 2008 can do this check for you.
    Register SPN's for the file server. Ensure there are no duplicates.
    Enable "trusted for delegation" on the SQL Server service account.
    Also check that your account isn't marked as not delagatable. (is that a word?)

    If you can't get it to work then you could set up a SQL Agent job to un the bulk insert. Then it will run under the account you configure the job to run as.

    • 1

  3. The lack of a client time in your error message makes me suspicious. Kerberos authentication will fail if the time on the client and the time on the server are too different. (I've never been sure what "too different" really is. I know that a minute can do it because we had this problem (again) yesterday with a new server.)

    When kerberos authentication fails, SSMS will probably still connect, but it will silently fall back to using NTLM authentication.

    You can force kerberos, by tweaking connection settings and strings, so that a connection will fail hard if kerberos authentication, but there is an easier way to see if you are connecting with kerberos. To ensure that you are connected using Kerberos authentication, connect as normal via SSMS and run this in a SSMS query window:

    select auth_scheme from master.sys.dm_exec_connections where session_id= @@spid

    You should see "KERBEROS". If you don't, you will probably see "NTLM" and you will know that something is wrong.

    • 1