Encryption in MS SQL

Joe Chan 24 Feb, 2019

MSSQL-Feature-Image

We create the following table for testing encryption.
Value column keep plaintext value
EN_C column keep value which is encrypted by Certificate
EN_SY column keep value which is encrypted by Symmetric Key
EN_AY column keep value which is encrypted by Asymmetric Key

CREATE TABLE [dbo].[TESTING](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Value] [varchar](50) NULL,
[EN_C] [nvarchar](256) NULL,
[EN_SY] [nvarchar](256) NULL,
[EN_AY] [nvarchar](256) NULL
) ON [PRIMARY]

Encrypt value
Using “ENCRYPTBYCERT(CERT_ID(‘)” to encrypt data by certificate.
Using “ENCRYPTBYKEY(Key_GUID(‘)” to encrypt data by Symmetric key.
Using “ENCRYPTBYASYMKEY(ASYMKEY_ID(‘)” to encrypt data by Asymmetric key.

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'P@ssw0rd'
OPEN SYMMETRIC KEY TESTINGSK01 DECRYPTION BY CERTIFICATE TESTINGC01
UPDATE TESTING SET EN_C = ENCRYPTBYCERT(CERT_ID('TESTINGC02'),Value),
ENCRYPTBYKEY(Key_GUID('TESTINGSK01'),Value),
ENCRYPTBYASYMKEY(ASYMKEY_ID('TESTINGAK01'),Value)

Decrypt value
The decrypted value need to convert as varchar.

SELECT CONVERT(varchar,DECRYPTBYCERT(CERT_ID('TESTINGC02'),EN_C)) FROM TESTING
SELECT CONVERT(varchar,DECRYPTBYKEY(EN_SY)) FROM TESTING
SELECT CONVERT(varchar,DECRYPTBYASYMKEY(ASYMKEY_ID('TESTINGAK01'),EN_AY)) FROM TESTING

Post related to Server Master Key
Post related to Database Master Key
Post related to Certificates
Post related to Asymmetric Keys
Post related to Symmetric Keys

Author: Joe Chan