AWS - Copying file to S3

This topic contains 3 replies, has 2 voices, and was last updated by  d will 3 months, 1 week ago.

  • Author
    Posts
  • #93135

    d will
    Participant

    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
    
  • #93144

    postanote
    Participant

    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

    • #93147

      d will
      Participant

      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.

    • #93516

      d will
      Participant

      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.