Encryption in SQL Server 2012–How to use EncryptByCert and DecryptByCert functions

In the previous article, we saw how to create X 509 Certificate and register it in SQL Server,

Now we will see how we can use the registered certificates

SQL Server 2012 provides the following two functions to encrypt and decrypt data directly using X 509 Certificates

1. EncryptByCert

2. DecryptByCert

Please refer to the below sample on how we can use these functions

DECLARE @CertID INT
DECLARE @encryptedtext varbinary(300);
DECLARE @plaintext nvarchar(100) = N'This is simple plain text for encryption'

-- Get the Certificate ID
SET @CertID = Cert_ID(N'Cert1_AdventureWorks2012')

-- Encrypt the data with the Certificate Registered
SET @encryptedtext = EncryptByCert(@CertID, @plaintext) 

-- Decrypt the data with the same Certificate
SELECT 
    @plaintext [PlainText], 
    @encryptedtext [EncryptedText],
    CAST(DECRYPTBYCERT(@CertID, @encryptedtext) AS nvarchar(100)) [DecryptedText]

The result of the above are as follows

Please make sure to use the correct Certificate ID to decrypt the data and also make sure to document which certificate is used for Encryption, if you are planning to use multiple certificates for different tables.

Hope this helps !!!

In the upcoming articles, we will see How we can use Certificates to

1. Encrypt Symmetric Keys

2. To Enable TDE based Encryption for Database

3. To digitally sign data using SignByCert() function

Author: Arunraj

I am a Microsoft Certified Technology Specialist (Database Developer). I work on SQL Server programming since SQL Server 7.0 specializes in SQL Server Programming and Performance Tuning and has 14 years of hands-on experience. I hold a Master Degree in Computer Applications. I am also part of NJSQL User Group and Northern New Jersey .Net User Group. View all posts by Arunraj