How to manage encryption in Synapse Analytics Dedicated SQL Pool?

One of the ways to secure data stored in Synapse Analytics Dedicated SQL Pool, is to enable Transparent Data Encryption (TDE). Enabling TDE ensures that all data stored in the dedicated SQL pool remains encrypted while it is at rest (i.e., not being accessed or in transit.) In this post, we will see how we can enable, disable, and verify the (TDE) encryption status of a dedicated SQL Pool in Synapse Analytics.

Step 1: Make sure that the SQL Pool that we want to enable encryption for, is online (i.e., not in a paused state).

We can check this by clicking on SQL pools under Analytics pools in the Synapse Workspace UI on the Azure Portal:

If the Pool status is Paused, we can enable it by clicking on the three dots symbol for the SQL pool and clicking on Resume in the context menu options:

Please note: It may take a while (up to 4 mins) for the SQL Pool to go from a paused state to online state.

Step 2: Enabling (or disabling) encryption in Synapse dedicated SQL Pool.

To enable encryption, run the command below on the master database of the dedicated SQL Pool :

ALTER DATABASE [ADEdb] SET ENCRYPTION ON;

To disable encryption, run the command below on the master database:

ALTER DATABASE [ADEdb] SET ENCRYPTION OFF;

[ADEdb] is the name of the SQL Pool database on which we would like to enable or disable encryption.

Please note: To be able to run the above commands, you need to be logged on with a login that has administrator privileges OR a login that is a member of the dbmanager role in the master database.

Step 3 (optional): To verify the encryption status, run the command below on the master database

SELECT
    [name],
    [is_encrypted]
FROM
    sys.databases;

The above command will show the database name in the current SQL pool with the encryption status (enabled/disabled).

Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-encryption-tde-tsql