July 30, 2021

Transparent Data Encryption (TDE) is a powerful security feature in SQL Server that has been available since SQL Server 2008. It provides an additional layer of protection by encrypting the entire database, including backups and transaction log files, at rest. This means that even if someone were to steal the physical media containing the database, they would not be able to access the data without the encryption key.

Until recently, TDE was not available in Azure SQL Database. However, with the introduction of Azure V12 SQL Databases, TDE is now supported and can be easily implemented.

To enable TDE in an Azure SQL Database, you can execute the following T-SQL command:

ALTER DATABASE [DatabaseName] SET ENCRYPTION ON;

Alternatively, you can enable TDE using the Azure Preview Portal or PowerShell.

Once TDE is enabled, you can check the progress of the encryption by querying the sys.dm_database_encryption_keys dynamic management view. This view provides information about the encryption state and the progress of the encryption process.

It is important to note that while TDE encrypts the database and its associated files, it does not encrypt .bacpac files that are created from the database. This means that if someone gains access to your Azure SQL Administration account, they could export the database to create a .bacpac file and then import it into an on-premises version of SQL Server to access the data.

It is crucial to protect your Azure SQL Administration account with strong passwords and implement additional security measures to prevent unauthorized access to your database.

Transparent Data Encryption is a valuable security feature that adds an extra layer of protection to your SQL Server databases. By encrypting the data at rest, it helps safeguard sensitive information from unauthorized access.

References:

• Announcing Transparent Data Encryption for Azure SQL Database
• Transparent Data Encryption with Azure SQL Database
• sys.dm_database_encryption_keys (Transact-SQL)
• Encrypt a SQL Server Database with Transparent Data Encryption

Tags: azure, security, transparent data encryption