x

Multi-Hop Authentication Failure

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.
more ▼

asked Oct 11, 2012 at 06:11 PM in Default

KnoticeTony gravatar image

KnoticeTony
100 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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:

http://msdn.microsoft.com/en-us/library/aa337083.aspx
more ▼

answered Oct 16, 2012 at 12:22 PM

KnoticeTony gravatar image

KnoticeTony
100 1 1 1

But a good to know for everyone. Thank you for sharing.
Oct 16, 2012 at 12:29 PM DirkHondong
I would think a work around to this problem is to utilize linked servers in SQL Server. That way it can handle the multiple hops and should pass the authentication.
Oct 17, 2012 at 03:35 AM Shawn_Melton
(comments are locked)
10|1200 characters needed characters left

Hi KnoticeTony,

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.

Dave.

more ▼

answered Oct 14, 2012 at 05:31 PM

Dave_Green gravatar image

Dave_Green ♦
4.2k 3 4 8

+1 This is working work around. I've also used the method with Job without a schedule to be abole to launch packages on appropriate machine remotely.
Oct 17, 2012 at 08:19 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

Hi,

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...).

Regards Dirk

more ▼

answered Oct 16, 2012 at 08:12 AM

DirkHondong gravatar image

DirkHondong
1.3k 15 17 19

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x943

asked: Oct 11, 2012 at 06:11 PM

Seen: 1741 times

Last Updated: May 17, 2013 at 02:35 AM