Recently I had to work with a client that moved its infrastructure between the 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.
Always Encrypted doesn’t have a flag on the database level, so how does one check if it is all good? Ironically, there is is_encrypted flag, but it was designed for a Transparent Data Encryption (“TDE”) feature and has no value for Always Encrypted. So how can you check if it’s all working as expected?
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.
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
WHERE database_id>4 — exclude master, msdb, model, tempdb
FETCH NEXT FROM db_cursor
WHILE @@FETCH_STATUS = 0
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