August 16, 2018 at 7:18 pm
Hello... yes, tar and feathers for yet another bulk insert question....
We've chosen not to pursue explicit delegation and have therefore created a SQL login that allows us to invoke our command as the named NT service account running the SQL server service.
We've set the permissions on the file share, and validated that we are able to open the UNC file path, as the NT service account from the server running SQL server.
If I move the file to a local disk on the SQL server, it works perfectly. If I recreate all the steps in my lower environment, everything works.
What could possibly prevent this in my production environment. How can I track down the root cause. Should I try a restart first?
August 16, 2018 at 7:36 pm
If any kind of group membership was changed for the service account, the running process will not have the relevant security tokens (because these are assigned when a service first logs in at startup) so a restart might well be necessary. If you can't do that, you could try ensuring that any security permissions are granted explicitly to the service account directly, rather than any group it is a member of, as that reduces the token requirement to something the running process is guaranteed to have already (not best practice though, so avoid if possible).
August 17, 2018 at 6:30 pm
Toddmonster85 - Thursday, August 16, 2018 7:18 PMHello... yes, tar and feathers for yet another bulk insert question....We've chosen not to pursue explicit delegation and have therefore created a SQL login that allows us to invoke our command as the named NT service account running the SQL server service.
We've set the permissions on the file share, and validated that we are able to open the UNC file path, as the NT service account from the server running SQL server.
If I move the file to a local disk on the SQL server, it works perfectly. If I recreate all the steps in my lower environment, everything works.
What could possibly prevent this in my production environment. How can I track down the root cause. Should I try a restart first?
I believe you're fighting the Kerberos Double-Hop problem. Your privs aren't being passed to the remote system (which is why I call it a "Secirity Feature" rather than a "problem").
You can prove if that's the actual problem or not by RDP'ing into the server itself and run the same code instead of running it from your desktop SSMS. If it works that way, then it's Kerberos doing its job correctly.
What most people end up doing is making the mistake of disabling Kerberos for these types of things. What needs to happen is for SQL Agent to be given the privs to the remote folder and then let a scheduled job do the run for you. I suspect that's going to be the end use anyway, is it not?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 20, 2018 at 8:40 am
Jeff Moden - Friday, August 17, 2018 6:30 PMToddmonster85 - Thursday, August 16, 2018 7:18 PMHello... yes, tar and feathers for yet another bulk insert question....We've chosen not to pursue explicit delegation and have therefore created a SQL login that allows us to invoke our command as the named NT service account running the SQL server service.
We've set the permissions on the file share, and validated that we are able to open the UNC file path, as the NT service account from the server running SQL server.
If I move the file to a local disk on the SQL server, it works perfectly. If I recreate all the steps in my lower environment, everything works.
What could possibly prevent this in my production environment. How can I track down the root cause. Should I try a restart first?
I believe you're fighting the Kerberos Double-Hop problem. Your privs aren't being passed to the remote system (which is why I call it a "Secirity Feature" rather than a "problem").
You can prove if that's the actual problem or not by RDP'ing into the server itself and run the same code instead of running it from your desktop SSMS. If it works that way, then it's Kerberos doing its job correctly.
What most people end up doing is making the mistake of disabling Kerberos for these types of things. What needs to happen is for SQL Agent to be given the privs to the remote folder and then let a scheduled job do the run for you. I suspect that's going to be the end use anyway, is it not?
Thanks Jeff. I am a little confused as to how the double hop problem could come into play here. We're using a SQL login to invoke the bulk insert command, so it's not coming from another NT authenticated login, instead it's coming from a SQL login and then should just be the single hop to the NT service account that is running the SQL services. I'm concerned that I am missing something fundamental.
The frustrating part is that this worked perfectly in UAT - we're matched like for like on all attributes between environments.
Even a restart of the SQL server over the weekend didn't resolve the issue... it's a real hum dinger....
What else could we do to produce further lower level debugging?
Ian
August 20, 2018 at 9:48 am
Toddmonster85 - Thursday, August 16, 2018 7:18 PMHello... yes, tar and feathers for yet another bulk insert question....We've chosen not to pursue explicit delegation and have therefore created a SQL login that allows us to invoke our command as the named NT service account running the SQL server service.
We've set the permissions on the file share, and validated that we are able to open the UNC file path, as the NT service account from the server running SQL server.
If I move the file to a local disk on the SQL server, it works perfectly. If I recreate all the steps in my lower environment, everything works.
What could possibly prevent this in my production environment. How can I track down the root cause. Should I try a restart first?
So we finally figure it out.
We were not subject to the kerb double hop. the SQL login approach worked.
The issue was with the root of the UNC share - the SQL service account needed to be listed individually, not within an AD group.
Once that was added and we validate that the folder within the UNC share was also properly perm'd everything began working as expected.
Thanks for everyones help!!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply