Calling SSIS package remotely fails on Proxy works on Local

  • 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!

  • 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.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply