PageViews Last Month

Saturday 10 October 2015

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.


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.

  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


No comments:

Post a Comment