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

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

image

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

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 111 other followers

%d bloggers like this: