Transparent Data Encryption with Azure SQL Database

THIS TOPIC APPLIES TO: noSQL Server yesAzure SQL DatabaseyesAzure SQL Data Warehouse noParallel Data Warehouse

Azure SQL Database transparent data encryption helps protect against the threat of malicious activity by performing real-time encryption and decryption of the database, associated backups, and transaction log files at rest without requiring changes to the application.

TDE encrypts the storage of an entire database by using a symmetric key called the database encryption key. In SQL Database the database encryption key is protected by a built-in server certificate. The built-in server certificate is unique for each SQL Database server. If a database is in a GeoDR relationship, it is protected by a different key on each server. If 2 databases are connected to the same server, they share the same built-in certificate. Microsoft automatically rotates these certificates at least every 90 days. For a general description of TDE, see Transparent Data Encryption (TDE).

Azure SQL Database does not support Azure Key Vault integration with TDE. SQL Server running on an Azure virtual machine can use an asymmetric key from the Key Vault. For more information, see Extensible Key Management Using Azure Key Vault (SQL Server).

Permissions

To configure TDE through the Azure portal, by using the REST API, or by using PowerShell, you must be connected as the Azure Owner, Contributor, or SQL Security Manager.

To configure TDE by using Transact-SQL requires the following:

  • To execute the ALTER DATABASE statement with the SET option requires membership in the dbmanager role.

Enable TDE on a Database Using the Portal

  1. Visit the Azure Portal at https://portal.azure.com and sign-in with your Azure Administrator or Contributor account.

  2. On the left banner, click to BROWSE, and then click SQL databases.

  3. With SQL databases selected in the left pane, click your user database.

  4. In the database blade, click All settings.

  5. In the Settings blade, click Transparent data encryption part to open the Transparent data encryption blade.

  6. In the Data encryption blade, move the Data encryption button to On, and then click Save (at the top of the page) to apply the setting. The Encryption status will approximate the progress of the transparent data encryption.

    SQL Database TDE Start Encryption

    You can also monitor the progress of encryption by connecting to SQL Database using a query tool such as SQL Server Management Studio as a database user with the VIEW DATABASE STATE permission. Query the encryption_state column of the sys.dm_database_encryption_keys view.

Enabling TDE on SQL Database by Using Transact-SQL

The following steps enable TDE.

  1. Connect to the database using a login that is an administrator or a member of the dbmanager role in the master database.

  2. Execute the following statements to encrypt the database.

    -- Enable encryption  
    ALTER DATABASE [AdventureWorks] SET ENCRYPTION ON;  
    GO  
    
  3. To monitor the progress of encryption on SQL Database, database users with the VIEW DATABASE STATE permission can query the encryption_state column of the sys.dm_database_encryption_keys view.

Enabling and Disabling TDE on SQL Database by Using PowerShell

Using the Azure PowerShell you can run the following command to turn TDE on/off. You must connect your account to the PS window before running the command. Customize the example to use your values for the ServerName, ResourceGroupName, and DatabaseName parameters. For additional information about PowerShell, see How to install and configure Azure PowerShell.

Note

To continue, you should install and configure version 1.0 of Azure PowerShell. Version 0.9.8 can be used but it is deprecated and it requires switching to the AzureResourceManager cmdlets by using the PS C:\> Switch-AzureMode -Name AzureResourceManager command.

  1. To enable TDE, return the TDE status, and view the encryption activity:

    PS C:\> Set-AzureRMSqlDatabaseTransparentDataEncryption -ServerName "myserver" -ResourceGroupName "Default-SQL-WestUS" -DatabaseName "database1" -State "Enabled"  
    
    PS C:\> Get-AzureRMSqlDatabaseTransparentDataEncryption -ServerName "myserver" -ResourceGroupName "Default-SQL-WestUS" -DatabaseName "database1"  
    
    PS C:\> Get-AzureRMSqlDatabaseTransparentDataEncryptionActivity -ServerName "myserver" -ResourceGroupName "Default-SQL-WestUS" -DatabaseName "database1"  
    

    If using version 0.9.8 use the Set-AzureSqlDatabaseTransparentDataEncryption, Get-AzureSqlDatabaseTransparentDataEncryption, and Get-AzureSqlDatabaseTransparentDataEncryptionActivity commands.

  2. To disable TDE:

    PS C:\> Set-AzureRMSqlDatabaseTransparentDataEncryption -ServerName "myserver" -ResourceGroupName "Default-SQL-WestUS" -DatabaseName "database1" -State "Disabled"  
    

    If using version 0.9.8 use the Set-AzureSqlDatabaseTransparentDataEncryption command.

Decrypting a TDE Protected Database on SQL Database

To Disable TDE by Using the Azure Portal

  1. Visit the Azure Portal at https://portal.azure.com and sign-in with your Azure Administrator or Contributor account.

  2. On the left banner, click to BROWSE, and then click SQL databases.

  3. With SQL databases selected in the left pane, click your user database.

  4. In the database blade, click All settings.

  5. In the Settings blade, click Transparent Data encryption part to open the Transparent data encryption blade.

  6. In the Transparent data encryption blade, move the Data encryption button to Off, and then click Save (at the top of the page) to apply the setting. The Encryption status will approximate the progress of the transparent data decryption.

    You can also monitor the progress of decryption by connecting to SQL Database using a query tool such as Management Studio as a database user with the VIEW DATABASE STATE permission. Query the encryption_state column of the sys.dm_database_encryption_keys view.

To Disable TDE by Using Transact-SQL

  1. Connect to the database using a login that is an administrator or a member of the dbmanager role in the master database.

  2. Execute the following statements to decrypt the database.

    -- Enable encryption  
    ALTER DATABASE [AdventureWorks] SET ENCRYPTION OFF;  
    GO  
    
  3. To monitor the progress of encryption on SQL Database, database users with the VIEW DATABASE STATE permission can query the encryption_state column of the sys.dm_database_encryption_keys view.

Moving a TDE Protected Database on SQL Database

You do not need to decrypt databases for operations within Azure. The TDE settings on the source database or primary database are transparently inherited on the target. This includes operations involving:

  • Geo-Restore

  • Self-Service Point in Time Restore

  • Restore a Deleted Database

  • Active Geo_Replication

  • Creating a Database Copy

    When exporting a TDE protected database using the Export Database function in the Azure SQL Database Portal or the SQL Server Import and Export Wizard, the exported content of the database is not encrypted. This exported content is stored in unencrypted .bacpac files. Be sure to protect the .bacpac files appropriately and enable TDE once import of the new database is completed.

    For example, if the .bacpac file is exported from an on-premises SQL Server, then the imported content of the new database will not be automatically encrypted. Likewise, if the .bacpac file is exported from an Azure SQL Database to an on-premises SQL Server, the new database is also not automatically encrypted.

    The one exception is when exporting to and from Azure SQL Database – TDE will be enabled in the new database, but the .bacpac file itself is still not encrypted.

Enable TDE on SQL Server Using EKM

See Also

Transparent Data Encryption (TDE)
CREATE CREDENTIAL (Transact-SQL)
CREATE ASYMMETRIC KEY (Transact-SQL)
ALTER DATABASE (Transact-SQL)