In the previous blogs we saw how to encrypt data using symmetric and asymmetric keys. 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
3) Create Key using Certificate
4) Encrypt Data
- Open Key
- Encrypt Data
- Close Key
5) Using EncryptByCert(), DecryptByCert() Functions 1) Create Database Master Key CREATE MASTER KEY ENCRYPTION BY PASSWORD = '456##456hhhh';
Now we proceed with creating a ceritificate which will serve as our container for the key.
CREATE CERTIFICATESymKeyCertificate ENCRYPTION BY PASSWORD = '456##456hhhh' WITH SUBJECT = 'Symmetric Key Certificate', EXPIRY_DATE = '10/31/2020'; 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; We shall be using a different table structure this time
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[CertificateDemo1]( [EmpName] [nchar](50) NULL, [EmpSal] [varchar](10) NOT NULL
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. -- Create symmetric key using the certificate OPEN SYMMETRIC KEY SymKeyCertTest DECRYPTION BY CERTIFICATE SymKeyCertificate
-- Encrypt the data
EncryptByKey(Key_GUID('SymKeyCertTest'), empsal)as "Encrypted Salary"
--Close symmetric key
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 ALTER TABLE CertificateDemo1 ADD certcolumn varbinary(MAX) NULL SET certcolumn = (EncryptByCert(cert_id('SymKeyCertTes'),empsal)) SELECT empid,empname,convert(varchar(max), DecryptByCert(cert_id('SymKeyCertTesT'),empsal,'456##456hhhh') as "decrypted salary"
Its that simple. In the next blog we see how to use the Transparent Data Encryption Technique