I needed to manually run a project deployment model SSIS package (on box A) that talks to a SQL Server instance on another machine (box B), the package runs a stored procedure and returns some results. Box A and Box B are not linked servers, however the SQL Agent service account and the SQL Server service account from Box A both have permissions to execute the procedure on box B.
When I run the package manually through the SSIS project
it fails with the below error:
What’s going on? I’ve got permission to run the stored procedure on Box B as well, but my user seems to be getting replaced with NT AUTHORITY\ANONYMOUS LOGIN. The issue here is that as of SQL Server needs credential delegation to be turned on. You can do this either through a linked server, or as I did it, by creating a SQL Agent job to handle the delegation for me.
There’s more learning to do here as we may have impersonation handling the delegation for me, but for the purposes of recording how an error message relates to a problem, I think this suffices for the moment and will allow further investigation.
lsdbtech 
did you resolve this issue, I’m facing the same problem.
LikeLike
Yep. The issue here is that as of SQL Server needs credential delegation to be turned on. You can do this either through a linked server, or as I did it, by creating a SQL Agent job to handle the delegation for me.
LikeLike