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
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
One thought on “Encryption in SQL Server 2012–How to use EncryptByCert and DecryptByCert functions”