Search

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

Active

14
1
Sign in
 to vote
Type: Suggestion
ID: 767088
Opened: 10/11/2012 12:08:59 PM
Access Restriction: Public
1
Workaround(s)
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 (expand)

Category

SSIS

Proposed Solution

Add Kerberos delegation support in SSIS 2012.
File Attachments
0 attachments
Sign in to post a comment.
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.
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement