How to check if Always Encrypted is available and in-use

Introduction

Recently I had to work with a client that moved its infrastructure between servers (side-by-side upgrade). Always Encrypted was used on a previous server and I was tasked to make sure its all good on a new server.

This feature (available in SQL Server 2016 SP1 Standard Edition and up) is end-to-end encryption that allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine. If you need more information, find it here. 

Problem

Always Encrypted doesn’t have a flag on the database level, so how does one check if it is all good? Ironically, there is the is_encrypted flag, but it was designed for a Transparent Data Encryption (“TDE”) feature and has no value for this feature. So how can you check if it’s all working as expected?

Solution

The following code loops through all the databases (excluding system databases) and checks for the enabled encryption key and then counts the number of encrypted columns.

Source code

SET NOCOUNT ON;
 
DECLARE @DB_NAME NVARCHAR(50);
DECLARE @SQLString2 AS NVARCHAR(MAX)
 
DECLARE @AlwaysEncryptedDatabases TABLE (database_name VARCHAR(50), AlwaysEncryptedAvailable BIT, NumofEncryptedColumns INT)
DECLARE db_cursor CURSOR FOR
SELECT name AS database_name
FROM sys.databases
WHERE database_id>4  — exclude master, msdb, model, tempdb
OPEN db_cursor
 
FETCH NEXT FROM db_cursor
INTO  @DB_NAME
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
 SET  @SQLString2 = N’SELECT ”’ + @DB_NAME + ”’ AS DatabaseName’
+ ‘, CASE WHEN ISNULL(Name,””)!=”” THEN 1 ELSE 0 END AS AlwaysEncryptedAvailable’
+ ‘, (SELECT COUNT(*) FROM ‘ + @DB_NAME + ‘.sys.columns WHERE encryption_type IS not null) AS NumofEncryptedColumns’
+ ‘ FROM ‘ + @DB_NAME + ‘.sys.column_master_keys’PRINT ‘@SQLString2: ‘ + @SQLString2
INSERT INTO @AlwaysEncryptedDatabases EXECUTE SP_EXECUTESQL @SQLString2PRINT ‘ ‘
    FETCH NEXT FROM db_cursor INTO @DB_NAME
END
CLOSE db_cursor;
DEALLOCATE db_cursor;
SELECT *FROM @AlwaysEncryptedDatabases

BTW, if you need an awesome write-up on how to enable Always Encrypted – https://www.sqlshack.com/configure-always-encrypted-sql-server-2016-using-ssms-powershell-t-sql/.

You can also review our post about encrypting a database.