I've got an SSIS package that opens a connection to an administrative database hosted on server A. The package is deployed to an SSIS catalog hosted on Server B. I've also got a stored procedure that launches the package using catalog.start_execution. That stored procedure is in an administrative database also hosted on Server B.
If I establish an RDP session directly to server B and run the package either via catalog.start_execution or directly from the SSIS Catalog UI it runs properly. However if I try to run the package using either method from a management studio session on a 3rd server lets call it server C, the connection to server A fails attempting to use "NT Authority/Anonymous Login" which suggests to me a delegation problem. I've even created an agent job on server C owned by the agent service account which is sysadm on all of the boxes and the error still occurrs.
We've verified that the SPNs for all of the sql server instances are listed for delegation in our SQL Server service account. We've also verified that Kerberos authentication is being used and we've been through the documentation on setting-up delegation several times. All of the sql server instances are SQL Server 2012, 64 bit. Server A, the connection target is Enterprise Edition running on a windows server 2008 core cluster node. Server B, the SSIS host is Sql Server standard edition running on windows server 2008 R2. Server C is SQL Server 2012 Enterprise, also on a clustered node of Windows Server 2008 core.
If anyone has encountered this problem and solved it, your help would be greatly appreciated.
asked Oct 11, 2012 at 06:11 PM in Default
Unfortunately we found the answer ourselves. Delegation is not supported in SSIS. Here's the link to the KB article, the note on delegation is at the bottom of the article:
answered Oct 16, 2012 at 12:22 PM
I think you're right that this sounds like a delegation issue.
You state you've tried most of the troubleshooting steps I'd try - just one clarification though - you sate you've checked that Kerberos Authentication is being used - have you checked that it's used in each actual connection C->B and B->A (e.g. by looking in sys.dm_exec_connections), or just that Kerberos is the method set up for connections to use?
I imagine you've seen these links, but if not they may have some extra troubleshooting steps you could try - http://www.microsoft.com/en-us/download/details.aspx?id=21820 and http://msdn.microsoft.com/en-us/library/ee191523(SQL.100).aspx
If I recall correctly I have used a workaround in the past to get round this - you could set up a job on server B that starts the SSIS package (running as the user in question), and from server C you simply start the job on server B. Because server C is just starting the job, the job should run from server B (as if started locally) and be able to contact server A. Unfortunately this isn't something I can test here immediately, but I believe that this is what I've used in the past.
answered Oct 14, 2012 at 05:31 PM
I have another link that may provide some help. It's the Kerberos checklist by Adam Saxton (MS CSS).Take a look here: http://blogs.msdn.com/b/psssql/archive/2010/06/23/my-kerberos-checklist.aspx
Now my additional thought (although I'm not that sure and maybe have to check a few documents by myself): I think that not only the executing service account needs the trust for delegation right but also the computer account from where the next hop is initiated.
Also: Are the servers in the same domain or "only" in the same forest? If it's the same forest, then the domain functional level must be at least 2003 (ok, we have 2012, but there's still a lot old stuff out there...).
answered Oct 16, 2012 at 08:12 AM