AWS - Copying file to S3

PowerShell Help
1

Hello All -

I am trying to automate the process of copying backup files from a local (on-premise) server to AWS S3.
( And, just an FYI, I am new to both PowerShell and AWS.)

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
(1) hard-code the keys and region just to see if that would work. The Get-S3Bucket command still fails.
(2) Used the Set-AWSCredentials command to store a profile.

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”
I then use the Set-AWSCredentials to set the stored one

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.

S3-test.ps1

## 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
2

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.
This means, you have to respond to credential prompts or you have to pre-stage the creds. This does not mean embed credentials in plain text creds, but as secure strings.

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.

Securely Store Credentials on Disk http://powershellcookbook.com/recipe/PukO/securely-store-credentials-on-disk

Multiple credentials

3

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.

Thanks again.

4

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!