Manage TDE Keys On Premises with SQL Data Files Stored in Azure

In this section, we will enable TDE for the test database and then try to detach and attach the database from one SQL instance to another. In order to learn more about TDE, you can refer to this tip. In this section, we will implement the steps to enable TDE for the test database.

As mentioned in the official Microsoft documentation, we need to follow these steps.

• Create a master key
• Create or obtain a certificate protected by the master key
• Create a database encryption key and protect it by using the certificate
• Set the database to use encryption

As a first step, we can create the master key using below script on the source SQL Server instance.

--Provide a strong password
USE Master;
GO

CREATE MASTER KEY ENCRYPTION
BY PASSWORD='!Us3Str0nGPa33w0rd';
GO 

Next, you can create the certificate which is protected by the master key.

--Create a certificate
CREATE CERTIFICATE TDE_Cert_motest
WITH 
SUBJECT='Database Encryption Certificate';
GO

Next, you can create the database encryption key and protect is using the certificate. Run this on the test database.

--Create the database encryption key on the correct database
USE motest
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert_motest;
GO

Once you run above command, you will get this warning below.

"Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database."

So, make sure to perform a backup of the certificate and private key associate with the certificate using below command.

-- Backup the certificate and private key
USE master
GO

BACKUP CERTIFICATE TDE_Cert_motest
TO FILE = 'C:\Temp\SQLTip\TDE_Cert_motest'
WITH PRIVATE KEY (file='C:\Temp\SQLTip\TDE_Cert_motest.pvk',
ENCRYPTION BY PASSWORD='!Us3Str0nGPa33w0rd')

Make sure to secure the backups as we will need this for restoring on to the destination SQL instance. Finally, you can enable encryption on the test database

--Enable encryption on the test database
ALTER DATABASE motest
SET ENCRYPTION ON;
GO

With this, TDE is enabled for the test database. You can validate using this script.

--Script to check the encryption status
select DB_NAME(database_id) AS [Database_Name], 
       encryption_State=
      case 
        when encryption_state='3' then 'Encrypted'
        else 'Unencrypted'
      end
from sys.dm_database_encryption_keys
where encryption_state='3'

The output will be as below.

Try to detach and attach the database after TDE is enabled

As TDE is enabled for the test database, try to repeat the steps as described in the earlier section by detaching and attaching the database. After detaching the database on the source SQL instance, use the script provided in the earlier section to attach the database on to another SQL instance. You will get an error as shown.

As you can see, you get this error "Cannot find server certificate with thumbprint '0xD7D6A7F306972433636C75974DA38FD5B7C90206'"

This is because the certificate we used for TDE is not restored on to this SQL instance. As you can see, you can't just detach and attach a database from one SQL instance to another once TDE is enabled unless the related certificates are transferred as well thereby enhancing security.

Steps to transfer the certificate from source SQL instance to destination

In order to successfully attach the database on to the new SQL instance, we need to restore the certificate using the backup we performed in the earlier section. But before that we need to create the master key on the new SQL instance. You can use the below script.

-- Create master key on new SQL instance
USE Master;
GO

CREATE MASTER KEY ENCRYPTION
BY PASSWORD='!Us3Str0nGPa33w0rd';
GO

Next, the certificate can be restored using below script. Make sure to provide correct password, use the correct names and backup file locations.

-- Restore the certificate from backup
USE MASTER
GO

CREATE CERTIFICATE TDE_Cert_motest
FROM FILE = 'C:\Temp\SQLTip\TDE_Cert_motest'
WITH PRIVATE KEY (FILE = 'C:\Temp\SQLTip\TDE_Cert_motest.pvk',
DECRYPTION BY PASSWORD = '!Us3Str0nGPa33w0rd');

With this the certificate will be successfully restored on the destination SQL instance where you want the database to be attached.

Attach the database on to the SQL instance

Once the certificate is restored, you can try to attach the database to the SQL instance. You can validate the certificate is restored by either querying sys.certificates catalog view or you can checking using SSMS as shown.

You will see that the test database gets successfully attached to the destination SQL instance. You can perform few simple validations like running sp_helpdb or by using the earlier script to validate if encryption is enabled for the database.

Output of sp_helpdb is below.

You can see the database files reside directly on Azure storage. By using the earlier script, you can confirm that encryption is enabled.

With this, you can see how we can safeguard the databases by using TDE locally on the on-premises servers but at the same time have the database files reside on Azure storage. We have total control of the certificates used for encryption as we are in charge of maintaining the backups of the certificates securely on on-premises infrastructure. With this approach, we have sort of implemented a hybrid solution where we are making use of the features of Azure storage and at the same time not compromising on security by any means thereby having the best of both worlds.

Next Steps

• You used the sample scripts from this tip, in order to first create a SQL database that has both the database files on Azure storage
• You can refer the examples described in this tip to try to detach and attach a test SQL database without TDE enabled
• After enabling TDE using the sample scripts, try to detach the database and then attach
• After restoring the certificate used in TDE, perform an attach to confirm it works as expected

About the author

Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips