1

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?

Improve this question
2
  • Please clarify where the linked server is configured and what server it is pointing to, and what server has the SQL Agent job. – David Browne - Microsoft Apr 2 at 12:34
  • Linked server configured on SvrA. Pointing to SvrC. SvrA has the agent job. – variable Apr 2 at 18:32
1

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:

  1. Local sql server
  2. target of linked server

To avoid two hops in ssis, don't use linked server. Instead use a direct connection string.

Improve this answer
1
  • If I use a direct connection string, then wont there be 2 hops. For example when the agent job runs the ssis package, the 1st hop will be on the job step using proxy account credentials to RUN AS, and the 2nd hop will be passing this credentials to 2nd server. – variable Apr 4 at 5:28
0

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.

Now you know why you are getting the error. You can either fix it or implement the suggestion by @MBuschi.

Improve this answer
1
  • That third link (livebook.manning.com) requires registration, and maybe payment. Unregistered users can only read portions of the content, and only for a very short time. – Doug Deden Apr 5 at 18:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.