How To Check Database Encryption In SQL Server

by

Recently, I got a requirement to enable database encryption for a few of my SQL server databases. So before implementing, I am supposed to check if the database encryption is already enabled for my databases. I have identified a few simple approaches to check database encryption status in SQL server. In this article, I will guide you through all those approaches.

How To Check Database Encryption In SQL Server

Let us discuss all those approaches individually.

Approach-1: Using SQL Server Management Studio (SSMS)

To check database encryption in SQL server, follow the steps below.

1. Open SQL Server Management Studio (SSMS) and connect to your SQL server instance.

2. Expand the Databases folder.

3. Right-click on the database name and select the Properties option, as shown in the screenshot below.

how to enable database encryption in sql server

4. Click on the Options tab. You can see the Encryption Enabled property is False, which means it is not enabled for the test database.

How To Check Database Encryption In SQL Server

Approach-2: Using SQL Query

We can execute the below query to check the database encryption for all the databases in SQL server.

SELECT
    sd.name,
    sd.is_encrypted
FROM
    sys.databases sd
    LEFT OUTER JOIN sys.dm_database_encryption_keys dd
        ON sd.database_id = dd.database_id;	
GO

After executing the above query, I got the expected output, as shown in the screenshot below. When the is_encrypted column value is 0 then the encryption is not enabled and if it is 1 then it is enabled.

how to check database encryption status in sql server

Video Tutorial

Conclusion

It is so easy to check database encryption status in an SQL server; you can use the SQL server management studio design approach or SQL query for this purpose, as mentioned in this article.

You may also like following the articles below.