Skip to content

transparent-data-encryption-with-azure-sql-database.md

Latest commit

 

History

History
166 lines (109 loc) · 12.2 KB

transparent-data-encryption-with-azure-sql-database.md

File metadata and controls

166 lines (109 loc) · 12.2 KB
title Transparent Data Encryption with Azure SQL Database | Microsoft Docs
ms.custom
ms.date 06/13/2017
ms.prod sql-server-2014
ms.reviewer
ms.technology security
ms.topic conceptual
helpviewer_keywords
TDE, SQL Database
Transparent Data Encryption, SQL Database
encryption (SQL Database) TDE
ms.assetid 0bf7e8ff-1416-4923-9c4c-49341e208c62
author CarlRabeler
ms.author carlrab
manager craigg

Transparent Data Encryption with Azure SQL Database

[!INCLUDEssSDSfull] transparent data encryption (preview) 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 [!INCLUDEssSDS] the database encryption key is protected by a built-in server certificate. The built-in server certificate is unique for each [!INCLUDEssSDS] 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. [!INCLUDEmsCoName] automatically rotates these certificates at least every 90 days. For a general description of TDE, see Transparent Data Encryption (TDE).

[!INCLUDEssSDSfull] does not support Azure Key Vault integration with TDE. [!INCLUDEssNoVersion] running on an Azure virtual machine can use an asymmetric key from the Key Vault. For more information, see Example A: Transparent Data Encryption by Using an Asymmetric Key from the Key Vault.
Applies to: [!INCLUDEsqldbesa] (Preview in some regions).

Important

This is currently a preview feature. I acknowledge and agree that implementation of [!INCLUDEssSDS] transparent data encryption in my database(s) is subject to the preview terms in my license agreement (e.g. the Enterprise Agreement, Microsoft Azure Agreement, or Microsoft Online Subscription Agreement), as well as any applicable Supplemental Terms of Use for Microsoft Azure Preview.

The preview of status of TDE applies even in the subset of geographic regions where version family V12 of [!INCLUDEssSDS] is announced as now being in general availability status. TDE for [!INCLUDEssSDS] is not intended for use in production databases until [!INCLUDEmsCoName] announces that TDE is promoted from preview to GA. For more information about [!INCLUDEssSDS] V12, see What's new in Azure SQL Database.

Permissions

To sign up for the preview and 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 [!INCLUDEtsql] requires the following:

  • You must be already signed up for the TDE preview.

  • To create the database encryption key you must be a [!INCLUDEssSDS] administrator or you must be a member of the dbmanager role in the master database and have the CONTROL permission on the database.

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

Sign Up for the Preview of TDE and Enable TDE on a Database

  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 (preview) part to open the Transparent data encryption PREVIEW blade. If you have not already signed up for the TDE preview, the data encryption settings will be disabled until you complete signup.

  6. Click PREVIEW TERMS.

  7. Read the terms of the preview, and if you agree to the terms, select the Transparent Data encryptionPreview terms check box, and then click OK near the bottom of the page. Returning to the Data encryptionPREVIEW blade, where the Data encryption button should now be enabled.

  8. In the Data encryption PREVIEW 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.

    SQLDB_TDE_TermsNewUI

    You can also monitor the progress of encryption by connecting to [!INCLUDEssSDS] using a query tool such as [!INCLUDEssManStudioFull] 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 [!INCLUDEssSDS] by Using Transact-SQL

The following steps, assume you have already signed up for the preview.

  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 create a database encryption key and encrypt the database.

    -- Create the database encryption key that will be used for TDE.
CREATE DATABASE ENCRYPTION KEY 
WITH ALGORITHM = AES_256 
ENCRYPTION BY SERVER CERTIFICATE ##MS_TdeCertificate##;

-- Enable encryption
ALTER DATABASE [AdventureWorks] SET ENCRYPTION ON;
GO

  3. To monitor the progress of encryption on [!INCLUDEssSDS], database users with the VIEW DATABASE STATE permission can query the encryption_state column of the sys.dm_database_encryption_keys view.

Enabling TDE on SQL Database by Using PowerShell

Using the Azure PowerShell you can run the following command to turn TDE on/off. You do have to connect your account to the PS window before running the command. The following steps, assume you have already signed up for the preview. For additional information about PowerShell, see How to install and configure Azure PowerShell.

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

    Switch-AzureMode -Name AzureResourceManager
Set-AzureSqlDatabaseTransparentDataEncryption -ServerName "myserver" -ResourceGroupName "Default-SQL-WestUS" -DatabaseName "database1" -State "Enabled"

Get-AzureSqlDatabaseTransparentDataEncryption -ServerName "myserver" -ResourceGroupName "Default-SQL-WestUS" -DatabaseName "database1"
Get-AzureSqlDatabaseTransparentDataEncryptionActivity -ServerName "myserver" -ResourceGroupName "Default-SQL-WestUS" -DatabaseName "database1"

  2. To disable TDE:

    Set-AzureSqlDatabaseTransparentDataEncryption -ServerName "myserver" -ResourceGroupName "Default-SQL-WestUS" -DatabaseName "database1" -State "Disabled"
Switch-AzureMode -Name AzureServiceManagement

Decrypting a TDE Protected Database on [!INCLUDEssSDS]

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 (preview) part to open the Transparent data encryption PREVIEW blade.

  6. In the Transparent data encryption PREVIEW 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 [!INCLUDEssSDS] using a query tool such as [!INCLUDEssManStudio] as a database user with the VIEW DATABASE STATE permission. Query the encryption_state column of the sys.dm_database_encryption_keysview.

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 [!INCLUDEssSDS], database users with the VIEW DATABASE STATE permission can query the encryption_state column of the sys.dm_database_encryption_keys view.

Working with TDE Protected Databases on [!INCLUDEssSDS]

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

Moving a TDE Protected Database on using .Bacpac Files

When exporting a TDE protected database using the Export Database function in the [!INCLUDEssSDSfull] Portal or the [!INCLUDEssNoVersion] Import and Export Wizard, the content of the database is not encrypted. The content is stored in .bacpac files which are not encrypted. Be sure to protect the .bacpac files appropriately and enable TDE once import of the new database is completed.

Related SQL Server Topic

Enable TDE Using EKM

See Also

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