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"

AWS CLI default installation path

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

System Environment Variables on Control Panel

Click Environment Variables button

Windows System properties screen

Among system variables, select "path" and check the installation folder of AWS CLI tool is in the list.

system environment variables

Amazon AWS CLI path is in the list below

System Environment Path Variable for AWS CLI command

Test if AWS is accessible by launching a new Command Prompt screen and typing "AWS" in command prompt

test AWS CLI setup

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.

aws configure for AWS CLI configuration

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


aws s3 cp "D:\Data\kodyaz.txt" "s3://aws-datavirtuality/kodyaz.txt"

Code

AWS CLI command sample to copy local text file to Amazon S3 bucket

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


EXEC master..xp_cmdshell 'whoami'

Code

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

$SQL Server Windows account: 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.


EXEC master..xp_cmdshell 'aws s3 cp "D:\Data\kodyaz.txt" "s3://aws-datavirtuality/kodyaz.txt"'

Code

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.

'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.


EXEC master..xp_cmdshell '""C:\Program Files\Amazon\AWSCLIV2\aws.exe"" s3 cp D:\Data\kodyaz.txt s3://aws-datavirtuality/kodyaz.txt'

Code

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:


EXEC master..xp_cmdshell ''

Code

And as we experienced before, AWS S3 command is in following format in its simpliest form


aws s3 cp source_file_full_path target_file_full_path

Code

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:


"C:\Program Files\Amazon\AWSCLIV2\aws.exe" s3 cp "D:\Data\kodyaz file.txt" "s3://aws-datavirtuality/kodyaz file.txt"

Code

run AWS CLI copy file to Amazon S3 bucket command

When we place the same AWS command into the xp_cmdshell command, ...

$'C:\Program' is not recognized as an internal or external command, operable program or batch file.$

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


EXEC master..xp_cmdshell '""C:\Program Files\Amazon\AWSCLIV2\aws.exe"" s3 cp D:\Data\kodyaz.txt s3://aws-datavirtuality/kodyaz.txt'

Code

But when SQL developers apply this syntax on file names including space characters in their path they will get another SQL exception.


EXEC master..xp_cmdshell '""C:\Program Files\Amazon\AWSCLIV2\aws.exe"" s3 cp "D:\Data\kodyaz file.txt" "s3://aws-datavirtuality/kodyaz file.txt"'

Code

The filename, directory name, or volume label syntax is incorrect.

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:


EXEC master..xp_cmdshell '""C:\Program Files\Amazon\AWSCLIV2\aws.exe" s3 cp "D:\Data\kodyaz file.txt" "s3://aws-datavirtuality/kodyaz file.txt""'

Code

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: '" ... "'

SQL Server xp_cmdshell to run AWS CLI command to copy file to Amazon S3 bucket

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.

Unable to locate credentials error when AWS CLI executed on SQL Server

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


aws configure list

Code

aws configure list command for configuration files folder

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

MSSQLSERVER permissions on .aws configuration folder for AWS CLI

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.