How to Check if TDE is Enabled on Your SQL Server

Introduction

Transparent Data Encryption (TDE) is a security feature in SQL Server that encrypts your data at rest. It ensures that your data is secure from unauthorised access, even if someone gains access to your storage or backups. In this blog post, we will demonstrate how to check if TDE is enabled on your SQL Server.

Check with SQL Server Management Studio

You can check if TDE Is enabled using SQL Server Management Studio. All you need to do is connect to the instance in question in Object Explorer. Right-click on the database in question and select options from the database properties menu

Check if TDE is enabled using a query.

As always, you can write a query to tell, you so it can be done programmatically if you need too.

1. Connect to your SQL Server:

First, you need to connect to your SQL Server using SQL Server Management Studio (SSMS) or Azure Data Studio.

2. Open a new query window:

After connecting to your SQL Server, open a new query window in SSMS or Azure Data Studio.

3. Execute either of the following queries to check the  TDE status check query:

To determine if TDE is enabled on your SQL Server, execute the following query:

SELECT d.name
,d.is_encrypted
,dek.encryption_state
,dek.percent_complete
,dek.key_algorithm
,dek.key_length
FROM sys.databases AS d
INNER JOIN sys.dm_database_encryption_keys AS dek ON d.database_id = dek.database_id

You can also run the above query and make the JOIN a left join, and you will get all of the databases listed.

This query will list any encrypted databases on this server. Despite the SQL Server documentation, the DMV only seems to return rows for encrypted databases. If the database is not encrypted, there are no rows returned from the DMV.

SELECT
DB_NAME(database_id) AS DatabaseName,
encryption_state,
CASE encryption_state
WHEN 0 THEN 'No Database Encryption Key Present'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encryption In Progress'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key Change In Progress'
WHEN 5 THEN 'Decryption In Progress'
ELSE 'Unknown'
END AS EncryptionStatus
FROM sys.dm_database_encryption_keys;

This query will return the encryption status of all databases on the SQL Server. The encryption_state column will show the status of TDE for each database. A status of ‘Encrypted’ (encryption_state = 3) indicates that TDE is enabled.

Review the results:

If TDE is enabled for a database, the query will return a row with the ‘Encrypted’ status for that database. If no rows are returned, TDE is not enabled on your SQL Server.

Summary

In this blog post, we demonstrated how to check if TDE is enabled on your SQL Server by executing a simple query. Ensuring that TDE is enabled on your databases is crucial in securing sensitive data from unauthorized access. If you find that TDE is not enabled, consider implementing it to enhance the security of your SQL Server environment.

Ready to Optimise Your SQL Server Performance?

If you found this guide helpful, imagine what a full SQL Server Health Check could do for your organisation! Our team of experts will analyze your SQL Server setup, identify potential issues, and provide actionable recommendations to enhance performance and security.

Don’t miss out on this opportunity to ensure your SQL Server runs at its best. Sign up for your FREE SQL Server Health Check today and take the first step towards optimizing your database environment.

You can signup for our free health check by completing this form

Sign Up Now

Useful Links

SSMS Error Generating Scripts

How to Write SQL Queries | Simple SELECT Statement