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 CERTIFICATESymKeyCertificate
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
