SQL Server allows several options to protect sensitive data, and one of them is specifying a column level encryption.
Using this option you can encrypt a column data also decrypt the data.ย SQL Server encrypts data with a hierarchical encryption and key management infrastructure, you can read about Encryption Hierarchy
To demonstrate the implementation of column level encryption, First we will create a table which will contain a column named AccountNumber having a account number of customer. Then we will encrypt the data of column AccountNumber using a SQL Server Symmetric keys.
Lets use the steps included in Encryption Hierarchy and implement the column level encryption.
First we create a sample table named CustomerAccountDetails, which contains the sensitive data of customer account number.
CREATE TABLE dbo.CustomerAccountDetails (
CustId INT PRIMARY KEY IDENTITY(1,1),
FirstName VARCHAR(100),
LastName VARCHAR(100),
AccountNumber VARCHAR(40)
)
GO
INSERT INTO dbo.CustomerAccountDetails
(FirstName, LastName, AccountNumber)
VALUES
('Atul', 'Kumar', 'HKT783459871'),
('Mahesh', 'Agarwal', 'BKY874534683'),
('Suraj', 'Negi', 'EBY778533682'),
('Lokesh', 'Mathur', 'CBY972534186'),
('Kumar', 'Ajay', 'NKH874534388'),
('Rahul', 'Rawat', 'ETY844524694'),
('Sanjay', 'Joshi', 'EBY274034688')
Implementing a SQL Server column level Encryption using a SYMMETRIC Keys
Lets start with the root of the SQL Server encryption hierarchy that isย Service Master Key, which is created during the instance creation. The Service master keys are protected by using the AES-256 algorithm.
It is created in master database and you can check, if the service master key has been created yet using following query.
USE Master; SELECT * FROM sys.symmetric_keys WHERE name = '##MS_ServiceMasterKey##';
Next we will setup a database master key.
Creating a DATABASE MASTER KEY
The database master key is a symmetric key which is used to protect the private keys of certificates and asymmetric keys that are present in the database. The database master keys are protected by using the AES-256 algorithm.
USE Prod_Db ; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password@123' GO
Theย encrypt by password argument which defines the password that is used to encrypt the master key in the database.
Lets quickly check whether a Database Master key is created or not using the following query.
USE Prod_Db ; GO SELECT * FROM sys.symmetric_keys
You can see, a Database master key is created. Nowย we will create a self signed SQL Server Certificate.
Creating a self signed SQL Server CERTIFICATE
A self signed SQL Server Certificate is a digitally signed security objectย contains a public or private (Optionally) key for SQL Server. It is protected by the database master key.
You can create a self certificate either with or without specifying an encryption by password.
When you create a certificate without encryption by password then in this case certificate is to be protected by the database master key.
When a certificate is created with argument ENCRYPTION BY PASSWORD, then in this case a certificate is protected by the password.
Lets create certificate without ENCRYPTION BY PASSWORD as we want certificate to be protected by Database master key.
USE Prod_Db; GO CREATE CERTIFICATE SelfSign_Certificate WITH SUBJECT = 'Data Security'; GO
Note that, subject should not be more than 64 characters long and this limit is enforced for SQL Server on Linux, and the subject can be up to 128 characters long for SQL Server on Windows.
Lets check whether the certificate is created or not using following query.
SELECT name, certificate_id, pvt_key_encryption_type_desc, Subject, expiry_date, start_date FROM SYS.certificates
As you can see above result, a certificate is created and you can see it is encrypted by the Database master key.
Also you can see that the certificate expiry date is also there. If an expiration date is not specified by the time certificated is created then a default expiration date of one year from the creation date will be applied.
If you want to provide the an expiry date when you create a certificate then you can modify above query as follows.
USE Prod_Db; GO CREATE CERTIFICATE SelfSign_Certificate WITH SUBJECT = 'Data Security', EXPIRY_DATE =ย 'specify expiry_date'ย GO
Now we will setup a symmetric key.
Creating a SYMMETRIC KEY
A symmetric key is used to encryption and decryption data in database. It is recommended to use symmetric key encryption for best performance when you encrypting a large amount of data.
Lets create a SYMMERTRIC KEY using following query.
USE Prod_Db ; GO CREATE SYMMETRIC KEY SymmetricKey WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE SelfSign_Certificate; GO
The WITH ALGORITHMย clause specifies the algorithm with which you want to use the key. You can choose betweenย AES_128,ย AES_192ย andย AES_256. Here we have chosen AES_128.
The ENCRYPTION BY CERTIFICATE, a symmetric key has to be protected by either a password, certificate , an asymmetric key or another symmetric key. Here we have used a certificate that we had just created.
Now we will add a new column EncryptedAccountNumber in table CustomerAccountDetailsย that will store a encrypted data of column AccountNumber.
Lets add a column named EncryptedAccountNumber in table using Alter Table command.
USE Prod_Db;
GO
ALTER TABLE dbo.CustomerAccountDetails
ADD EncryptedAccountNumber VARBINARY(MAX ) NULL
GO
Lets see the records in table.
SELECT * FROM dbo.CustomerAccountDetails
As you can see, there are seven records in table, now we will encrypt the column AccountNumber values and update the encrypted values into column EncryptedAccountNumber.
Encrypting a data using symmetric key
To encrypt the data, first we will open Symmetric key using OPEN SYMMETRIC KEYย command to use the symmetric key then uses the EncryptByKey command.
After using the symmetric we close the symmetric key using CLOSE SYMMETRIC KEYย command as following.
USE Prod_Db;
GO
OPEN SYMMETRIC KEY SymmetricKey
DECRYPTION BY CERTIFICATE SelfSign_Certificate;
GO
UPDATE dbo.CustomerAccountDetails
SET EncryptedAccountNumber = EncryptByKey (Key_GUID('SymmetricKey'),AccountNumber)
FROM dbo.CustomerAccountDetails ;
GO
CLOSE SYMMETRIC KEY SymmetricKey;
GO
As you can see, seven rows affected. Lets check the table.
SELECT * FROM dbo.CustomerAccountDetails;
As you can see the data of column AccountNumber is encrypted and updated in new column EncryptedAccountNumber.
Decrypting a data
To reading the encrypted data you need to decrypt it using a DecryptByKey command.
USE Prod_Db; GO OPEN SYMMETRIC KEY SymmetricKey DECRYPTION BY CERTIFICATE SelfSign_Certificate; GO SELECT CustId, FirstName, LastName, AccountNumber, EncryptedAccountNumber, CONVERT(VARCHAR, DecryptByKey(EncryptedAccountNumber)) AS 'DecryptedAccountNumber' FROM dbo.CustomerAccountDetails CLOSE SYMMETRIC KEY SymmetricKey; GO
As you can see, the encrypt data of column EncryptedAccountNumber is decrypted that can be seen in column DecryptedAccountNumber.
Inserting a records in table
Lets insert a record in table column EncryptedAccountNumber.
USE Prod_Db;
GO
OPEN SYMMETRIC KEY SymmetricKey
DECRYPTION BY CERTIFICATE SelfSign_Certificate;
GO
INSERT INTO dbo.CustomerAccountDetails
(FirstName, LastName, AccountNumber, EncryptedAccountNumber)
VALUES (
'Pradeep',
'Raturi',
'PRAD28403463',
EncryptByKey( Key_GUID('SymmetricKey'),
CONVERT(VARCHAR,'PRAD28403463') ) );
CLOSE SYMMETRIC KEY SymmetricKey;
GO
Lets check the record in table, you can see, record is inserted in table also a account number is encrypted.
SELECT * FROM dbo.CustomerAccountDetails;
Note that you can delete the column AccountNumber from table, as you have EncryptedAccountNumber column that you can read using DecryptByKey command.
Also Read
Data discovery and classification
Implementing a ROW_LEVEL security in SQL Server
6,283 total views, 3 views today