Enabling TDE on Databases Migrated to Managed Instance

Transparent Database Encryption (TDE) is a thorny topic. On-premises it is very often spurned because of the impact that it has on storage and backup systems, nullifying deduplication and compression capabilities. However, when it comes to running databases on cloud platforms it becomes a much bigger concern because the data is hosted outside of our infrastructure.

This results in a situation where databases created in the cloud on Azure SQL Database Managed Instance are protected by default. However, databases that are migrated from on-premises are typically not.

We will look at the process of migrating a database from on-premises SQL Server to Azure SQL Database Managed Instance and then enabling TDE on that database. We will be using built-in backup and restore as the method for moving the database to Managed Instance today.

Accompanying code for this post can be found in my GitHub here.

Database Migration

The database migration process is the same for migrating to Managed Instance as it is for migrating between two on-premises SQL Servers via backup and restore.

Setup Storage Location

We need a storage location that both systems can access, this way the source can backup and destination can restore. In this situation Azure Blob Storage will fulfil that need. The script below will create an Azure storage account and then a container (folder) within it that we will use to store our backup files.

$newStorageAccountParams = @{
     ResourceGroupName = "rg-migrations" 
     Name = "stmigblogpost" 
     SkuName = "Standard_LRS"
     Location = 'UK South' 
     Kind = "StorageV2" 
     AccessTier = "Hot"
     MinimumTlsVersion = "TLS1_2"
     AllowBlobPublicAccess = $false
}
$storageAccount = New-AzStorageAccount @newStorageAccountParams

$newStorageAccountContainerParams = @{
     Name = "dbbackups"
     Context = $storageAccount.Context
}
$container = New-AzStorageContainer @newStorageAccountContainerParams

Create Shared Access key

Because we are using an Azure blob storage account normal file-system permissions will not work for our backup & restore operation. Instead we need to generate a Shared Access Signature which we will use to grant access via SQL Server credentials.

$newSasTokenParams = @{
      Context = $storageAccount.Context
      Service = "Blob"
      ResourceType = "Container","Object"
      Permission = "racwdlup"
      StartTime = ((Get-Date).AddDays(-1))
      ExpiryTime = ((Get-Date).AddDays(7))
 }
 $sasToken = New-AzStorageAccountSASToken @newSasTokenParams

Create Credential for Storage Access

In order to access the Azure storage location we will need to use SQL Server credential objects. These will need to be created on both the source and target servers to allow for the backup to be taken and then restored onto the Managed Instance. Using DbaTools we can quickly and easily create the credential in our on-premises server then copy that directly to the Managed Instance. This way we know that it is the same and if the backup succeeds then the restore operation will get to the files we create.

$newDbaCredentialParams = @{
     SqlInstance = "SQL-01"
     Name = $container.CloudBlobContainer.Uri.AbsoluteUri
     Identity = "Shared Access Signature"
     SecurePassword = (ConvertTo-SecureString -AsPlainText -Force ($sasToken.Replace("?","")))
}
$credential = New-DbaCredential @newDbaCredentialParams
$copyCredentialParams = @{
     Source = "SQL-01"
     Destination = "mi-migrations.public.000000000000.database.windows.net,3342"
     DestinationSqlCredential = (Get-Credential) 
     Name = $credential.Name
}
Copy-DbaCredential @copyCredentialParams

Migrate the database

Now that we have all of the prerequisites in place we can migrate our AdventureWorks2019 database from SQL-01 to our Managed Instance. DbaTools allows us to seamlessly wrap the whole process into a single operation for backup and restore. The script below will take a backup to blob storage and then pipes all of the required details into the restore command.

$backupDatabaseParams = @{
    SqlInstance = "sql-01"
    Database = "Adventureworks2019"
    AzureBaseUrl = $container.CloudBlobContainer.Uri.AbsoluteUri
    Type = "Full"
}
$restoreDatabaseParams = @{
    SqlInstance = "mi-migrations.public.000000000000.database.windows.net,3342"
    SqlCredential = (Get-Credential)
    DatabaseName = "Adventureworks2019"
}
Backup-DbaDatabase @backupDatabaseParams | Restore-DbaDatabase @restoreDatabaseParams

Now we have the database migrated we can see that it is there, however it is not encrypted. Using the following T-SQL we can see that our database is up and running but the is_encrypted flag is set to 0.

SELECT db.name,
        db.create_date,
        db.compatibility_level,
        db.collation_name,
        db.user_access_desc,
        db.state_desc,
        db.recovery_model_desc,
        db.page_verify_option_desc,
        db.is_encrypted
 FROM sys.databases AS db
 WHERE db.database_id > 4
 ;
Screen capture showing database name and encrypted flag not set.

Enable TDE on Migrated Database

Enabling TDE on a migrated Managed Instance database has a lot fewer steps to it than doing so for on-premises systems. Normally, the process involves setting up master keys, certificates, and then encrypting the database. However, on Managed Instance Microsoft have done a lot of the heavy lifting and configuration for us. In order to enable TDE for our migrated database all we need to do is issue the following command.

ALTER DATABASE AdventureWorks2019
     SET ENCRYPTION ON;
 GO

After a few seconds our recently migrated database will have been configured with TDE.

Screen capture of query results showing the is encrypted flag set to 1.

It should be noted that this does not automatically have your database fully encrypted immediately. The SQL Server engine will take some time to complete the operation of encrypting the database files on the storage. How long will this take? It will depend on a number of factors including how busy our server is as well as how large our database is. More information about the TDE scan process can be found here in the Microsoft documentation. For larger databases we can track the encryption progress by using the following query.

DECLARE @DatabaseName SYSNAME = 'Adventureworks2019';
SELECT DB_NAME(dek.database_id) AS DbName,
       dek.encryption_scan_state_desc,
       dek.encryption_state_desc,
       dek.percent_complete,
       dek.encryptor_thumbprint,
       dek.encryptor_type
FROM sys.dm_database_encryption_keys AS dek
WHERE dek.database_id = DB_ID(@DatabaseName)
;
Query results showing encryption status of the Adventureworks2019 database.

Summary

Through this post we have looked at how we can take a database from our on-premises system, move it to Managed Instance, and then protect it with TDE. By doing so we can take advantage of the extra security of encrypting the files which are held on external systems so that we can help ensure that we keep audit and other security functions happy.

It should be noted that the backup file stored in blob storage will not be encrypted. That same can be said for the restored database until we have enabled TDE and the scan has been completed. So there is still a window where our data is not as secure as it could be. In this situation we could look to enable TDE for our on-premises database before we migrate it, but it would be wise to do this shortly before you migrate in order to minimise the impact to storage and backup systems in your environment. The process for migrating an encrypted database to Managed Instance is not too different to the one described here, you can find more information in the two posts where I talk through the process in more detail below.

Migrating TDE Databases to Azure SQL Database Managed Instance – Part 1

Migrating TDE Databases to Azure SQL Database Managed Instance – Part 2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.