TDE is a SQL Server feature which encrypts your data at rest, i.e. your database files. When TDE is enabled encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE does not increase the size of the encrypted database. Here is TDE architecture schema from MSFT documentation:
Transparent Data Encryption Architecture
This blog post explains how to enable Transparent Data Encryption (TDE) for SQL Database (on-premise/Azure).
Scenario 1. On-premise SQL Server 2017 (this will also work for SQL Server in a Azure VM). You can use the following SQL script to enable TDE:
Be sure to replace ‘K2’ with your target database name and adjust password value. Script uses IF clauses to avid creating things which already exist (which are missing in the sample script you can find in MSFT documentation). Once TDE is enabled you can confirm this in the database properties using SSMS GUI:
Scenario 2. Azure SQL Database. Script mentioned above won’t work here. Easiest/default approach to enable TDE for Azure SQL Database is to do so from Azure Portal:
This approach called service-managed transparent data encryption and by default database encryption key is protected by a built-in server certificate. All newly created SQL databases are encrypted by default by using service-managed transparent data encryption.
Other approach called Bring Your Own Key and requires use of Azure Key Vault.
TDE can also be managed with PowerShell, Transact-SQL and REST API. PowerShell contains number of cmdlets for that:
And using T-SQL you can use ALTER DATABASE (Azure SQL Database) SET ENCRYPTION ON/OFF command (encrypts or decrypts a database) and two dynamic management views:
- databasesys.dm_database_encryption_keys which returns information about the encryption state of a database and its associated database encryption keys
- sys.dm_pdw_nodes_database_encryption_keys which returns information about the encryption state of each data warehouse node and its associated database encryption keys
Once TDE has been enabled there is also options to check whether it is enabled or not using T-SQL:
For further information refer to official MSFT documentation: