How to Encrypt and Decrypt in SQL Server with Certificate

How to Encrypt and Decrypt in SQL Server with Certificate:

While using the sensitive data in applications, we need to encrypt it whenever we are storing in the database tables.

For this purpose, we usually use the asymmetric keys with certificate. The process is descriped as below...

First, we need to create as master key with a password.
Later, we need to create a certificate with Subject Name and Expiry Date.
Later, we need to create a symmetric key with a algorithm by making use of the certificate.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd'

CREATE CERTIFICATE MyCertCert WITH SUBJECT = 'MyTestCert', EXPIRY_DATE = '10/31/2018'

CREATE SYMMETRIC KEY MySymKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE MyCertCert

Once the key is created with the certificate, you can see your certificate by making use of the below query.

SELECT * FROM sys.certificates

Later you can use the below SQL Statements to encrypt or decrypt the contents for the rows.
Encryption:
USE AdventureWorks2012;  
GO  
  
-- Create a column in which to store the encrypted data.  
ALTER TABLE HumanResources.Employee  
    ADD EncryptedNationalIDNumber varbinary(128);   
GO  
  
-- Open the symmetric key with which to encrypt the data.  
OPEN SYMMETRIC KEY MySymKey  
   DECRYPTION BY CERTIFICATE MyCertCert;  
  
-- Encrypt the value in column NationalIDNumber with symmetric key  
-- MySymKey. Save the result in column EncryptedNationalIDNumber.  
UPDATE HumanResources.Employee  
SET EncryptedNationalIDNumber  
    = EncryptByKey(Key_GUID('MySymKey'), NationalIDNumber);  
GO  
CLOSE SYMMETRIC KEY MySymKey;
Decryption:
OPEN SYMMETRIC KEY MySymKey  
   DECRYPTION BY CERTIFICATE MyCertCert;  
GO  
  
-- Now list the original ID, the encrypted ID, and the   
-- decrypted ciphertext. If the decryption worked, the original  
-- and the decrypted ID will match.  
SELECT NationalIDNumber, EncryptedNationalID   
    AS 'Encrypted ID Number',  
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalID))   
    AS 'Decrypted ID Number'  
    FROM HumanResources.Employee;  
GO  
CLOSE SYMMETRIC KEY MySymKey;

We can view the contents of the certificate by saving it to the disk.

we can do that by backing up the cert. we can also backup the Master Key.
BACKUP CERTIFICATE MyCertCert TO FILE = 'c:\share\mycert.cer'   
    WITH PRIVATE KEY ( --DECRYPTION BY PASSWORD = 'p@ssw0rd' ,  
    FILE = 'c:\share\privatekey_mycert' ,   
    ENCRYPTION BY PASSWORD = 'p@ssw0rd' );  
GO 

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'p@ssw0rd';  
BACKUP MASTER KEY TO FILE = 'c:\share\exportedmasterkey'   
    ENCRYPTION BY PASSWORD = 'p@ssw0rd';  
GO 
we can also restore the Master Key from a backed up file.

USE AdventureWorks2012;  
RESTORE MASTER KEY   
    FROM FILE = 'c:\share\exportedmasterkey'   
    DECRYPTION BY PASSWORD = 'p@ssw0rd'   
    ENCRYPTION BY PASSWORD = 'p@ssw0rd';  
GO