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


By:   |   Updated: 2021-09-30   |   Comments   |   Related: > Azure


Problem

In an earlier tip, I saw how to perform SQL database backups using file snapshot backups. In that tip, I saw how SQL Server data files can be created directly on Azure storage. Is there a way by which we can encrypt the data files on Azure by implementing transparent date encryption (TDE) locally on an on-premises server?

Solution

This tutorial assumes that you already have resources in your Azure environment and you are familiar with the Azure portal. If you are new to Azure, you can subscribe to a trial account here, which will give you access to Azure services for a year and a credit of USD $200. It is assumed, that you already have SQL Server databases that have their database files stored directly on Azure storage. If you don't have one, you can refer this tip for setting up sample databases that make use of this feature. In this tip, we will perform a demo by detaching and attaching a test database from one SQL instance to another with and without TDE enabled for the database whose database files reside on Azure storage account. This tip assumes you are already familiar with the concept of TDE. To learn about TDE, you can refer this tip.

What happens when TDE is not enabled and you try to detach and attach the database

In this section, we will test a scenario where TDE is not enabled on the on-premises server and the data files for the SQL Server database resides on Azure storage. We can try to detach and attach the database from one SQL Server instance to another. In this demo, both the SQL instances are on-premises servers but the SQL Server data files reside on Azure storage. You can refer to this tip for sample scripts to create the SQL Server data files on Azure storage.

As shown in the screenshot, you can see a database named motest on a SQL Server instance.

database data fileson azure storage

As you can see the SQL Server database files reside on Azure storage. We can detach this database and try to attach it to another SQL Server instance. For detaching database, right click on the database and click on Tasks > Detach as shown.

detach sql database

You will see this screen and click on 'OK'. Drop connections if any and click on OK to detach the database.

detach sql database

Next, connect to another SQL Server instance to attach the database. You can use below script to attach the database. Make sure, the credentials are created on the other SQL instance where you will attach the database. You can refer to this tip for creating the credentials using the SAS token generated from the Azure storage account where the SQL Server database files are stored.

-- Make sure to update the correct paths
USE master;  
GO
  
CREATE DATABASE motest   
    ON (FILENAME = 'https://sqldbcontainermo.blob.core.windows.net/moh/motest_data.mdf'),  
    (FILENAME = 'https://sqldbcontainermo.blob.core.windows.net/moh/motest_log.ldf')  
    FOR ATTACH;  
GO

Once done, you will see the database attached to the other SQL instance as shown.

create database script

By using this method, you use how easily the databases can be detached from one SQL instance and attached to another SQL instance just by a few clicks. It may be a bit risky to have this kind of easy option on critical systems as database security can be easily compromised.

In this next section, we will see how we can make use of the TDE option in order to safe guard the database files further.

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.

query results

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.

create database error

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.

restored certificates

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.

database files

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

database encrypted status

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





get scripts

next tip button



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

View all my tips


Article Last Updated: 2021-09-30

Comments For This Article





download














get free sql tips
agree to terms