December 30, 2013 at 8:29 am
I am trying to achieve the following scenario and I'd like some advice as to whether it is supported in SQL 2012?
Server A: SQL Agent Job - executing SSIS task SSISDB\Folder\Package.dtsx on Server B
Package.dtsx calling (DTEXEC) a remote SSIS package2.dtsx on Server A
Note: The SQL Agent job SSIS step runs as a proxy that is both server admin and sysadmin on A and B
When I setup the above i get the following error when using DTEXEC with /File ..
"Unable to load the package as XML because of package does not have a valid XML format...
Failed to open package file "\\brcubiprep02\2012_SSISPackages\Package2.dtsx" due to error 0x80070005 "Access is denied."
When I setup the above i get the following error when using DTEXEC with /ISServer..
"Failed to execute IS server package2...
Description: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
By moving the SQL Agent to Server B it works but as things stand this is not an option for me.
Any other suggestions or explanations?
Stuart
December 30, 2013 at 11:57 am
This could be SPN issue(Failing at the double hop scenario). when Kerberos is not configured correctly the creds are not carried when the packet hops from the one machine to the next. Can you please double check, SPN's are properly setup in your environment?
December 31, 2013 at 3:32 am
Hi Journeyman, Both Server A and B have enabled 'trust this computer for delegation to any service'
and the domain account enabled as 'Account is trusted for delegation'
How else can I check the SPN's are configured correctly?
December 31, 2013 at 8:34 am
http://www.sqlservercentral.com/Forums/Topic724433-149-1.aspx#bm724477
Also, SQL Server Error Log will have an entry something similar to "The SQL Server Network Interface library successfully registered the Service Principal Name (SPN)..............." when it got restarted last time. If it had any issues(Missing or Duplicate) with SPN registration, it gets logged in the error log as well.
December 31, 2013 at 8:43 am
sreekanth bandarla (12/31/2013)
http://www.sqlservercentral.com/Forums/Topic724433-149-1.aspx#bm724477Also, SQL Server Error Log will have an entry something similar to "The SQL Server Network Interface library successfully registered the Service Principal Name (SPN)..............." when it got restarted last time. If it had any issues(Missing or Duplicate) with SPN registration, it gets logged in the error log as well.
An Awesome short article by Brain Kelly on SPNs if you are interested...
January 10, 2014 at 9:46 am
Still trying to get the network guys engaged to sort add the SPN entries.
In the meantime could someone pls confirm whether the double hop issue can be resolved using SQL authentation?
January 10, 2014 at 10:07 am
What happens if you try to execute T-SQL stored procedures remotely with T-SQL?
EXEC [SSISDB].[catalog].[create_execution]
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
EXEC [SSISDB].[catalog].[start_execution]
January 13, 2014 at 10:09 am
Kerberos authentication is now working - verified by querying sys.dm_exec_connections
EXEC [LinkedServer].[SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'Test', @project_name=N'ETLMaster', @use32bitruntime=False, @reference_id=Null
Select @execution_id
DECLARE @var0 smallint = 1
EXEC [LinkedServer].[SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0
EXEC [LinkedServer].[SSISDB].[catalog].[start_execution] @execution_id
GO
But executing the procs to run the package remotely via a linked server give the following - So still not resolved the issue.
Msg 27146, Level 16, State 1, Procedure create_execution, Line 167
Cannot access the package or the package does not exist. Verify that the package exists and that the user has permissions to it.
(1 row(s) affected)
Msg 27138, Level 16, State 1, Procedure set_execution_parameter_value, Line 66
The input parameter cannot be null. Provide a valid value for the parameter.
Msg 27138, Level 16, State 1, Procedure start_execution, Line 55
The input parameter cannot be null. Provide a valid value for the parameter.
January 13, 2014 at 12:08 pm
Have the appropriate rights been granted in SSISDB and at the Project/Folder level for the linked server account?
January 13, 2014 at 12:09 pm
Have the appropriate rights been granted in SSISDB and at the Project/Folder level for the linked server account?
January 13, 2014 at 1:30 pm
Since you are not seeing 'NT AUTHORITY\ANONYMOUS LOGON', I think you fixed one issue and now you are dealing with User Permissions Issue...
Well, Can you double check if the user(Who ever is calling the package) has "SSIS_Admin role" granted?
January 14, 2014 at 7:41 am
There was an issue with the linked server that had caused this error. The user is fully permissioned as sa so no security issue.
Am now able to remotely execute a package but the package failing still on 'NT AUTHORITY\ANONYMOUS LOGON'
I've assigned the SQL Server SPN's from the SPN commands generated using the handy SQL Server Kerberos configuration tool but is the issue that I need to create SPN's for the SSIS service too?
i.e.MsDtsServer110
thanks in advance!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply