All six databases have been encrypted, but the status (in sys.dm_database_encryption_keys) is only showing correctly for three out of the six databases. To be able to see the correct status, I first need to select the database with the USE db_name statement.
This is the query and the output while being on master.
SELECT db.database_id, db.is_encrypted, encryption_state,
encryption_state_desc =
CASE encryption_state
WHEN '0' THEN 'No database encryption key present, no encryption'
WHEN '1' THEN 'Unencrypted'
WHEN '2' THEN 'Encryption in progress'
WHEN '3' THEN 'Encrypted'
WHEN '4' THEN 'Key change in progress'
WHEN '5' THEN 'Decryption in progress'
WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
ELSE 'No Status'
END,
percent_complete, encryptor_type
FROM sys.databases db LEFT OUTER JOIN sys.dm_database_encryption_keys dm ON db.database_id = dm.database_id
WHERE db.is_encrypted = 1;
Result:
| database_id | is_encrypted | encryption_state | encryption_state_desc | percent_complete | encryptor_type |
|---|---|---|---|---|---|
| 2 | 1 | 3 | Encrypted | 0 | ASYMMETRIC KEY |
| 5 | 1 | NULL | No Status | NULL | NULL |
| 6 | 1 | NULL | No Status | NULL | NULL |
| 7 | 1 | NULL | No Status | NULL | NULL |
| 8 | 1 | 3 | Encrypted | 0 | CERTIFICATE |
| 9 | 1 | 3 | Encrypted | 0 | CERTIFICATE |
| 10 | 1 | 3 | Encrypted | 0 | CERTIFICATE |
I presume I must have some kind of permission error or maybe a specific setting on the three databases only showing when USING them. I am logged on as a full administrator.
What could be the culprit?
