Trying to copy sql backups to AWS S3

• will_william

  Old Hand

  Points: 316

  More actions

  February 2, 2018 at 4:44 pm

  #410194

  Hello -
  We have weekly full and daily diff backups scheduled (created via a maintenance plan). The backups are created on a network share. There is a new requirement to also copy the backups to AWS S3.
  I have a windows command that i can execute outside of the sql server job and it runs as expected and copies the backup files to our S3 bucket.

  When I try to run from within a sql server job, it fails. I have tried various things like
   - setting the %userprofile% to my user which has the aws credentials
   - copying my aws credentials to the C:\Users\SQLSERVERAGENT
  ...and the aws command part of the windows .cmd script fail. I am just trying something simple at first:
   aws s3 ls s3://mybucket/folder1/

  The error message is:
  ('Connection aborted.', error(10060, 'A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond'))

  As i wrote, i can run the same .cmd file and it works every time.

  Could this still be an issue with aws credentials? Or, any other suggestions?

  thanks -
  will

• Alexander Zhang

  Default port

  Points: 1497

  More actions

  February 8, 2018 at 11:47 pm

  #1979179

  I'm pretty sure it's AWS credentials issue. I guess you use SAML/IAM Role to generate temp credential. MSSQL works in different security context. Means it can NOT use your credential. You can have a try to generate AWS credential in MSSQL context (for example, xp_cmdshell). BTW, to backup DB to AWS S3, good options might be AWS Storage Gateway or some 3rd backup tool.

  GASQL.com - Focus on Database and Cloud

• will_william

  Old Hand

  Points: 316

  More actions

  February 8, 2018 at 11:53 pm

  #1979180

  Hi...Thanks for the reply and suggestion.

• will_william

  Old Hand

  Points: 316

  More actions

  February 12, 2018 at 4:12 pm

  #1979545

  Hi All -
  Figured this out finally.  I had to create a Sql Server Credential and Proxy.  I used my account (just to test) as the one for the Credential.  Then in the job step, i set the "Run As" to that proxy and it worked!