Accessing #AWS #CLI from #SQL Server Agent
The AWS CLI allows you to interact with AWS from the command line, and an obvious use of the tool, is to upload your Database Backups to S3, to keep them safe.
Once you’ve configured AWS to run from the command line on your server, and tested it, then you try the exact same command from xp_cmdshell and it fails with “unable to locate credentials“.
What is happening here, is your remote desktop user, is probably “Adminstrator” but SQL server agent runs under the username MSSQLSERVER
You can verify the username used by SQL server agent by runing the command
xp_cmdshell ‘echo %username%’
The AWS CLI is looking for the configuration file under c:\users\MSSQLSERVER\.aws – but the configuration file is actually under c:\users\Adminstrator\.aws , so you need to copy the configuration file using xp_cmdshell as follows
- Using remote desktop, copy the configuration file to an accessible folder, like C:\temp
- xp_cmdshell ‘md C:\users\MSSQLSERVER\.aws’
xp_cmdshell ‘copy c:\temp\credentials c:\users\MSSQLSERVER\.aws’
- And then test with
xp_cmdshell ‘”C:\Program Files\Amazon\AWSCLI\bin\aws.exe” s3 ls’
Actually
xp_cmdshell ‘echo %userprofile%’ works better!