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?
i am the owner of the sql job. it is running as sqlserver agent. as part of the .cmd file, i write out the current user and profile, and i can see that it is SQLSERVERAGENT.
by owner I mean in the actual sql job there is a field under job Name that says Owner. It has your windows credentials? and you use the same credential for your aws?
Also it could be issue with some sort of delegation
or it could also maybe be that you need to run this in an elevated cmd (as Admin)
Yes, I know what you mean. my credentials are listed in the "Owner" .
For AWS we don't use our domain credentials. We set up users via their IAM tool and then use those credentials. Have you done any work with AWS and S3?
Create a domain account with minimal permissions (only those needed to access AWS).
Modify your agent job to be owned by 'sa' - then modify the step that runs the windows command script to run using the proxy account.
You do not want this running under your personal account - as that will fail as soon as you change your password. You want a fixed 'service' account where the password never changes - then you add that account as a credential in SQL Server. You then create a proxy account under the appropriate subsystem in SQL Server Agent and set the job step to use that proxy account.
Note: you should also not be using a personal account as the owner of agent jobs. This is just asking for further problems when your account is disabled for any reason. If - for example - you have too many failed login attempts then AD will disable your account for a minimum of 15 minutes and during that time any agent jobs that attempt to run will fail.
Thank you for the response and suggestions. Yes, we do have a service account for running jobs once they're in production, but for this current one...which I am just setting up, I am using my account. Once it is all working, i would swtich to our service account.
Hi all -
Finally figured this out. I need to create a sql server Credential and Proxy. For testing, I used my own account (that has the necessary AWS credentials) as the one associated with the Sql Credential. That will be replaced a service account later.