Database

How to check database encryption in SQL Server

By Muhammad Kamran | Published December 27, 2022

Many organizations around the world store and manage sensitive data in their databases. To protect sensitive data from falling into unauthorized hands, they encrypt the sensitive data in the database. Transparent Data Encryption (TDE) is a technique used for encryption and decryption in SQL Server. Before going ahead to check, if a database is encrypted in SQL Server or not. Let me define TDE encryption.

What is TDE Encryption

Transparent Data Encryption (TDE) is a process used in SQL Server to protect sensitive data, using encryption and decryption of the data. This technique encrypts while the data is at rest. TDE encryption and decryption work on the data file and log file. It encrypts and decrypts the data using the database Encryption Key (DEK) that is secured by a certificate. This certificate is stored in the master database or by another Asymmetric key.

Steps to check database encryption in SQL Server

We can check the database encryption in SQL Server using TSQL query, SSMS, and PowerShell script. I will share with you the TSQL query to check if TDE is enabled in SQL Server.

To check the database encryption key for the database, run the below query.

SELECT db_name(database_id) Name, encryption_state FROM sys.dm_database_encryption_keys;

In the above screenshot, the databases have an encryption state of 3, which means the database encryption key is configured for databases SQL_KDB and Tempdb.

Now run the second query mentioned below to check the encryption status of the databases.

SELECT

    db1.name,

    db1.is_encrypted,

    dm1.encryption_state,

    dm1.percent_complete,

    dm1.key_algorithm,

    dm1.key_length

FROM

    sys.databases db1

    LEFT OUTER JOIN sys.dm_database_encryption_keys dm1

        ON db1.database_id = dm1.database_id;

GO

If one or more SQL Server databases are encrypted, then you will see the names of those databases in the result as shown below in the output.

As we can see from the above screenshot, the database SQL_KDB is encrypted along with tempdb having the ‘is_encrypted’ column value of 1 and the ‘encryption_state’ column value of 3. Different encryption state and their description are mentioned below.  

Please note that when you encrypt a user database in SQL Server, tempdb will be encrypted automatically. Transparent Data Encryption (TDE) cannot be used to encrypt master, msdb, or model databases.

Encryption State in SQL Server

There are different encryption states in SQL Server. Each state defines a particular encryption status, for example, encryption state 3 in SQL Server Indicates that the database is encrypted. Please check the below table for all encryption states.

• 0 = No database encryption key available, no encryption
• 1 = Unencrypted
• 2 = Encryption in progress
• 3 = Database is Encrypted
• 4 = Key change in progress
• 5 = Decryption in progress
• 6 = Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)

Check the database encryption through SSMS

You can confirm the status of the encryption from the SQL Server studio management as well. Right-click on the database, click properties, and select Options, you will see the Encryption Enabled property True.

Conclusion

In this article, we have shared with you different ways (TSQL, SSMS) to check the database encryption in SQL Server. These steps are valid for almost all SQL Server and their upgrade versions. We tried to keep the article as simple as possible so that it is easy to understand for newbies as well.

Latest posts