In a previous post, I displayed one method to encrypt data. This post is to show how to show the decrypted data. This post refers to the objects used/created in the previous post.There are multiple ways to show the decrypted data. The method I will show is view friendly for those who want to create views to display the decrypted data. It may defeat the purpose of data encryption if you are automatically decrypting the data but I will assume the proper security is set and that the automatic decryption makes good business sense. I will now show how to decrypt the data we encrypted in the previous post:
USE DATABASE; GO SELECT TOP(1) CONVERT(nvarchar(25) , DecryptByKeyAutoCert(CERT_ID('certificate_name') , NULL, encrypted_column, 1 , HashBytes('SHA1', CONVERT(varbinary, authenticator_column)))) AS 'name' FROM schema.tablename; GO
In the example above, you would replace database with the database name that has the table with the encrypted data. Replace certificate_name with the name of the certificate created to encrypt the symmetrical key. Replace the encrypted_column with the name of the column that has the encrypted data. Replace authenticator_column with the name of the column used as an authenticator when the data was encrypted. Replace name with an alias that you want to use as the name of the column. Replace schema.tablename with the schema and table name for the table that has the encrypted data. Once all the replacements are complete, execute the query to see the first row of the encrypted data. If you do not get the expected data returned, check the datatype used in the CONVERT function. In the example above, I used nvarchar(25). I believe the datatype of the original data would determine the data type to use here. For example, if my raw data had a varchar datatype and then I encrypted it using the steps in the previous post, using nvarchar datatype like above when decrypting it should result in garbage data. The fix would be to change the CONVERT datatype to varchar.
The key to making this decryption work automatically is the DecryptByKeyAutoCert function. This function combines the functionality of the OPEN SYMMETRIC KEY, CLOSE SYMMETRIC KEY, and DecryptByKey functions. As shown in the previous post, OPEN SYMMETRIC KEY allows usage of the key for various functions and CLOSE SYMMETRIC KEY closes this key. The DecryptByKey function uses an open symmetric key to decrypt encrypted data.