All we need is an easy explanation of the problem, so here it is.
I have a ssis package deployed to ssis catalog in ServerA. It is configured to be run via sql agent job step.
The job step is set to Run As proxy user that is pointing to a domain user credential. Example: domainname\user1
Effectievly the ssis package is getting run in context of the above user.
The ssis package access a file on shared drive located on ServerB and dumps the data into a sql table on ServerC.
The sql table on ServerC is access via linked server configured on ServerA using the same windows account and impersonate is check marked. Delegation is not configured.
There is an error when dumping data to sql table via linked server since I have not configured delegation. My question is that – why does linked server need delegation for Windows domain user where as accessing a shared network file using the same account doesn’t need any delegation?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
This happens as the ssis is not involved in a two hops windows authentication process.
As you said you use a proxy so you impersonate an account that is used directly against the CIFS share.
Instead with a linked server you are doing a two hops windows authentication:
- Local sql server
- target of linked server
To avoid two hops in ssis, don’t use linked server. Instead use a direct connection string.
This is by design (how the linked server works). This is will document and I am not going to repeat what is already written about. I will point you to some credible articles.
- Setting Up Delegation for Linked Servers by Gregory A. Larsen
- Questions About Kerberos and SQL Server That You Were Too Shy to Ask by Kathi Kellenberger
- Successfully implementing Kerberos delegation
Now you know why you are getting the error. You can either fix it or implement the suggestion by @MBuschi.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂