With the new ability to execute SSIS packages from TSQL, Kerberos delegation should be supported. - by FuzzyDBA

Status : 


Sign in
to vote
ID 767088 Comments
Status Active Workarounds
Type Suggestion Repros 2
Opened 10/11/2012 12:08:59 PM
Access Restriction Public


It is stated very clearly in the Books Online article "Connect to a Remote Integration Services Server (SSIS Service)" that kerberos delegation is not supported by SSIS.  This, however, is a significant problem in SQL 2012 when you have deployed to the SSISDB catalog.

One major motivation to embrace the SSISDB catalog methodology is the new set of stored procedures that allow one to execute a package directly from TSQL.  So, if I were to deploy a package to the SSISDB catalog that utilizes Windows integrated authentication, it will always fail.

It isn't a bug only because it is clearly documented, but it is still a major issue.
Sign in to post a comment.
Posted by JannieZA on 2/23/2017 at 10:33 PM
This seems to be the solution. so can anyone confirm then if SSIS 2012,14,16 supports Kerberos officially?


Posted by David DeLella on 12/6/2016 at 6:41 AM
Does Microsoft not care to comment on this issue? They basically created an entire deploy process for SSDT and it does not work for production use because you cannot execute any package that accesses the file system do to Access Denied error messages. Even the caller field in SSMS execution logs shows a windows authenticated account which has all the necessary access it still does not pass the credentials through to the SSIS package during execution. I am using SQL Server 2014 SP2.
Posted by guestMS on 9/9/2016 at 10:47 PM
We are experiencing the same issue with SQL Server 2016....

We set up everything required for Kerberos auth, thus when we connect from the client (a C# console test application) to the SQL server hosting the SSIS catalog the connection is indeed using Kerberos auth.

So the first hop is working as expected, but all subsequent SQL connections made within SSIS packages revert back to using NTLM (even though connection strings use SSPI) and thus the error "NT AUTHORITY\ANONYMOUS LOGON".
Shared network ressources are not accessible due to the same error => credentials are not passed forward.

I'm doubting that the workaround really works, so has anyone tested it and managed to make it work in practice ?
Posted by SPFriend on 6/14/2016 at 8:43 AM
I just upgraded my SSIS package from package deploy mode to project deploy mode. And find this issue still exists, it gives access denied and cannot open the file on a UNC path shared network drive. I have no problem running it in package deploy mode and in SSDT tool. But when running from SSISDB catalog db, the issues still exists, and it is SQL server 2014. This is not acceptable, we don't want to implement kerobos authentication for this small issue.
It should allow windows authentication
Posted by Codernater on 7/31/2015 at 5:32 PM
We are experiencing the same issue here as well!
It's frustrating to see that this issue is still exist! even in SQL server 2014
Our packages are deployed on a hosted environment, we can't run the packages from our private network because of this limitation.
I haven't tried the kerberos delegation approach yet.
Have anyone tried this approach https://joethebusinessintelligenceguy.wordpress.com/2013/08/14/ssis-2012-using-sql-authentication-with-dont-save-sensitive-successfully/

Please let me know

When will Microsoft take out this limitation?


Posted by Jorge Segarra (SQLChicken) on 6/18/2014 at 10:21 AM
Good news all. Got word from SSIS team that this, in fact, does work and is supported. Someone should be updating this Connect item soon as well as updating pages in BOL for more clarification around delegation restrictions (doesn't apply to SSIS catalog)
Posted by Merrill Aldrich on 1/29/2014 at 11:58 AM
I support what everyone else has said here - I just wasted a decent amount of time trying to make delegation work as it would reasonably be expected to. Without it, the remote invocation of packages is of very limited/no use.
Posted by Asif Ghanchi on 1/22/2014 at 6:53 AM
You can follow steps given in below link to configure Kerberos authentication
Posted by Asif Ghanchi on 1/12/2014 at 10:10 PM
It's worked with service account logged in for which we have configured Kerberos authentication.
Posted by Asif Ghanchi on 1/12/2014 at 9:23 PM
After a lots of struggled, we had finally configured Kerberos delegation for our both Client and Server. And also at user level. We have also verified it using below query.

SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid;

But the issue is still exist if we are running from client server package is failing for UNC path.

I don't know why this solution is not working even though it's provided in Microsoft tickent. Is any other things we need to look.
Posted by Asif Ghanchi on 1/6/2014 at 9:54 PM
Hi Microsoft Team/Others,

I am also facing same issue. I am new to active directory configuration. Can anyone give exact steps for below method so i can use it as workaround for this.

1) Add Kerberos delegation support in SSIS 2012.
2) Create SPNs for all network resources you need to hit with double-hop auth, and configure network accounts as trusted for delegation
Posted by Willgart1 on 12/11/2013 at 6:49 AM
I agree Microsoft MUST provide a quick fix for this.
this is not acceptable as-is.
its a major bug that Microsoft cant support Windows Authentication when we just want to run remtly a simple package.
if its a simple Kerberos configuration issue, please provide a clear solution.
what are the SPN entries to add?
Posted by dline292 on 8/6/2013 at 8:58 AM
With this limitation, any package that needs to parse a flat file, or connect to a database on a remote server, will fail using Windows integrated authentication. The same package will work when called from a SQL Agent job if SQL Agent is configured with the appropriate credentials, but for some reason SSISDB wasn't designed to run under a service account like SQL Agent.

This is horrible. We just spent weeks upgrading to 2012 to realize that it has several major limitations, like this one. Please address prior to the next release. If forced to do another major upgrade, we'll probably look at other ETL tools.
Posted by allmhuran on 7/11/2013 at 11:52 PM
When you say "next release", do you mean SQL 2014??

So for two years we'll be stuck unable to use best practise designs (integrated security), and then only after going through yet another massive infrastructure project to upgrade all of our SQL Servers?

Very disappointing if this is the case. I would hope to see this in a service pack.
Posted by Michael Ferguson on 4/30/2013 at 8:45 AM
Delegation seems to be working in 2012 (SP1 at least)? When I connect to SQL using Windows authentication (showing KERBEROS in dm_exec_connections), the SQL IsServer process (running the package) executes as my connected user account and is able to access remote resources. This is all assuming you have the right SPNs set up (as we do), the accounts are configured to allow delegation, etc. I'm not sure I see the issue here.
Posted by Microsoft on 10/28/2012 at 8:09 PM
Thanks for the feedback. We're adding windows authentication in next release.
Posted by Tom Maloney on 10/14/2012 at 12:48 PM
I have a stored procedure that accepts parameters used in the new 2012 SSIS T-SQL statements. When I execute the stored procedure from the server containing SQL Server 2012 where the SSISDB is, the SSIS package runs with no problem. When I run the procedure via SSMS on my workstation, the SSIS package fails - Access denied to a UNC location. If I use a location local to the server where SSISDB is running, the procedure works.
I finally set the stored procedure to EXECUTE AS OWNER and the procedure fails when I run it from my workstation or from the Server containing SSISDB. The error returned is: The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.
When I run the procedure, without EXECUTE AS, using SQL Agent Job and execute the Job either from my workstation or from the Server, it works, but I'm not able to dynamically provide the parameters to the new SSIS T-SQL procedures. It does not provide a lot of good to have the new SSIS T-SQL statement it they can only be run from the server where the SSISDB is.
This is not an acceptable method to run the 2012 SSIS packages; I will have to use the alternative of deploying the new packages the old way to the file system and not having the benefit of the SSISDB if things are not corrected.
Posted by Adam Bean on 10/11/2012 at 12:14 PM
I would agree. Utilizing SQL Server authentication is not an appropriate work around either.