June 6, 2013 at 3:17 am
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:
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!
June 6, 2013 at 7:27 am
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