Hello All –
I am trying to automate the process of copying backup files from a local (on-premise) server to AWS S3.
I have a PowerShell script that does the copy when I run it manually (inside powershell), but when I try and run via a Sql Server job (the backups are from sql server), the PowerShell commands seem to work, but NOT the AWS ones.
I thought that this might be a user/credential issue, and I did find that when the job is being run from sql server, it is run as the Sql Serer Agent user. I thought that I might be able to just change to my credentials or a stored profile, but that is not working.
As a test, I am simply trying to run the AWS Get-S3Bucket command. The error message i receive is:
Get-S3Bucket : Name resolution failure attempting to reach service in region eu-west-1 (as supplied to the -Region parameter or from configured shell default). Unable to connect to the remote server. Possible causes: - The region may be incorrectly specified (did you specify an availability zone?). - The service may not be available in the region. - No network connectivity.
But I can run the powershell script and that works.
Below is my simple script, but what I tried to do is
Set-AWSCredentials -AccessKey MyAccessKeyXXXXX -SecretKey MySecretKey -StoreAs SqlAgent
This works and i can run the command to check the credentials available, and i see one named "SqlAgent"
Set-AWSCredentials -StoredCredentials SqlAgent
and try to run the AWS commands...and it still fails when run through the Sql Server Agent Job.
Thanks in advance for any feedback and suggestions.
## set up some log file variables $logFile = 'C:\Temp\PS-Log-003.txt' $text = ' Starting PowerShell script...' $text >> $logFile $text = ' Executing within Sql Server...' $text >> $logFile date >> $logFile $text = ' Import the AWS module' $text >> $logFile Import-Module -Name 'C:\Program Files (x86)\AWS Tools\PowerShell\AWSPowerShell\AWSPowerShell' $text = ' List the current AWS credentials ' $text >> $logFile Get-AWSCredentials -ListProfileDetail >> $logFile # Set the aws credentials # $accessKey = "xxxxxxxxxxxxxxxxxxxx" # $secretKey = "xxxxxxxxxxxxxxxxxxxxxxxxxx" # $region = "eu-west-1" # Set-AWSCredentials -AccessKey $accessKey -SecretKey $secretKey # Set-DefaultAWSRegion $region # Initialize-AWSDefaultConfiguration -AccessKey $accessKey -SecretKey $secretKey -Region $region # $text = ' After setting AWS credentials ' # $text >> $logFile # Get-AWSCredentials -ListProfileDetail >> $logFile ## list out the current user running this $text = 'The current user is: ' $text >> $logFile [System.Security.Principal.WindowsIdentity]::GetCurrent().Name >> $logFile $text = 'Running AWS cmdlet...' $text >> $logFile Get-S3Bucket -BucketName MyBucketNameHere Set-AWSCredentials -StoredCredentials SqlAgent Get-DefaultAWSRegion ## try hard-coding the region Set-DefaultAWSRegion eu-west-1 Get-DefaultAWSRegion >> $logFile Get-S3Bucket -BucketName itgs-nasales-dev >> $logFile $text = ': End of PowerShell script. ' $text >> $logFile
This works in PoSH, because you are explicitly loading the AWS cmdlets and entering the AWS creds to talk to AWS assets.
All modules / resources must be loaded and credentials must be available to have access to on-prem and AWS assets.
You can set a credential file with multiple creds in it and read it in and use is as needed. See this article and of course the caveats of this approach.
Thank you for the response and links/suggestions. I will check them out.
Yes, the goal is to have the powerShell scripts run automatically and without any user interaction. Ultimately, they would be jobs that would run under a service account...not mine, but for testing and to confirm that i can even do this, I wanted to go with either embedded credentials, or use the aws stored credentials.
Hi All –
Finally figured this out. I had to create a SQL Credential and Proxy and then execute the job to "Run As" that proxy. It now works!
You must be logged in to reply to this topic.