SQL Server 2005 and SQL Server 2008 provide encryption as a new feature to protect data against hackers’ attacks. Hackers might be able to penetrate the database or tables, but owing to encryption they would not be able to understand the data or make use of it. Nowadays, it has become imperative to encrypt crucial security-related data while storing in the database as well as during transmission across a network between the client and the server.
Encryption hierarchy is marked by three-level security. These three levels provide different mechanisms for securing data across networks and local servers. Different levels of hierarchies allow multiple instances of services (e.g., SQL Server Services) to run on one physical server.
- Windows Level – Highest Level – Uses Windows DP API for encryption
- SQL Server Level – Moderate Level – Uses Services Master Key for encryption
- Database Level – Lower Level – Uses Database Master Key for encryption
There are two kinds of keys used in encryption:
- Symmetric Key – In Symmetric cryptography system, the sender and the receiver of a message share a single, common key that is used to encrypt and decrypt the message. This is relatively easy to implement, and both the sender and the receiver can encrypt or decrypt the messages.
- Asymmetric Key – Asymmetric cryptography, also known as Public-key cryptography, is a system in which the sender and the receiver of a message have a pair of cryptographic keys – a public key and a private key – to encrypt and decrypt the message. This is a relatively complex system where the sender can use his key to encrypt the message but he cannot decrypt it. The receiver, on the other hand, can use his key to decrypt the message but he cannot encrypt it. This intricacy has turned it into a resource-intensive process.
Yet another way to encrypt data is through certificates. A public key certificate is a digitally signed statement that binds the value of a public key to the identity of the person, device, or service that holds the corresponding private key. A Certification Authority (CA) issues and signs certifications.
Please create a sample database that we will be use for testing Encryption. There are two different kinds of encryptions available in SQL Server:
- Database Level – This level secures all the data in a database. However, every time data is written or read from database, the whole database needs to be decrypted. This is a very resource-intensive process and not a practical solution.
- Column (or Row) Level – This level of encryption is the most preferred method. Here, only columns containing important data should be encrypted; this will result in lower CPU load compared with the whole database level encryption. If a column is used as a primary key or used in comparison clauses (WHERE clauses, JOIN conditions) the database will have to decrypt the whole column to perform operations involving those columns.
Let’s go over a simple instance that demonstrates the encryption and the decryption process executed with Symmetric Key and Triple DES encryption algorithm.
/* Create Database */ USE master GO CREATE DATABASE EncryptTest ON PRIMARY ( NAME = N'EncryptTest', FILENAME = N'C:\EncryptTest.mdf') LOG ON ( NAME = N'EncryptTest_log', FILENAME = N'C:\EncryptTest_log.ldf') GO
First, let’s create a sample table and then populate it with sample data. We will now encrypt one of the two columns of the table.
/* Create table and insert data in the table */ USE EncryptTest GO CREATE TABLE TestTable (FirstCol INT, SecondCol VARCHAR(50)) GO INSERT INTO TestTable (FirstCol, SecondCol) SELECT 1,'First' UNION ALL SELECT 2,'Second' UNION ALL SELECT 3,'Third' UNION ALL SELECT 4,'Fourth' UNION ALL SELECT 5,'Fifth' GO /* Check the content of the TestTable */ USE EncryptTest GO SELECT * FROM TestTable GO
The preceding code will return the result depicted in the subsequent figure.
Every database can have one master key. Database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys present in the database. It uses Triple DES algorithm together with user-provided password to encrypt the keys.
/* Create Database Master Key */ USE EncryptTest GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQLAuthority' GO
Certificates are used to safeguard encryption keys, which are used to encrypt data in the database. SQL Server 2005 has the capability to generate self-signed X.509 certificates.
/* Create Encryption Certificate */ USE EncryptTest GO CREATE CERTIFICATE EncryptTestCert WITH SUBJECT = 'SQLAuthority' GO
The symmetric key can be encrypted by using various options such as certificate, password, symmetric key, and asymmetric key. A number of different algorithms can be employed for encrypting key. The supported algorithms are DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, and AES_256.
/* Create Symmetric Key */ USE EncryptTest GO CREATE SYMMETRIC KEY TestTableKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE EncryptTestCert GO
Now add a column of type varbinary to the original table, which will store the encrypted value for the SecondCol.
/* Encrypt Data using Key and Certificate Add Columns which will hold the encrypted data in binary */ USE EncryptTest GO ALTER TABLE TestTable ADD EncryptSecondCol VARBINARY(256) GO
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 EncryptSecondCol column.
/* Update binary column with encrypted data created by certificate and key */ USE EncryptTest GO OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptTestCert UPDATE TestTable SET EncryptSecondCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SecondCol) GO
We can drop the original SecondCol column, which we have now encrypted in the EncryptSecondCol column. If you do not want to drop the column, you can keep it for future comparison of the data when we decrypt the column.
/* DROP original column which was encrypted for protect the data */ USE EncryptTest GO ALTER TABLE TestTable DROP COLUMN SecondCol GO
We can run a SELECT query on our database and verify if our data in the table is well protected and hackers will not be able to make use of it even if they somehow manage to reach the data.
/* Check the content of the TestTable */ USE EncryptTest GO SELECT * FROM TestTable GO
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 SecondCol and EncryptSecondCol.
/* Decrypt the data of the SecondCol */ USE EncryptTest GO OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptTestCert SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptSecondCol)) AS DecryptSecondCol FROM TestTable GO
If you drop the database after the entire processing is complete, you do not have to worry about cleaning up the database. However, in real world on production servers, the database is not dropped. It is a good practice for developers to close the key after using it. If keys and certificates are used only once or their use is over, they can be dropped as well. Dropping a database will drop everything it contains – table, keys, certificates, all the data, to name a few.
/* Clean up database */ USE EncryptTest GO CLOSE SYMMETRIC KEY TestTableKey GO DROP SYMMETRIC KEY TestTableKey GO DROP CERTIFICATE EncryptTestCert GO DROP MASTER KEY GO USE [master] GO DROP DATABASE [EncryptTest] GO
Encryption is a very important security feature of SQL Server 2005. Long keys and asymmetric keys create unassailable, stronger encryption and stronger encryption uses lots of CPU to encrypt data. Stronger encryption is slower to process. When there is a huge amount of data to encrypt, it is suggested to encrypt it using a symmetric key. The same symmetric key can be encrypted further with an asymmetric key for additional protection, thereby adding the advantage of a stronger encryption. It is also recommended to compress data before encryption, as encrypted data cannot be compressed.
Reference : Pinal Dave (https://blog.sqlauthority.com)
I have go through whole process and executed all thing step by step successfully.
but after this process i want backup (.bak file with encrypted coloumn) of encrypt_test databse and restore to another database server.
please help me out..
You can refer books online. It has steps to move encrypted database from one server to another.
Hi Sir, I transferred column level encrypted data to CSV files, then from CSV files I restored to another DB in same instance for the same I took back up of the certificated (with private key) and created the same certificate using the certificate backup.
But while decryption it gives me null results. Please help me where I am doing mistake.
you have to open key before using it.
I allow access to the key to test it and I can see the data using the Grant View Definition command. I then deny myself access to the Key and Certification. However I can still see the data if I open the Key and Decrypt the column
Is this a Microsoft Defect ??
I have no idea. You should try MSDN forums to report it to Microsoft and check.
I am getting only special characters on encryption. Please help
Hi, Thanks for posting this article.
I want to know if there is any way we can get the same varbinary data if we encrypt the same value two times.
I run below query and each time value was different
OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptTestCert
Op : 0x006A54B47CD3AE4494379E2A800A3F570100000020968A4E1A79A34A7D282AA6766BEC7B143CA49423915CD2
Op : 0x006A54B47CD3AE4494379E2A800A3F5701000000A47683576DB357A5B88F97CA86CBC9B758995CA37CA4C4B0
The reason I need this is because I have to use this column in where clause. Another approach is that first I decrypt all values in that column and then match this value in where clause but this is making is very slow.
Please let me know if this is even possible.
create master key encryption by password = ‘MAKV2SPBNI99212!’;
open master key decryption by password = ‘MAKV2SPBNI99212!’;
create certificate Mypasswordcertificate encryption by password = ‘MAKV2SPBNI99212!’
with subject = ‘A certificate password’
select * from sys.certificates
create symmetric key sys_password
with identity_value = ‘a fairly secure name’,
algorithm = aes_256 ,
key_source =’a very secure strong password’
encryption by certificate Mypasswordcertificate;
select * from sys.symmetric_keys where symmetric_key_id=256
open symmetric key sys_password decryption
by certificate Mypasswordcertificate
select fdUsername,fdPassword as ‘Encypted Password’, CONVERT(varchar(50), DECRYPTBYKEY(fdPassword)) as ‘Decrypted password’ from tblBankCustomers where fdCostumerId = ’52’
close symmetric key sys_password;
but Decrypted Password show null value
solve this please
How can I use lookup functions for eg Like on encrypted data in SQL 2016. Because I am facing operand clash error whenever I try to execute the SQL script lookup. Also none of the SQL functions are not working on encrypted data eg datediff, isnull, convert etc. I just want to confirm are these limitations of SQL 2016 Always encrypted data or is there any way to perform these operations?
Please suggest your assistance is required.
Good article. Very useful to me.
is it possible to do cell level encryption using certificate stored in HSM ?
Never tried that but it should be possible.
I need to write Stored Proc for Encrypt and Decrypt values. Can you please suggest algorithm for the same.
Thanks in advance.
How to query an encrypted column from java?
is it possible to backup key and certificate to a user defined table..Please let me know
I created a symmetric key with password with the AES 256 algorithm and the cost column was changed to varchar and encrypted the data, while showing in an application that the data was decrypted with the same key and the same password.
Now the symmetric key has been removed from the sys.symmetric key table and now I know the key name and password, if you create the same key name with the same password, if it will work in this case or not.?
Hi Sir ,
Can we use other DB’s Symmetric Key and certificate to encrypt new DB’s table column in same instance !
Is it good practice ?
Hi Pinal sir,
I Need to know is there a common AES-256 algorithm which support Encryption and decryption from Both C# and SQL server ?
I needed to Encrypt the data in DB using symmetric Key and Same the encrypted data should able to decrypt from Application using C# code with using same Symmetric key and vice versa.
Can you please help me with this ?