Execute AWS CLI Command using xp_cmdshell on SQL Server Database
In this SQL Server tutorial, I want to show how database programmers can run AWS CLI commands using xp_cmdshell procedure to copy local files into Amazon S3 bucket folders. While calling AWS command in SQL Server database with SQL xp_cmdshell procedure, different SQL exceptions can occur because of some missing permissions and configurations as well as some syntax problems. I want to show how SQL developers can resolve these exceptions starting from AWS CLI setup, configuration, enabling xp_cmdshell procedure on SQL Server, specific permissions for SQL Server account "nt service\mssqlserver" and using the correct syntax with parameters including space characters, etc.
Before trying to execute AWS CLI commands on SQL Server, please review below required steps
AWS CLI Setup
Install AWS CLI
Amazon Web Services provided AWS CLI version 2, so if you have not yet installed the new version, you might want to continue with newer version of the AWS CLI tool.
The default installation folde for AWS CLI ver2 is "C:\Program Files\Amazon\AWSCLIV2"
Make sure that the AWS CLI executable path is in system environment variables.
You can use "Search Windows" for "System Environment Variables" on Control Panel
Click Environment Variables button
Among system variables, select "path" and check the installation folder of AWS CLI tool is in the list.
Amazon AWS CLI path is in the list below
Test if AWS is accessible by launching a new Command Prompt screen and typing "AWS" in command prompt
AWS CLI Configuration
In order to provide access to AWS services and resources, you have to configure the default profile and provide valid credentials to your installation.
It is possible to create named profiles at this step, too.
I used default profile and show you here how to configure your default profile for Amazon AWS CLI software.
Open Command Prompt application and type "aws configure"
The AWS CLI application will prompt you to provide valid AWS Access Key ID, AWS Secret Access Key, the default AWS Region name and preferred output format.
If you are not configuring AWS CLI for the first time, and previously entered information is correct, you can press Enter to continue with next data entry.
If this is the first time, you have to enter valid AWS credentials at this step.
Please note that, the AWS Access Key and Secret Access Key is very important, keep these data secure and do not share with others.
You can further test if your credentials are working, if is there any additional problems by making a test.
For example, below command will copy an existing file with the same name into a specific Amazon S3 bucket
For simplicity, please keep folder and files names simple without space characters in their names
OK, we have completed the easy steps :)
Enable xp_cmdshell and Identify SQL Server Account
At this step, launch SQL Server Management Studio and validate if xp_cmdshell is enabled on the SQL Server instance.
You can execute below SQL statement on a new SQL Editor window for example
If you experience below error message, please enable the xp_cmdshell procedure by yourself or with the help of the database administrator.
Please refer to How to Enable xp_cmdshell in SQL Server 2005 using sp_configure
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 [Batch Start Line 0]
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.
If it works successfully, you will also learn the Windows account the SQL Server is working with:nt service\mssqlserver
This information is very critic.
Because while executing AWS CLI commands within SQL Server, we will have to provide access and additional permission to the nt service\mssqlserver Windows account.
SQL developers will experience different errors regarding this missing permissions for the SQL Server account nt service\mssqlserver
'aws' is not recognized as an internal or external command, operable program or batch file.
Now we can start to execute sample AWS CLI command using SQL xp_cmdshell procedure.
Here is the SQL command you can start testing.
You may get below error message although we have set PATH environment variable before
'aws' is not recognized as an internal or external command, operable program or batch file.
In this case, you can prefer to use full path of the AWS CLI executable as seen in below screenshot.
Please note instead of "aws" I used full path "C:\Program Files\Amazon\AWSCLIV2\aws.exe"
In addition to using full path of the AWS CLI executable, I used double quotes twice around the path in xp_cmdshell command
xp_cmdshell and AWS Command with Folder and File Names with Spaces
The xp_cmdshell SQL command executes given string command.
This command is encapsulated with single quote (') character at the beginning and at the end of the statement like '...'
Developers cannot use an additional single quote character in this command. Basically the usage is as follows:
And as we experienced before, AWS S3 command is in following format in its simpliest form
In previous section, we see that we could use double quotes (") character like "..." around the AWS.exe file full path to overcome the problem occured because of the space characters in the full path
For example:
When we place the same AWS command into the xp_cmdshell command, ...
Following error occurs
'C:\Program' is not recognized as an internal or external command, operable program or batch file.
We have already solved this error by using double ("") quotes around the path of the aws executable file like
But when SQL developers apply this syntax on file names including space characters in their path they will get another SQL exception.
The filename, directory name, or volume label syntax is incorrect.
Applying double quotes ("") around all paths will not solve the problem.
Following will not work!
EXEC master..xp_cmdshell '""C:\Program Files\Amazon\AWSCLIV2\aws.exe"" s3 cp ""D:\Data\kodyaz file.txt"" ""s3://aws-datavirtuality/kodyaz file.txt""'
The correct syntax is adding one extra (") within single quotes(')
For example:
Around executable file, source file and target file names; use double quotation marks like "..."
Contain all AWS command within single quote and double quotes as follows: '" ... "'
With above command, I could successfully copy a text file located on my local disk into an Amazon S3 bucket using AWS CLI command within SQL Server.
I executed the AWS CLI command in SQL Server using SQL xp_cmdshell procedure.
Although the file path contain spaces, using the double quotes correctly solved syntax problems.
upload failed: Unable to locate credentials
Another exception that SQL developers can experience while calling AWS CLI tool using xp_cmdshell on SQL Server database, is the "Unable to locate credentials" error message.
Although as logged on user, you validated that you can execute AWS commands successfully in previous steps.
This shows that you have already configured the default profile for AWS CLI tool using "AWS Configure" command.
With executing SQL command "EXEC master..xp_cmdshell 'whoami'" you have already identified the Windows user or service the SQL Server is running as.
Problem is that the credentials you have used successfully in your test is not defined for the SQL Server user or in this case for user nt service\mssqlserver
If you execute "aws configure list" statement on Command Prompt, on Location tab you will see the credentials are stored at .aws folder under current users' profile
So, what I can suggest is just to copy the folder ".aws" from current user's profile
To MSSQLSERVER Windows Account's profile as seen below
Please be sure that the folder permissions enable SQL Server account to access to this folder and its content. You can right click on .aws folder then display Properties and switch to Security tab. By default, SQL Server account MSSQLSERVER is not listed so is not granted any of the permissions seen below. Click Edit and then Add buttons. Search for user nt service\mssqlserver and grant full control to .aws and its contents
These 2 steps will resolve the Unable to locate credentials error and within SQL Server database developers can call AWS CLI using SQL xp_cmdshell procedure.
Enable Access to SQL Server Account to File Folders
If you want to read or write files from SQL Server using xp_cmdshell, the MSSQLServer account should have access to the source or target folders.
For example, my files are under "D:\Data" path so I should do the same and add MSSQLServer user or nt service\mssqlserver with required permissions on the Security tab of the Windows folder jsut like in previous section.