Database is a wonderful tool to store information and to look at the data in different ways to make decisions, present an opinion etc. However, the same data can pose a big problem should it fall in the hands of unscrupulous people and the impact can be more devastating if it has sensitive information such as Credit Card/Bank Account numbers, personal Telephone/Email details. For some organizations, it is mandatory to meet stringent data security regulations such as PCI-DSS, HIPAA/HITECH, or GLBA/FFIEC. Hence all such sensitive information must be encrypted in order to protect the customers and avoid data loss in the event of data breach.
This article focuses on how to do data encryption in Microsoft SQL Server by looking at the available options.
MS SQL Server – Data Encryption – Available Options
- Transparent Data Encryption (TDE)
Transparent Data Encryption (TDE) is available from version 2008 and above, which doesn’t require any programming knowledge. Entire database or an individual column can be encrypted using TDE which internally uses AES and 3DES algorithms. Backups of TDE encrypted databases are also encrypted.
However, TDE is available in Enterprise Edition only.
- Column/Cell Level Encryption
This is available to all the editions of MS SQL from version 2005 onwards. Unlike TDE, this requires schema modification and affects performance as well. AES with 128,196,256 bit keys and 3DES algorithms are used to perform data encryption with the help of a passphrase, asymmetric/symmetric key or a certificate.
- Custom Libraries
Can be implemented whether you use Microsoft Programming languages or others such as PHP/Java.
We’ll go with option-b, as TDE requires Enterprise edition and using custom libraries is dependent on the programming language of choice.
Cell Level Encryption
Any of the following methods can be used to encrypt the data:
Considered to be least secured as the same password is used for both encryption and decryption.
Provides strong protection with good performance. Certificate used must be signed by database master key (DMK).
- Symmetric key
Considered to be strong enough with good performance. Same key is used for both encryption and decryption.
- Asymmetric key
Different keys are used to encrypt/decrypt data, hence provides strong protection. However, it impacts performance. As per Microsoft’s guidelines, this should not be used to encrypt large values.
This blog explains data encryption using Symmetric key by using a test database, script for the same is provided below.
USE [master] GO CREATE DATABASE [Users]; GO USE [Users] GO CREATE TABLE [dbo].[tblUsers] ( [UserID] [INT] IDENTITY(1,1) NOT NULL, [FirstName] [NVARCHAR] (50), [LastName] [NVARCHAR] (50), [EMailId] [NVARCHAR] (50), [MobileNo] [NVARCHAR] (20) ) GO INSERT INTO [dbo].[tblUsers] ( [FirstName], [LastName], [EMailId], [MobileNo] ) VALUES ( 'John', 'Doe', 'firstname.lastname@example.org', '1234567890' ); GO
Data in the tblUsers is human readable since no encryption is in place, yet.
Cell Level Encryption – Symmetric Key
- Execute the below T-SQL statement to create database master key (DMK), encrypted with the passphrase: P@$$W0RD.
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101) CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@$$W0RD'; GO
Execute the below T-SQL statement to see the details of DMK created above.
SELECT * FROM sys.symmetric_keys;
Execute the below T-SQL statement to create a Certificate which will be used to encrypt the Symmetric key.
CREATE CERTIFICATE Cert_SymmetricEncryption --(Name of the Certificate) WITH SUBJECT = 'Certificate For Symmetric Encryption' --(Certificate's Subject); GO
Use this query to check the certificate created:
SELECT * FROM sys. Certificates;
You may see an error similar to the below if DMK is not created.
Msg 15581, Level 16, State 1, Line 1
Please create a master key in the database or open the master key in the session before performing this operation.
Create the DMK as mentioned above and then issue the CREATE CERTIFICATE statement.
It is possible to use external/third party certificates, which is not in the scope of this blog.
- Use the below T-SQL statement to create a symmetric key with the certificate created above.
CREATE SYMMETRIC KEY SymmetricKey_Users WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Cert_SymmetricEncryption; GO
Check the symmetric_keys table, which should show both DMK and the Symmetric Key created above.
SELECT * FROM sys.symmetric_keys;
We’re done with the preliminaries of Symmetric Key setup and can move on to data encryption. Slightly roundabout way has to be used to implement cell level encryption since we’re using existing database with existing table/data.
Execute the below script to add a new column: EncryptedEmail to the table tblUsers, which will store the encrypted value of the User’s Email Id.
USE [Users] ALTER TABLE tblUsers ADD EncryptedEmail VARBINARY(500);
Now, use the below script to encrypt the Email Id data and put it in the newly created column: EncryptedEmail.
USE [Users] OPEN SYMMETRIC KEY SymmetricKey_Users DECRYPTION BY CERTIFICATE Cert_SymmetricEncryption; UPDATE tblUsers SET EncryptedEmail = ENCRYPTBYKEY(KEY_GUID('SymmetricKey_Users'),EMailId); GO
Check the content of tblUsers, which should show the encrypted value of email.
USE [Users] SELECT * FROM tblUsers;
Since the encrypted column for Email is in place, we can drop the old column: EmailId.
USE [Users] ALTER TABLE tblUsers DROP COLUMN EMailId; Rename the Encrypted email column SP_RENAME 'tblUsers.EncryptedEmail', 'EMailId', 'COLUMN';
Now, we’ve a database that supports cell level encryption. We’ve also ensured all the existing data is not lost and properly encrypted.
Use the below script to fetch decrypted email id.
USE [Users] OPEN SYMMETRIC KEY SymmetricKey_Users DECRYPTION BY CERTIFICATE Cert_SymmetricEncryption; SELECT UserID, FirstName, LastName, MobileNo, CONVERT(NVARCHAR, DECRYPTBYKEY(EMailId)) AS EMailId FROM tblUsers;
Cell-Level Encryption Advantages
- Provides the means to encrypt single cell in a table.
- Data is not in plain text when it is loaded into memory, and is decrypted only when it is queried.
Cell-Level Encryption Dis-advantages
- Requires changes to the schema since encrypted data is stored in VARBINARY format. This is not an issue for new databases though.
- Encryption and Decryption affects the overall performance of the database.
- Table scans could be expensive, as indexes are also encrypted.
- Opening of Symmetric Key will not work in User Defined Functions (UDF); hence the below script must be called prior to invoking the UDF.
OPEN SYMMETRIC KEY SymmetricKey_Users DECRYPTION BY CERTIFICATE Cert_SymmetricEncryption;
Posted By: Sairam Dhanalakota, Osmosee