October 3, 2012 at 3:11 pm
Hello,
I setup a 2012 SQL box and upgraded 1 of my projects for testing. Everything upgraded fine, I then created the stored procedure using the new SSISDB logic which works great. The problem I am having is I cannot EXEC the stored procedure from a linked server. Long story short I want Windows Authentication setup for linked server, explanation below. If anyone has any ideas that would be great or if I did not explain something properly let me know.
Different errors depending on linked server setup listed below.
"Be made using this security context:" - then specify the username and password:
This set allows me to connect and do everything exeptexceptthe SSIS Proc "[SSISDB].[catalog].[start_execution]"
Message:
"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 try to setup
"Be made using the login's current security context:"
Message:
"Login Failed for User 'NT Authority\anonymous logon. (Micosoft SQMicrosoft Error: 18456)
Which is the way I know it needs to be setup to run the stored procedure as I need it to use Windows Authentication
A way I have been able to get the stored procedure to run which does not make sense to me is to have the following settings:
"Be made using the login's current security context:"
with Local login added and mapped to a SQL login then run the stored procedure from another login i.e. logging into another machine with windows authentication as another user. This works I am at a loss this does not make sense.
October 4, 2012 at 1:59 am
Are you connecting to database from a web-application?
October 4, 2012 at 8:42 am
No currently just trying via SSMS. We will end up with a .net application calling it, but not until we can get this working.
October 4, 2012 at 9:37 am
Ok, but i was trying to figure out how do you get below:
"Login Failed for User 'NT Authority\anonymous logon.
Can you post the script that you use to execute? And how are connected to query window in SSMS?
October 4, 2012 at 10:03 am
Listed below is a part of the stored procedure I am trying to execute that will fail just running this will not work. I can execute anything to this server except to the SSISDB Database as this is the new way IS works in 2012 meaning it needs Windows Authentication.
being executed via SSMS
EXEC LINKEDSERVERNAME.[SSISDB].[catalog].[create_execution] @package_name = N'Package.dtsx'
, @execution_id = @execution_id OUTPUT , @folder_name = N'SystemSync'
, @project_name = N'ProjectName'
, @use32bitruntime = False
, @reference_id = NULL
October 4, 2012 at 10:34 am
Sorry I meant to say "from what I can tell windows authentication is what is needed to run the new ssis stored procedures."
October 9, 2012 at 2:46 am
Hello,
Not sure if I understand your exact issue but the Anymous logon does definitely sound like a double hop issue.
Delegation is not supported with SSIS; never been.
http://msdn.microsoft.com/en-us/library/aa337083.aspx
SQL Server Integration Services does not support the delegation of credentials, sometimes referred to as a double hop. In this scenario, you are working on a client computer, Integration Services is installed on a second computer, and SQL Server is installed on a third computer. Although SQL Server Management Studio successfully passes your credentials from the client computer to the second computer on which Integration Services is running, Integration Services cannot delegate your credentials from the second computer to the third computer on which SQL Server is running.
October 11, 2012 at 1:31 pm
I just ran into the same issue. I'm not using a linked server, instead just trying to call the package from the SSISDB catalog stored procedures.
I deployed to the new SSISDB catalog just so that I could execute the package from a stored procedure. Unfortunately for me, this causes the double-hop scenario and therefore I'm unable to use integrated authentication in the package due to the lack of delegation support.
Since it will now mean that every stored proc execution causes a double-hop scenario, this seems like a pretty significant issue.
I opened up a connect item. If it gets enough votes Microsoft may even look at it.
-Matt
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply