Updated: Mar 9
Troubleshooting encryption errors can be difficult due to an inherent complexity of encryption architectures and the multiple actors involved. Performing a root cause analysis might require you examine your SQL Server instances, client computers, Active Directory Domain Services, ADDS, Windows operating system, or even hardware.
The root cause for encryption errors can include wrong or changed passwords, missing or expired certificates, SQL Server configuration being changed, encryption algorithm issues, time not being synchronized, key length, password complexity, EKM issues and a plethora of other problems.
When troubleshooting encryption errors, examine the following potential sources for error messages:
Error Log SQL Server's error log should generally be your first port of call.
Windows event logs The Windows application, security or system event logs will also generally have useful information that you can leverage in your troubleshooting efforts.
sys.assymetric_keys This DMV returns information about asymmetric keys. Pay attention to the encryption algorithm being used and how the asymmetric keys were encrypted (master key, password, or service master key).
sys.certificates This system catalogue view will have more information about the certificates within the database, including their issuer, and the expire date.
sys.column_encryption_keys Returns information about column encryption keys (CEKs) created with the CREATE COLUMN ENCRYPTION KEY statement.
sys.column_encryption_key_values This DMV returns information about encrypted values of column encryption keys (CEKs) created with either the CREATE COLUMN ENCRYPTION KEY or the ALTER COLUMN ENCRYPTION KEY (Transact-SQL) statement.
sys.column_master_keys Returns a row for each database master key added by using the CREATE MASTER KEY statement. Each row represents a single column master key (CMK)
sys.crypt_properties This is a system catalogue view that returns each cryptographic property associated with a securable.
sys.cryptographic_providers Another system catalogue view that returns information about each registered cryptographic provider.
sys.dm_database_encryption_keys Another important DMV for troubleshooting encryption problems.
sys.key_encryptions This system catalogue view contains a row for each symmetric key encryption specified by using the ENCRYPTION BY clause of the CREATE SYMMETRIC KEY statement.
sys.dm_exec_connections This view will have information about the current connection being made to the database engine. It contains the [encrypt_option] column, describing whether encryption is enabled for a particular connection.
sys.openkeys This system catalogue view returns information about encryption keys that are open in the current session.
sys.security_policies Returns a row for each security policy in the database.
sys_symmectric_keys This DMV returns information about symmetric keys. Again, pay attention to the encryption algorithm being used.
Need more review files, scripts or examples? OK Let me know here - Email Me and we have a complete zone of documentation, scripts and solutions.
Things that you can find in the Paddock Zone.
Implementing column-level encryption using a certificate.
Implement Always Encrypted.
Implement Transparent Database Encryption.