Introduction
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 (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 on the Always Encrypted, you can find it here – https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-2017
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 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?
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.
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/.
Comments