Encrypt - Decrypt
Encrypt - Decrypt

Master Key

If your database does not already have a database master key, create one by executing the following statement providing your password

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123';
Encrypt - Decrypt

Certificate

CREATE CERTIFICATE CetificateName WITH SUBJECT = 'Subject Certificate';
Encrypt - Decrypt

Symmetric Key

CREATE SYMMETRIC KEY SymmetricKeyName 
WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE CertificateName;
Encrypt - Decrypt

Encrypt

OPEN SYMMETRIC KEY SymmetricKeyName 
	DECRYPTION BY CERTIFICATE CertificateName;
DECLARE @salary INT = 1000000, @encrypt  VARBINARY(128);
SET @encrypt = EncryptByKey(Key_GUID('SymmetricKeyName'), 
	CONVERT(VARCHAR, @salary));
CLOSE SYMMETRIC KEY SymmetricKeyName;
Encrypt - Decrypt

Encrypt With Key

OPEN SYMMETRICT KEY SymmetrictKeyName 
	DECRYPTION BY CERTIFICATE CertificateName;
DECLARE @salary INT = 1000000, @key VARCHAR(4) = '123', 
	@encrypt VARBINARY(128);
SET @encrypt = EncryptByKey(Key_GUID('SymmetrictKeyName'), 
	CONVERT(VARCHAR, @salary), 1, HASHBYTES('MD5', @key));
CLOSE SYMMETRICT KEY SymmetrictKeyName;
Encrypt - Decrypt

Decrypt

OPEN SYMMETRICT KEY SymmetrictKeyName 
	DECRYPTION BY CERTIFICATE CertificateName;
DecryptByKey(@encrypt);
CLOSE SYMMETRICT KEY SymmetrictKeyName;
Encrypt - Decrypt

Decrypt With Key

OPEN SYMMETRICT KEY SymmetrictKeyName 
	DECRYPTION BY CERTIFICATE CertificateName;
DECLARE @key VARCHAR(4) = '123';
DecryptByKey(@encrypt, 1, HASHBYTES('MD5', @key));
CLOSE SYMMETRICT KEY SymmetrictKeyName;