Steps for encryption and decryption cell values in SQL Server.
Step 1: Create table and populate data.
Create a Master key(DMK-Database Master Key)
CREATE TABLE TestTable (Id INT, TextValue VARCHAR(50)) GO INSERT INTO TestTable (Id, TextValue) SELECT 1,'First' UNION ALL SELECT 2,'Second' UNION ALL SELECT 3,'Third' UNION ALL SELECT 4,'Fourth' UNION ALL SELECT 5,'Fifth' GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Teciners#123' GO
The master key (DMK) in a database is encrypted by SQL server using its service master key (SMK).
SMK is created by a SQL Server instance when it starts up the very first time after installation. For any set of instances of SQL server, the SMK will always be different. So, the DMK in the database will be encrypted differently on each server.
Step 2: Create Certificate
CREATE CERTIFICATE EncryptTechinersCert WITH SUBJECT = 'Techiners#123' GO
Step 3: Create Symmetric Key and Secure the Symmetric Key with the certificate
CREATE SYMMETRIC KEY TechinersKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE EncryptTechinersCert
Step 4: Open the Symmetric Key and Select DB/COLUMN/CELL on which we want to implement encryption.
Before the key is used, it needs to be decrypted using the same method that was used for encrypting it. In our example, we have used a certificate for encrypting the key. Because of the same reason, we are using the same certificate for opening the key and making it available for use. Subsequent to opening it and making it available for use, we can use the encryptkey function and store the encrypted values in the database, in the EncryptTextValue column.
--ALTER TABLE TestTable --ADD EncryptTextValue VARBINARY(256) OPEN SYMMETRIC KEY TechinersKey DECRYPTION BY CERTIFICATE EncryptTechinersCert UPDATE TestTable SET EncryptTextValue = ENCRYPTBYKEY(KEY_GUID('TechinersKey'),TextValue) GO
The above query will encrypt entire column. But, in some scenarios we require only particular cell values to be encrypted, then we can use below mentioned query.
--OPEN SYMMETRIC KEY TechinersKey DECRYPTION --BY CERTIFICATE EncryptTechinersCert --UPDATE TestTable --SET EncryptTextValue = ENCRYPTBYKEY(KEY_GUID('TechinersKey'),TextValue) --where Id in (2,5) --GO
Step 5: Check the data whether it is safe or not i.e., encrypted or not.
SELECT * FROM TestTable GOStep 6: Decrypt the data
Authorized user can use the decryptbykey function to retrieve the original data from the encrypted column.
If Symmetric key is not open for decryption, it has to be decrypted using the same certificate that was used to encrypt it.
An important point to bear in mind here is that the original column and the decrypted column should have the same data types.
If their data types differ, incorrect values could be reproduced.
In our case, we have used a VARCHAR data type for TextValue and EncryptTextValue.
OPEN SYMMETRIC KEY TechinersKey DECRYPTION
BY CERTIFICATE EncryptTechinersCert SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptTextValue)) AS DecryptTextValue FROM TestTable GOStep 7: Close Symmetric Key. (Can aslo drop all keys)
CLOSE SYMMETRIC KEY TechinersKey GO
You can drop keys that you have created above. Dropping entire database will drop all the associated keys.
DROP SYMMETRIC KEY TechinersKey GO DROP CERTIFICATE EncryptTechinersCert GO DROP MASTER KEY GO