By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

AWS System Manager Running AWS-RunRemoteScript doesnt redirect stream output to file

0

I have a script that runs across our estate. It includes running a SQL script, and ideally I need to capture the output into a text file on that instance. As the Powershell script uses Invoke-SQLCMD, i need to redirect the verbose output to capture it.

This works as intended when running the script\line in a PS window direct on the instance and the verbose output is captured in my file $logfile:

(Invoke-SQLCMD -ServerInstance ".\$InstanceName" -Query $OptimizeIndexesSQL -Verbose -ErrorAction Stop) 4>&1 | Tee-Object -FilePath $LogFile -Append

When the script runs via SSM, it fails to push the verbose output to the file. Why?

Topics
Management & Governance
Tags
AWS Systems Manager
Language
English
invtomy
asked 3 years ago1K views
3 Answers
0
Accepted Answer

Resolved-ish. AWS SSM Service on each instance runs as "LOCAL SYSTEM", yet the logic to connect to the local SQL instance uses a trusted connection. As LOCAL SYSTEM does not have access to SQL, the running of the script fails. I have since moved the job out to Windows Task Scheduler, which works as intended, but not as clean to manage across multiple instances.

It doesnt answer why I didnt get any output within my log file (such as "Access Denied" from SQL), however the initial issue is now closed and resolved a different way.

invtomy
answered 3 years ago
0

Thanks for your suggestions. All the powershell modifications work, but they still dont write the output to the log file when executed via AWS SSM.

I also cannot use AWS-RunPowerShellScript, as I am pulling the .ps1 file from S3 storage. The extract is part of a larger housekeeping script, which would be too cumbersome to put in a single SSM document.

invtomy
answered 3 years ago
  • gsfritz
    2 years ago

    I realize this post is quite old now, but... RE: "pulling the .ps1 file from S3 storage". There is an "AWS-RunRemoteScript" document that allows you to copy a script from a specified S3 location onto the local instance and then execute it. RE: Why the script wouldn't write the output. As you note, the SSMAgent runs as a local user, so is this another permissions issue where the SSMAgent didn't have access to the file ($LogFile) you specified for the output? Separately, did you try specifying an S3 bucket in the "Output Options" so that the output would be written there? Of course, in order for this to work you would also need to add the PUT privilege to the S3 bucket in the instance profile associated with the instance.

0

The last option here would solve your issue completely. You can change the options for your needs. Lastly, try 'AWS-RunPowerShellScript' instead of 'AWS-RunRemoteScript' maybe.

Try this.

(Invoke-SQLCMD -ServerInstance ".$InstanceName" -Query $OptimizeIndexesSQL -Verbose 4>&1 -ErrorAction Stop) | Tee-Object -FilePath $LogFile -Append

Or this:

(Invoke-SQLCMD -ServerInstance ".$InstanceName" -Query $OptimizeIndexesSQL -Verbose 4>&1 -ErrorAction Stop) | Out-File -FilePath $LogFile -Append

Or this will log ALL the things:

Start-Transcript -Path $LogFile # -append , or -force?; Invoke-SQLCMD -ServerInstance ".$InstanceName" -Query $OptimizeIndexesSQL -Verbose 4>&1 -ErrorAction Stop; Stop-Transcript

Powershell_is_the_way
answered 3 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content