Here is another way of encrypting and decrypting the data using SYMMETRIC keys. This is more secure when compared to the encryptbypassphrase and decryptbytpassphrase methods. Here we can even select different encyption schmes like TRIPLE_DES, AES_128, AES_264 etc.
There is a catch here though. We are not allowed to use OPEN SYMMETRIC key with pass phrase as a parameter in stored procedures. Why cant we use it? Because OPEN SYMMETRIC statement is considered as a DDL(Data Definition Language) not DML(Data Manipulation Language). A way around this is to execute it dynamically as shown:
A sample showing the usage of symmetric keys to encrypt data.
–Test Table to store Data
Create Table SecretData(Data varbinary(MAX))
–Create a Symmetric Key
CREATE SYMMETRIC KEY SecretData_Key
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY PASSWORD=’SecretPa$$’;
–Procedure to Encrypt Data
Create Procedure EncryptData_SymKey (@Password varchar(10),@Data varchar(20))
as
–Dynamic query to open symmetric key
Exec (’OPEN SYMMETRIC KEY SecretData_Key
DECRYPTION BY PASSWORD=”’+ @Password +””)
Insert SecretData
Select EncryptByKey(Key_GUID(’SecretData_Key’), @Data )
–Procedure to decrypt data
Create Procedure DecryptData_SymKey (@Password varchar(10))
as
Exec (’OPEN SYMMETRIC KEY SecretData_Key
DECRYPTION BY PASSWORD=”’+ @Password +””)
Select convert(varchar(20),DecryptByKey(Data))
From SecretData
–Testing
–Encrypt
EncryptData_SymKey ‘SecretPa$$’,'MyData’
–Check Data
Select Data From SecretData
–Decrypt
DecryptData_SymKey ‘SecretPa$$’
