SQL server encryption – Certificates

In the previous blogs we saw how to encrypt data using symmetric and asymmetric keys.

We saw how to use functions like ENCRYPTBYKEY, DECRYPTBYKEY & ENCRYPTBYASYMKEY,DECRYPTBYASYMKEY.

http://sqlandmorewithkruti.blogspot.com/2015/10/sql-server-encryption-symmetric-keys.html

http://sqlandmorewithkruti.blogspot.com/2015/10/sql-server-encryption-asymmetric-keys.html

Its important to note that while using these functions we need to ensure the password is included correctly. This involves a risk.In-case we lose the password, the keys render useless. Apart from this the password would have to be maintained in case of changes.

 In this blog we shall see something similar but with the use of SQL server certificates.

Certificates encapsulate data like password, user information, validity,etc. Their prime benefit is relieving the hosts of the need to maintain a set of passwords for individual subjects.

1) Create Database Master Key

2) Create Certificate

3) Create Key using Certificate

4) Encrypt Data

• Open Key
• Encrypt Data
• Close Key

5)  Using EncryptByCert(), DecryptByCert() Functions

1) Create Database Master Key

USE TESTDB;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '456##456hhhh';

GO

2) Create certificate

Now we proceed with creating a ceritificate which will serve as our container for the key.

Syntax : https://msdn.microsoft.com/en-us/library/ms187798.aspx

  USE TESTDB;

  GO

   CREATE CERTIFICATE SymKeyCertificate

   ENCRYPTION BY PASSWORD = '456##456hhhh'

   WITH SUBJECT = 'Symmetric Key Certificate',

   EXPIRY_DATE = '10/31/2020';

   GO

The reason we created a database master key here is if we dint mention the password the certificate would be encrypted by the DMK.

3) Create symmetric key using certificate

    CREATE SYMMETRIC KEY SymKeyCertTest

    WITH ALGORITHM = TRIPLE_DES

     ENCRYPTION BY CERTIFICATE SymKeyCertificate;

     GO

We shall be using a different table structure this time

  USE [TESTDB]

  GO

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  SET ANSI_PADDING ON

  GO

CREATE TABLE [dbo].[CertificateDemo1](

       [EmpId] [int] NOT NULL,

       [EmpName] [nchar](50) NULL,

       [EmpSal] [varchar](10) NOT NULL

) ON [PRIMARY]

Data is as follows

EmpId	EmpName	EmpSal
1	abc	5000
2	def	15000
3	hij	25000

4) Encrypt Data

We shall be encrypting and showing the data using the same functions for symmetric keys but this time we skip mentioning the password instead we use certificates.

USE TESTDB;

GO

-- Create symmetric key using the certificate       

OPEN SYMMETRIC KEY SymKeyCertTest

DECRYPTION BY CERTIFICATE SymKeyCertificate

-- Encrypt the data 

GO

SELECT empID,empname,

EncryptByKey(Key_GUID('SymKeyCertTest'), empsal) as "Encrypted Salary"

FROM CertificateDemo1

      

--Close symmetric key 

GO

CLOSE SYMMETRIC KEY SymKeyCertTest;

      

Im leaving the use of DecryptByKey()  function here. You can try that on your own.

5)  Using EncryptByCert(), DecryptByCert() Functions

If one wishes to use the certificate directly you can try the below options as well

EncryptByCert() function

ALTER TABLE CertificateDemo1

ADD certcolumn varbinary(MAX) NULL

GO

UPDATE certificatedemo1

SET certcolumn = (EncryptByCert(cert_id('SymKeyCertTes'),empsal))

DecryptByCert() function

SELECT empid,empname,convert(varchar(max), DecryptByCert(cert_id('SymKeyCertTesT'),empsal,'456##456hhhh') as "decrypted salary"

FROM certificatedemo1

Its that simple. In the next blog we see how to use the Transparent Data Encryption Technique