We do have “Always encrypted” feature in SQL 2016 and later versions, from this feature we can encrypt the column data instead of encrypting whole database. Sensitive data like credit card numbers, SSN.
We need to prepare a list of encrypted columns at some where to track for future.
Without tracking anywhere in the documents we can run the simple SQL query which returns all the columns in respective tables and encryption type.
t.name AS TableName,
c.name AS ColumnName,
k.name AS KeyName,
FROM sys.columns c
INNER JOIN sys.column_encryption_keys k ON c.column_encryption_key_id = k.column_encryption_key_id
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE encryption_type IS NOT NULL
Always Encrypted is a feature designed by the Microsoft in SQL Server 2016 to protect sensitive data, such as credit card numbers or national identification numbers (SSN). It allows clients to encrypt sensitive data inside client applications.
When you work with Always Encrypted in development environment, it will be works fine because it requires certificate keys, which will be already installed on Dev machine. But when you moved to production environment it will not work. The Certificate needs to install on that server to run the web application. Without it encryption will not work. You will get database timed out error or encryption error while run the application.
Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
To resolve this error, you need to install the certificate on the server for specific user. After that you have to defined, selected user in application pool advanced properties. Which user you need to enabled the “Load user profile” property too under “Process Model”.