1

Looking to see if anyone else having this issue. I have a windows server 2019/sql server 2022 ec2 instance that has a instance profile using a role/policy to upload to an s3 bucket. I created a policy to allow to list,read,write etc.. to the s3 bucket and have it attached to a role. The role has been added to the ec2 instance. Using the aws cli, and I can list the files in the bucket and upload to the bucket. But in SSMS I try to run:

BACKUP DATABASE SampleDB
TO URL = 's3://xxxxxdemobucket/server1/sampledb.bak'
WITH FORMAT, COMPRESSION, MAXTRANSFERSIZE = 20971520;

Msg 3201, Level 16, State 1, Line 2 Cannot open backup device 's3://xxxxxdemobucket/server1/sampledb.bak'. Operating system error 86(The specified network password is not correct.). Msg 3013, Level 16, State 1, Line 2 BACKUP DATABASE is terminating abnormally.

I read that you need to add aws credentials to sql server to upload to aws s3, but since I'm using the role in the instance profile I figure I shouldn't have too.

Rohit Gupta
  • 1,626
  • 6
  • 17
  • 19
KC_IT
  • 11
  • 1

1 Answers1

0

TL;DR

Only because I spent over a day figuring this out myself, I have some pointers for you, as well as other readers. Without assuming too much about your exact setup on AWS, try these tips in order and I am near certain you will get it fixed. Unfortunately, most AWS documentation revolves around users doing backups / restores from RDS, so it can be a bit of a struggle for someone trying to piece things together for EC2 hosted SQL Server instances!

1) Your URL is wrong

Instead of this...

BACKUP DATABASE SampleDB
TO URL = 's3://xxxxxdemobucket/server1/sampledb.bak'

You need something like this..

BACKUP DATABASE SampleDB
TO URL = 's3://xxxxxdemobucket.s3.us-east-2.amazonaws.com/server1/sampledb.bak'

It's a bit of a misnomer, because your error will look something like this.

Cannot open backup device 's3://xxxxxdemobucket/server1/sampledb.bak'. Operating system error 86(The specified network password is not correct.).

Which makes it seems like a permission or password issue, but it's really that your ec2 instance fails to resolve the s3 endpoint... if this still doesn't fix it, keep reading, you may only be a few clicks away from this working!

2) You do need credentials for SQL Server

Yes, the IAM role will allow traffic between your EC2 instance and your S3 bucket, but for SQL Server to execute a backup from a URL, I am quite certain that you need to declare a set of credentials on your SQL Server instance like so.

CREATE CREDENTIAL [s3://xxxxxdemobucket.s3.us-east-2.amazonaws.com] 
WITH 
    IDENTITY = 'S3 Access Key', 
    SECRET = '<ACCESS_KEY>:<SECRET_KEY>'
;

In order to get your <ACCESS_KEY> and <SECRET_KEY>, create a set of credentials for a new IAM user with full access to s3 (worry about least privileged after you get it working). After the credentials are created, if this is still not working, I am betting the last tip will be your solution!

3) Your VPC is missing the s3 endpoint

In order for your ec2 instance to resolve the following endpoint

xxxxxdemobucket.s3.us-east-2.amazonaws.com

You need to "create a VPC endpoint" for the following "AWS service" and attach it to the VPC that is hosting your ec2 instance - since there are two AWS services with this name, you will want to select the one with Gateway as type.

com.amazonaws.us-east-2.s3

After this endpoint is created, wait a few minutes for the endpoint status to show as "Available" in the endpoints tab of VPC section and give it another shot.

Good luck!!

Mr. Vanderstelt
  • 1