Powershell script to execute DDL statements on linked servers - not working when run using SSIS

Question

I have a Powershell script that loops through a list of SQL Servers and creates server logins and database users.

The script runs on a separate server, under the administrator credentials on that server, and connects to the other SQL Servers via linked servers.

#Get administrator credentials
$password = get-content C:\Powershell\General\password.txt | convertto-securestring;
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist "DOMAIN\administrator",$password;

When this script is run manually (either directly through a Powershell window or using a batch file through a command prompt) it works perfectly well. I am logged onto the executing server as administrator when running the script manually.

I then tried to run this Powershell script using an SSIS package on the executing server, using the Execute Process Task to run a batch file. The package was executed from a SQL Agent Job. Although both the job and the package seemed to execute successfully, the DDL statements were not executed against the linked servers.

SQL Agent on the executing server is run under a designated Service Account. SSIS runs under the Network Service account.

Does anybody have any thoughts on what I might be doing wrong? I am happy to provide details of the script or anything else that is required.

Thanks

Ash

UPDATE: ok we have a little more information.

I took out the lines I posted above as I have discovered I don't actually need the administrator credentials I was retrieving.

I logged onto the server with the script on it using the service account. As per @ElecticLlama's suggestion I set a Profiler trace on the destination server. When running the script manually (or running a batch file manually that runs the Powershell script) everything works well and the Profiler shows the DDL actions, under the service account login.

When running a job through SQL Agent (either a CmdExec job or an SSIS package) that runs the same batch file, I get the following error:

'Login failed for user 'DOMAIN\ServiceAccount'. Reason: Token-based server access validation failed with an infrastructure error.'

Anybody have any further thoughts?

if you do not get any other responses try looking into CSSP. — rob
My assumption at this point is the service account has its Execution-Policy set at the default... whatever it was, doesn't allow for the execution of scripts. You can verify this by having an Execute Process Task in a package issue a Get-ExecutionPolicy and pipe the output back to an SSIS Variable and then log that value. If that is the case, then the resolution is to have that account issue an Set-ExecutionPolicy for something better than the default. I like to run with my pants down so I use Unrestricted — billinkc
"Seemed to Execute Successfully" - To troubleshoot you need logs from SQL Agent, SSIS, and from within your powershell script. You need be certain whether everything executed successfully or not. — Nick.McDermaid
@billinkc thanks for the suggestion. We set the Execution Policy to Unrestricted for both the 32-bit and 64-bit versions of Powershell for the service account. Unfortunately that did not help. — The Dumb Radish
@ElectricLlama thanks for your input. Of course I had checked all Event Logs and SQL Logs and no errors were found. — The Dumb Radish

The Dumb Radish The Dumb Radish · Accepted Answer · 2014-04-04T09:55:54

Thnaks to everyone for their help. Once I got that last error a quick search revealed I just had to restart SQL Agent and now everything works as it should. Thanks in particular to @ElecticLlama for pointing me in the right direction.

Ash

Powershell script to execute DDL statements on linked servers - not working when run using SSIS

1 Answers