January 20, 2015 at 6:40 am
I'm going slowly crazy with this and cannot see the wood for the trees.
SQL Server 2012 running under a domain Managed Service Account. (Server A)
File located on a Windows 2012 server in a directory which has been shared to user A. (Server B).
User A is a domain account and is using his laptop, (laptop C) which is using SSMS to run a bulk insert command.
User A (Bulk Insert from laptop SSMS Client) --- > SQL Server (server A) --- > File Server (Server B)
The command fails and is returning Access denied to the file/folder share on Server B.
Running the same command on the SQL Server (Server A), the command works fine, so this is a double hop kerberos issue.
If I use a SQL Login from Laptop C, then the command works fine as the SQL Server will use the SQL's Managed service account to connect to the file share, which is set up for delegation and impersonation.
I am struggling to work out why a domain user cannot bulk insert a file from a remote location. I have checked that the user is connected with Kerberos authentication and they are. All articles seem to talk about setting up SPN's for the SQL Server so that SQL Login authentication can work over remote bulk insert, and just say to set up the file share properties properly if using a domain account.
Does anyone have any help or suggestions as to what I am missing to allow domain accounts to bulk insert remotely, from a remote file share?
Thanks.
January 20, 2015 at 9:35 am
There are a few things to check to see if kerberos is setup properly.
check if the SPN is correct, in an elevated command prompt window type "setspn -L <managed service account>" (no domain or $)
SETSPN -L sqlserverMSA
check that the MSA is trusted for delegation
you could also use the Kerberos checker here http://www.microsoft.com/en-us/download/details.aspx?id=39046
January 21, 2015 at 1:46 am
Thanks. I've already checked this and the SPN's are set up.
The best guide I've found which details how to resolve this issue is this one which I have followed to a tee. http://thesqldude.com/2011/12/30/how-to-sql-server-bulk-insert-with-constrained-delegation-access-is-denied/
The only part I haven't done is add the SQL Account to the local server permissions as it says this needs a reboot and cannot do this until we have some downtime. I'm hoping once done, this will fix this issue.
January 27, 2016 at 9:10 am
Did the reboot fixed the issue? I know this post is very old..Do you recall if this is fixed and if so, please let me know how. I am in a same situation now. Got SPNs, Delegation, Local security policies all set up. Still issue exists.. Waiting for a downtime to reboot the server.
January 27, 2016 at 10:14 am
I found that using a managed service account, to get this working, the delegation needed to be setup a bit differently:
"If using a managed service account, set the delegation up as follows: http://ityogi.se/wordpress/msas-and-kcd/"
So delegation to the second server needed to be done on the managed service account.
January 27, 2016 at 11:24 am
Yes, it is managed service account and the delegation is set up as outlined. Still have the issue.. Is reboot of the server required?
January 28, 2016 at 1:58 am
To be honest, I really cant remember. Usually a restart of the service will do for these things, but it may be worth bouncing the box too. Sorry I cannot help more.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply