4

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.
Sam Cogan
  • 38,158
  • 6
  • 77
  • 113
  • Just to be clear - this is running a bulk insert interactively, right? i.e. from a query window and not from an agent job? – squillman Aug 09 '11 at 15:01
  • Correct, this is from an interactive query which is running a stored proc. – Sam Cogan Aug 09 '11 at 15:04
  • Are you connected using a Windows login or a SQL login? – squillman Aug 09 '11 at 15:10
  • It's a windows login, a domain user. Should also mention that SQL is running as a domain user, which is what the SPN's are applied to. – Sam Cogan Aug 09 '11 at 15:16
  • Can you try it logged in as a SQL user by any chance? When connected with a Windows login SQL will try to impersonate the domain user you're logged in as and not the service account that SQL is running as. If you connect as a SQL user then it should try and hit the file server as the SQL service account and use the delegation you have already set up. – squillman Aug 09 '11 at 15:18
  • @squillman Ok, using a SQL account works. Not sure what that indicates! It will need to run as the Windows user as the end result of this will be using a web app that uses impersonation the Windows user through to SQL to determine what rights they have. – Sam Cogan Aug 09 '11 at 15:23

3 Answers3

1

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.

squillman
  • 37,618
  • 10
  • 90
  • 145
  • Thanks, that is essentially where the problem lies, can work a solution into the app now. – Sam Cogan Aug 09 '11 at 15:56
  • Ah, sorry didn't see your comment edit about the web app until now. How many users are you looking at? – squillman Aug 09 '11 at 16:04
  • Unfortunately it could be many users, so delegating them all wouldn't work. Will look at running just the bulk import part as the service user. – Sam Cogan Aug 09 '11 at 16:09
  • Is this your app? To get around this I usually create a user table keyed by user ID in my apps and connect to the db using a single login. To authenticate I set the site to use Windows authentication and read the user ID from server variables, then do a lookup in the table to get the appropriate authorizations. – squillman Aug 09 '11 at 16:13
1

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.

pipTheGeek
  • 1,152
  • 5
  • 7
1

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.

Darin Strait
  • 2,012
  • 12
  • 6