Home Dashboard Directory Help
Search

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


Status: 

Active


28
1
Sign in
to vote
Type: Suggestion
ID: 767088
Opened: 10/11/2012 12:08:59 PM
Access Restriction: Public
1
Workaround(s)
view

Description

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.
Details
Sign in to post a comment.
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
http://www.sqlscientist.com/2014/01/setup-kerberos-authentication-for-sql.html
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;
Output: KERBEROS

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.
Sign in to post a workaround.
Posted by Michael Ferguson on 4/30/2013 at 8:46 AM
Create SPNs for all network resources you need to hit with double-hop auth, and configure network accounts as trusted for delegation.