Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS remote package call - access denied Expand / Collapse
Author
Message
Posted Monday, December 30, 2013 8:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 8:54 AM
Points: 7, Visits: 101
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





Post #1526512
Posted Monday, December 30, 2013 11:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 04, 2014 3:32 PM
Points: 205, Visits: 830
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?
Post #1526575
Posted Tuesday, December 31, 2013 3:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 8:54 AM
Points: 7, Visits: 101
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?
Post #1526708
Posted Tuesday, December 31, 2013 8:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 04, 2014 3:32 PM
Points: 205, Visits: 830
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.
Post #1526764
Posted Tuesday, December 31, 2013 8:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 04, 2014 3:32 PM
Points: 205, Visits: 830
sreekanth bandarla (12/31/2013)
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.


An Awesome short article by Brain Kelly on SPNs if you are interested...
http://www.sqlservercentral.com/articles/Security/65169/
Post #1526767
Posted Friday, January 10, 2014 9:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 8:54 AM
Points: 7, Visits: 101
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?
Post #1529874
Posted Friday, January 10, 2014 10:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:39 PM
Points: 2,141, Visits: 486
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]
Post #1529886
Posted Monday, January 13, 2014 10:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 8:54 AM
Points: 7, Visits: 101
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.
Post #1530369
Posted Monday, January 13, 2014 12:08 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:39 PM
Points: 2,141, Visits: 486
Have the appropriate rights been granted in SSISDB and at the Project/Folder level for the linked server account?
Post #1530404
Posted Monday, January 13, 2014 12:09 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:39 PM
Points: 2,141, Visits: 486
Have the appropriate rights been granted in SSISDB and at the Project/Folder level for the linked server account?
Post #1530405
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse