Troubleshooting encryption errors can be difficult due a 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 included 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 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 assymmectric keys. Pay attention to the encryption algorithm being used and how the assymmetric keys was encrypted (master key, password, or service master key).
sys.certificates This system catalog 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 statment.
sys.column_encryption_key_values This DMV reuturns 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 catalog view that returns each cryptographic property associated with a securable.
sys.cryptographic_providers Another system catalog 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 catalog view contains a row for each sysmmetric 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 current connection being made to the database engine. It contains the [encrypt_option] column, describing whther encryption is enabled for a particular connection.
sys.openkeys This sytem catalog view a returns information about encryption keys that are open in the current session.
sys.security_policies Returns a row for each security policiy 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.