SQL Server | Column-Level Encryption using Certificate

We use the CREATE MASTER KEY statement to create a database master key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘PASSWORD’

Create a self-signed certificate for column-level SQL Server encryption

CREATE CERTIFICATE Certificate_test WITH SUBJECT = ‘Protect my data’;

Configure a symmetric key for column-level SQL Server encryption

CREATE SYMMETRIC KEY SymKey_test WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Certificate_test

Open the symmetric key and decrypt using the certificate.

We need to use the same symmetric key and certificate name that we created earlier

OPEN SYMMETRIC KEY SymKey_test DECRYPTION BY CERTIFICATE Certificate_test;

Data encryption

UPDATE TABLENAME SET ColumnToBeEncrypted = ENCRYPTBYKEY(KEY_GUID(‘SymKey_test’), ColumnWithPlainValue) from TABLENAME;

Close the symmetric key using the CLOSE SYMMETRIC KEY statement. If we do not close the key, it remains open until the session is terminated

CLOSE SYMMETRIC KEY SymKey_test;

Example

pwd (ColumnWithPlainValue)

password_encrypted(ColumnToBeEncrypted)

Video Tutorial