I have enabled TDE (Transparent Data Encryption) on almost all of my production SQL Server database servers. Yesterday I was approached by the IA (Information Assurance) team and they wanted to know what was the encryption level (key length and algorithm) of one of the database servers.
I already knew it was AES-256 but IA needed a screenshot as proof. So I wrote this little query to bring back the database name, id, encryption key length and algorithm. I ran it, took a screenshot and sent it to them.
--Bring back database name, id --key algorithm and key length select db.name, e.database_id, e.key_algorithm, e.key_length from sys.dm_database_encryption_keys e join sys.databases db on db.database_id = e.database_id