AWS Database Blog

Cross-account migration of HAQM RDS for SQL Server with column-level encryption

Organizations running SQL Server workloads on HAQM Relational Database Service (HAQM RDS) sometimes need to migrate their databases to different AWS accounts. This migration becomes more complex when mission-critical data requires column-level encryption to meet compliance requirements.

In this post, we demonstrate how you can migrate your symmetric key-encrypted database on HAQM RDS for SQL Server to another AWS account without compromising security. The solution we present can also help you implement symmetric key encryption on a new database in HAQM RDS for SQL Server.

Solution overview

In this solution, we demonstrate how to migrate a database that uses SQL Server certificate and symmetric key encryption from one HAQM RDS for SQL Server instance to another instance in a different AWS account. The workflow architecture illustrated in the diagram outlines the key components and steps of this migration process.

Before implementing this solution in your environment, note that it requires creating new AWS resources that will incur costs. For detailed pricing information, refer to AWS Pricing. We strongly recommend testing this solution in a non-production environment and performing comprehensive validation before deploying it in production.

Prerequisites

This post assumes that you’re familiar with navigating the AWS Management Console. For this example, you need the following resources and services enabled in your AWS account:

  1. An HAQM RDS for SQL Server instance in the source and target account
  2. An HAQM S3 bucket in the target account and grant access to the source RDS
  3. A SQL Server certificate
  4. A SQL Server symmetric key

Walkthrough

  1. Configure an HAQM Simple Storage Service (HAQM S3) bucket and its policy in the target account to allow access from the source account
  2. Setup a sample database on the source RDS instance
  3. Back up the SQL Server certificate and symmetric key
  4. Convert the SQL Server certificate and symmetric key into binary data
  5. Back up the SQL Server database from the source account to the S3 bucket in the target account
  6. Restore the database backup to the RDS for SQL Server instance in the target account
  7. Drop and recreate the symmetric key, certificate, and primary key in the RDS for SQL Server database in the target account
  8. Validate the encrypted data on the target account

Configure the S3 bucket in the target account to allow access from the source account

Refer to Setting up for cross-account native backup and restore in HAQM RDS for Microsoft SQL Server for detailed steps to configure the S3 bucket in the target account to allow access from the source account.

Setup a sample database on the source RDS instance

The following sample SQL commands will create a database and a table, insert rows after table creation. Further create certificate, symmetric key and encrypt a column with the certificate created.

--Create database
CREATE DATABASE employeedetails;
GO
--Create a master key in the BikeStores database:
USE employeedetails;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Your_password_here';
GO
--Create a self-signed certificate named SQLCertificateemployeedetails and then a symmetric key using this self-signed certificate with the AES_256 algorithm:
USE employeedetails;
GO
CREATE CERTIFICATE SQLCertificateemployeedetails
WITH SUBJECT = 'employeedetails Encryption';
GO
USE employeedetails;
GO
CREATE SYMMETRIC KEY Sym_Key_employeedetails
WITH ALGORITHM = AES_256,
IDENTITY_VALUE = 'AWS RDS Restore', KEY_SOURCE = 'Source RDS SQL Server'
ENCRYPTION BY CERTIFICATE SQLCertificateemployeedetails
GO
--Verify the creation in SSMS under employeedetails, Security, Certificates and under employeedetails, Security, Symmetric Keys.
--Create the Employee table and insert sample data in the table, and encrypt the data in the column encrypted_ssn by using the function ENCRYPTBYKEY with the symmetric key created in the previous step:
CREATE TABLE employees (
	emp_id INT IDENTITY (1, 1) PRIMARY KEY,
	first_name VARCHAR (255) NOT NULL,
	last_name VARCHAR (255) NOT NULL,
	phone VARCHAR (25),
	email VARCHAR (255) NOT NULL,
	street VARCHAR (255),
	city VARCHAR (50),
	state VARCHAR (25),
	zip_code VARCHAR (5),
	--ssn VARCHAR (15) NULL,
	encrypted_ssn VARBINARY (MAX) NULL
);
GO
OPEN SYMMETRIC KEY Sym_Key_employeedetails DECRYPTION BY CERTIFICATE SQLCertificateemployeedetails
GO
INSERT INTO dbo.employees(first_name, last_name, phone, email, street, city, state, zip_code, encrypted_ssn) VALUES('Kasha','Todd',NULL,'kasha.todd@yahoo.com','910 Vine Street ','Campbell','CA',95008,ENCRYPTBYKEY(KEY_GUID('Sym_Key_employeedetails'), convert(varbinary,'111-111-1111'))); 
INSERT INTO dbo.employees(first_name, last_name, phone, email, street, city, state, zip_code, encrypted_ssn) VALUES('Tameka','Fisher',NULL,'tameka.fisher@aol.com','769C Honey Creek St. ','Redondo Beach','CA',90278,ENCRYPTBYKEY(KEY_GUID('Sym_Key_employeedetails'), convert(varbinary,'222-222-2222')));
INSERT INTO dbo.employees(first_name, last_name, phone, email, street, city, state, zip_code, encrypted_ssn) VALUES('Daryl','Spence',NULL,'daryl.spence@aol.com','988 Pearl Lane ','Uniondale','NY',11553,ENCRYPTBYKEY(KEY_GUID('Sym_Key_employeedetails'), convert(varbinary,'333-333-3333')));
INSERT INTO dbo.employees(first_name, last_name, phone, email, street, city, state, zip_code, encrypted_ssn) VALUES('Charolette','Rice','(916) 381-6003','charolette.rice@msn.com','107 River Dr. ','Sacramento','CA',95820,ENCRYPTBYKEY(KEY_GUID('Sym_Key_employeedetails'), convert(varbinary,'444-444-4444')));
INSERT INTO dbo.employees(first_name, last_name, phone, email, street, city, state, zip_code, encrypted_ssn) VALUES('Lyndsey','Bean',NULL,'lyndsey.bean@hotmail.com','769 West Road ','Fairport','NY',14450,ENCRYPTBYKEY(KEY_GUID('Sym_Key_employeedetails'), convert(varbinary,'555-555-5555')));
SELECT * from dbo.employees;
GO
--Perform the following typical SQL operation, which involves performing an OPEN SYMMETRIC KEY operation to decrypt the symmetric key and make it available for use:
--OPEN SYMMETRIC KEY Sym_Key_employeedetails DECRYPTION BY CERTIFICATE SQLCertificateemployeedetails
--GO
SELECT first_name, last_name, CONVERT(varchar, DecryptByKey(encrypted_ssn)) AS Unencrypted_ssn FROM [dbo].[Employees];

Back up the SQL Server certificate and symmetric key

To migrate a database with column-level encryption to the RDS for SQL Server instance from another account, you must migrate both the SQL Server certificate and the symmetric key.

Before you begin, refer to the following screenshot. This post assumes that the encrypted_ssn column in the dbo.employees table of the employeedetails database contains encrypted data. The encryption was performed using the SQL certificate named SQLCertificateemployeedetails and the symmetric key named Sym_Key_employeedetails.

The following screenshot shows commands to view the unencrypted data of the dbo.employees table.

To back up the SQL Server certificate and private key from the source RDS instance, run the following command. The password used here must match the same password used to create the primary key on the source server.

USE employeedetails
BACKUP CERTIFICATE SQLCertificateemployeedetails TO FILE = 'D:\RDSDBDATA\data\BackupemployeedetailsCert.cert'
WITH PRIVATE KEY (FILE = 'D:\RDSDBDATA\data\BackupemployeedetailsCert.pvk',
ENCRYPTION BY PASSWORD = 'Your_password_here')
GO

Convert the SQL Server certificate and symmetric key into binary data

Both the symmetric key and the certificate must be migrated to the target RDS instance because they form an encryption hierarchy in SQL Server’s encryption system. Because HAQM RDS for SQL Server instances don’t allow direct file system access, you can’t directly copy certificate files to RDS instances. OPENROWSET provides a workaround for this limitation. To convert the SQL Server certificate and symmetric key into binary data, complete the following steps:

  1. To convert the backup of the SQL Server certificate using the function OPENROWSET, run the following command: 
    USE employeedetails;
SELECT 1, BulkColumn
FROM OPENROWSET(BULK N'D:\RDSDBDATA\data\BackupemployeedetailsCert.cert', SINGLE_BLOB) rowset
GO

  2. Copy the output of the column named BulkColumn and save it to a text file for later use on the target RDS instance.
  3. Similarly, run the following command to convert the backup of the private key into binary data: 
    USE employeedetails
SELECT 1, BulkColumn
FROM OPENROWSET(BULK N'D:\RDSDBDATA\data\BackupemployeedetailsCert.pvk', SINGLE_BLOB) rowset
GO

  4. Copy the output of the column named BulkColumn and save it to a text file for later use on the target RDS instance.

Now that you have created a binary copy of the SQL Server certificate and private key, proceed to the next step.

Back up the SQL Server database from the source account to the S3 bucket in the target account

To be able to back up and restore the database on HAQM RDS, the native SQL Server backup and restore option must be enabled and configured. Complete the following steps to back up the employeedetails database from the source account to the S3 bucket configured in the previous step:

  1. Run the following command to back up the employeedetails database to your HAQM S3 bucket. 
    exec msdb.dbo.rds_backup_database
@source_db_name='employeedetails',
@s3_arn_to_backup_to='arn:aws:s3:::your_bucket_name_here/employeedetails.bak',
@overwrite_S3_backup_file=1;
  2. To check the status of the database backup, run the following command: 
    exec msdb.dbo.rds_task_status @db_name='employeedetails';

You can refer to the lifecycle column in the command output to check for the database backup completion. When the command displays SUCCESS, you should see the database backup file on the target S3 bucket.

Restore database backup to the RDS for SQL Server instance in the target account

Now that the source database backup is available in the S3 bucket, follow these steps to restore the database onto the target RDS instance:

  1. Connect to Target RDS for SQL Server from SQL Server Management Studio and run the following command to restore the database: 
    exec msdb.dbo.rds_restore_database
@restore_db_name='employeedetails',
@s3_arn_to_restore_from='arn:aws:s3:::your_bucket_name_here/employeedetails.bak';
GO
  2. You can check the status of the database restore by running the following command: 
    exec msdb.dbo.rds_task_status @db_name='employeedetails';

  3. Validate that the employeedetails database was restored along with the certificate and symmetric keys.

Although the SQL Server certificate and symmetric key are restored in the employeedetails database, the data from the encrypted column is not accessible. You can run the following query to verify the results. Notice the output of the encrypted_ssn column shows NULL.

USE employeedetails
SELECT first_name, last_name, CONVERT(varchar, DecryptByKey(encrypted_ssn)) AS Unencrypted_ssn FROM [dbo].[employees];

The preceding output is due to the fact that the master key is not encrypted by the target RDS for SQL Server instance. Run the following command to verify.

SELECT d.is_master_key_encrypted_by_server,* FROM sys.databases AS d;

Notice the output value of the is_master_key_encrypted_by_server column. If the output is 1, the key is encrypted by the same server; otherwise, it is not.

Drop and recreate the symmetric key, certificate, and primary key in the RDS for SQL Server database in the target account

To access the encrypted data transferred to the target account, drop and recreate the target database’s existing symmetric key, certificate, and primary key. Refer to the following steps:

  1. Drop the symmetric key, certificate, and primary key in this order using the following command: 
    USE employeedetails;
DROP SYMMETRIC KEY [Sym_Key_employeedetails];
DROP CERTIFICATE [SQLCertificateemployeedetails];
DROP MASTER KEY;

    Dropping the symmetric key, certificate, and primary key doesn’t affect the existing encryption on other databases because the certificate or the key deletion is associated with a particular database only.

  2. Use the following command to create the primary key for the employeedetails database on the RDS for SQL Server instance in the target account. Use the same password you used to create the primary key on the source server. Using the same password maintains the encryption chain integrity and ensures successful data access after migration. 
    USE employeedetails;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Your_password_here';
GO
  3. Next, create the SQL Server certificate using the binary data output of the SQL Server certificate and the private key generated in the steps 1 and 3 executed on the source RDS instance. Run the following command to create the SQL Server certificate: 
    USE employeedetails;
CREATE CERTIFICATE SQLCertificateemployeedetails
  FROM BINARY = 0x308202D2308201BAA00302010***
  WITH PRIVATE KEY ( BINARY = 0x1EF1B5B00000000001000000010000001***
  , DECRYPTION BY PASSWORD = 'Your_password_here')
GO
  4. To create the symmetric key using the SQL Server certificate, run the following command: 
    USE employeedetails
CREATE SYMMETRIC KEY Sym_Key_employeedetails
WITH ALGORITHM = AES_256,
IDENTITY_VALUE = 'AWS RDS Restore', KEY_SOURCE = 'Source RDS SQL Server'
ENCRYPTION BY CERTIFICATE SQLCertificateemployeedetails
GO
  5. Make sure that the symmetric key, certificate, and primary key are recreated successfully. To validate that the key is server encrypted, run the following command: 
    USE employeedetails
SELECT d.is_master_key_encrypted_by_server,* FROM sys.databases AS d;
GO

Validate the encrypted data on the target account

Finally, to validate the encrypted data on the employeedetails database in the target account, use the following command:

USE employeedetails
OPEN SYMMETRIC KEY Sym_Key_employeedetails DECRYPTION BY CERTIFICATE SQLCertificateemployeedetails
GO
SELECT first_name, last_name, CONVERT(varchar, DecryptByKey(encrypted_ssn)) AS Unencrypted_ssn FROM [dbo].[employees];

Clean up

Remember to clean up AWS resources that you no longer need in order to avoid additional costs. Please refer to Deleting a DB instance for more.

Conclusion

In this post, we showed you how to securely migrate a column-level encrypted database from one HAQM RDS for SQL Server instance to another AWS account. This solution also enables you to migrate self-managed SQL Server databases with symmetric key encryption to HAQM RDS, even without direct host access. By following these steps, you can maintain data security while performing cross-account database migrations.We encourage you to try this solution for your specific use case and share your experience in the comments section below.

About the authors

Aruna Gangireddy

Aruna Gangireddy

Aruna is a Consultant with AWS Professional Services. She specializes in SQL Server and database technologies, helping customers successfully migrate and optimize their Microsoft workloads on AWS. With deep expertise in both homogeneous and heterogeneous migrations, Aruna enables customers to seamlessly transition their workloads from on-premises environments to AWS Cloud.

Sushant Deshmukh

Sushant Deshmukh

Sushant is a Sr Partner Solutions Architect at AWS, collaborating with Global System Integrators to design highly available, scalable, resilient, and secure cloud architectures. He specializes in helping AWS customers and partners successfully migrate and modernize their applications on AWS. When not architecting cloud solutions, Sushant enjoys exploring new destinations and cuisines, playing volleyball, and spending time with family and friends.

Minesh Chande

Minesh Chande

Minesh is a Senior Database Specialist Solutions Architect at AWS, focused on helping customers across diverse industries optimize their database solutions. He specializes in designing, migrating, and enhancing SQL Server workloads on managed database platforms such as HAQM RDS, HAQM RDS Custom, and Babelfish for Aurora PostgreSQL.