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

Calling SSIS package remotely fails on Proxy works on Local Expand / Collapse
Author
Message
Posted Thursday, June 6, 2013 3:17 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 315, Visits: 768
Hi all,

New to SSIS and trying to set up a new set of ETLs. We have a Database server and a ETL server.

I use a proxy account on the DB server to call the ETLs. It does this via powershell.

Link below:
http://blogs.msdn.com/b/dbrowne/archive/2010/10/11/remote-ssis-package-execution-with-powershell-2-0.aspx

If i run the package locally via the proxy account it runs with no errors. If i run it from the DB server Agent job it fails with the error:


Message
Executed as user: DOMAIN\SSIS_Proxy. The job script encountered the following errors. These errors did not stop the script: Unhandled Exception: System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list. at System.Text.StringBuilder.AppendFormat(IFormatProvider provider, String format, Object[] args) at System.String.Format(IFormatProvider provider, String format, Object[] args) at Microsoft.SqlServer.Management.PowerShell.Res.GetString(String key, Object arg0) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.GetErrorRecordMessage(ErrorRecord errorRecord) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.HandleAgentJob(RunspaceConfiguration config) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.Main(String[] args). Process Exit Code -532459699. The step failed.


If i run the agent job via a proxy with my account it works! so it must be security related?

The proxy account has the following sec which i thought was enough, as i said it works when i run it locally on the same account.. Could anyone help? clean it up for me?

ETL Server:

DCOM permissions based on this article:
http://msdn.microsoft.com/en-us/library/aa337083.aspx

Launch and Activation Permissions - Local and Remote

(I tried with just remote permissions and i couldnt get packages to run)

ETL DB engine:
Public Login
User to MSDB with db_ssisoperator

Thanks for any help!

Post #1460593
Posted Thursday, June 6, 2013 7:27 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 315, Visits: 768
Problems solved pretty silly really.

TLDR

Add the proxy account to the ETL Servers "Remote Management Users" group to enable running Posh remotely.



It was failing due to calling powershell remotely without access. when i ran it in PoSH ISE instead of the Agent i got a security error which linked to below:

http://technet.microsoft.com/en-us/library/hh847850.aspx

HOW TO ENABLE REMOTING FOR NON-ADMINISTRATIVE USERS
---------------------------------------------------
ERROR: ACCESS IS DENIED

To establish a PSSession or run a command on a remote computer, the user
must have permission to use the session configurations on the remote
computer.

By default, only members of the Administrators group on a computer have
permission to use the default session configurations. Therefore, only
members of the Administrators group can connect to the computer remotely.

To allow other users to connect to the local computer, give the user
Execute permissions to the default session configurations on the local
computer.

The following command opens a property sheet that lets you change the
security descriptor of the default Microsoft.PowerShell session
configuration on the local computer.

Set-PSSessionConfiguration Microsoft.PowerShell -ShowSecurityDescriptorUI


Running the example at the end just brings up the Local Users and Groups. Theres a group called "Remote Management Users" I added my proxy account to that, which enabled it to run the ETLs without error.

Post #1460684
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse