SQL SERVER – Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial with Script

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

Solarwinds

 

The preceding code will return the result depicted in the subsequent figure.

SQL SERVER - Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial with Script SqlQueryResult1

Result of the SQL query

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
SQL SERVER - Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial with Script SqlQueryResult2

Result of the previous SQL query

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

SQL SERVER - Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial with Script sqlQueryResult3

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

Summary

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)

Solarwinds
, , ,
Previous Post
SQLAuthority News – Starting the SQL Journey – How Did I Get Started With SQL?
Next Post
SQL SERVER – Solution to Puzzle – Shortest Code to Perform SSN Validation

Related Posts

151 Comments. Leave new

  • Hi Mr. Pinal,

    As i am new to sql Database I have found this article very informative. Actually i will have to create customize query in order to read data from DB but the problem is Column-Level Encryption is implemented and unluckily i don’t have any key or certificate in order to de crypt columns. Can you please or anyone in community guide me how I can de crypt these columns.

    Reply
  • Hello Sir thank you for this post. Now as per your suggestion i have used encryption in my database by creating a master key, a certificate and a SYMMETRIC key. it works fine now. but problem is when i carry my .mdf files to my client system it gives error of master key. So how to carry these keys with .mdf file

    Reply
  • it gives this error. Please create a master key in the database or open the master key in the session before performing this operation.

    Reply
  • hi..tqs for views..i have a small issue…i have asql backup file..in which some jpegs encrypted..to decrypt the same and get back the files in jpeg format only…any guidance pl…

    Reply
  • but is it possible LIKE search in WHERE condition?

    Reply
  • Rahul Dongare
    April 9, 2015 9:17 pm

    can i logon sql server through SSL certificate?

    Reply
  • Can we logon sql server using SSL Certificate?

    Reply
  • Rahul Dongare
    April 16, 2015 4:35 pm

    Can we configure Certificate-based Authentication to connect(logon) sql client? or any other method to connect sql client using mutual authentication(sql server and sql client)?

    Reply
  • Thanks sir for your response, we have created self signed certificate and we want to connect those client to SQL server who is have this certificate is issued and others should not be Connect. e.g. Server A created certificate and configure with SQL server to use certificate. Now Client B installed certificate issued by server A. Now client B should be connect to SQL server A . If client C does not certificate issued by Server A in this case client C should not be connect to SQL server A.
    This is the actual requirement could you please help?

    Reply
  • Hello sir,
    i was sucessful in implementing this in my project.. But i am facing a prob.

    Problem
    I will encrypt data and put it in my txt file.Later i wl make a bulk insertion of encrypted data in a temporary table. then i wl try to decrypt data and do my other related tasks.

    Right now i am getting error type mismatch for datatype during bulk insertion, i came to know that in txt file, encrypted data has taken string form (nvarchar(max), and decryption takes only varbinary. even when i try to use convert(varbinary(500), encryptdatafromtxtfile), i get null..

    what may be the solution?? plz help:
    in text file i have:
    1,0x00069BE708C5894DB60D1319A0D6D507010000007C7583CA72E8764CEBBD71E407805920A204924833A9CDB9C01C3C4327362490,Software developer

    decrypted form of sec field: anil

    my query:

    create table #temp
    (
    id int,
    name NVARCHAR(MAX),
    designation nvarchar(50)
    )

    BULK
    INSERT #temp
    FROM ‘D:\emp.bat’ –location with filename
    WITH
    (
    FIELDTERMINATOR = ‘,’,
    ROWTERMINATOR = ‘n’
    )
    GO

    ALTER TABLE #temp
    ADD encryptname VARBINARY(500)
    GO

    /* Update binary column with encrypted data created by certificate and key */

    OPEN SYMMETRIC KEY TestTableKey DECRYPTION
    BY CERTIFICATE EncryptTestCert
    UPDATE #temp
    SET encryptname = ENCRYPTBYKEY(KEY_GUID(‘TestTableKey’),[Name])

    /* Decrypt the data of the SecondCol */

    OPEN SYMMETRIC KEY TestTableKey DECRYPTION
    BY CERTIFICATE EncryptTestCert
    SELECT CONVERT(NVARCHAR(MAX),DECRYPTBYKEY(convert(varbinary(500),encryptname))) AS DecryptSecondCol
    FROM #temp

    –here in select i want “anil” as decrypted data

    select * from #temp

    drop table #temp

    Reply
    • Converting varchar to varbinary would change data. You need to take care during import itself.

      Reply
  • tanmaydeveloper
    July 27, 2015 5:07 pm

    Hi,

    It is a nice blog ,
    But I am facing a challenge in doing the fulltext search on the encrypted column

    Can u provide me a article How to do Full text search on encrypted column

    Reply
  • Hi,

    Due to application limitation we are trying to use the same logic to encrypt and decrypt field data using SQL server trigger, and i am getting ‘implict conversion from data type varchar to varbinary is not allowed, use convert function to run this query’, when i ran that SQL outside the trigger it is working fine.. any recommendation. Thanks.

    Joe

    Reply
  • Hello Sir, May I know is this above procedure documented in this article, for encrypting Database using TDE? Or does encrypting DB using TDE, has a different procedure/steps to implement? Can you please guide how to implement TDE in SQL Server 2014?
    Also another doubt, does encrypting DB using TDE, available in SQL Server 2014 Standard and Web Editions? Thank You. Nikhil Satam

    Reply
  • Dear sir,
    I wanted to confirm one thing, i have RSA 3072 size public and private keys in xml format in my application, i wanted to use public key in sql procedure to encrypt data. how should i do that by using ENCRYPTBYKEY function ?
    if yes then tell me are the following steps are correct?
    1. save these keys in some table (but plz tell me in which format?).
    2. use ENCRYPTBYKEY function and pass that key in first argument ?

    Reply
  • Azhar Mansuri
    May 3, 2016 3:17 pm

    Hello Pinal,

    It is really a great and awesome blog I have ever read. Thanks for sharing great explained article. Just I have one small query related to it.

    If I have a string and I just wanted to use this encryption and decryption method, Is it possible to use it?

    I have tried to do so, But when I decrypt the encrypted string, it gives me null.

    Could you help me to resolve this?

    Reply
  • Hi Pinal,

    We have requirement to encrypt entire databse. Is it possible in SQL serevr 2012 Standard?
    If yes, Please help me with the way. ( Any way from Db servcr with SMMS directly)?

    Thank you in advance.

    Reply
  • i am getting Srting or Binary data truncated error. when i convert from varchar(max) to varbinary(max)
    ———
    Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

    Reply
  • Hello,

    In-order to encrypt/decrypt the data entering/retrieving from the db i need to write the decryption/encryption inside stored procedures.

    if a hacker will get my db he will get the stored procedures as well and hence get my SYMMETRIC KEY.

    am i missing something? is there a way to deal with that?

    Thank you

    Reply
  • Hi There, how I can restrict users access to CERTIFICATE and SYMMETRIC KEY. I already created the Database Role for the users are allowded to decrypt the data, but when I run

    OPEN SYMMETRIC KEY EncryptTestTable DECRYPTION
    BY CERTIFICATE EncryptTestCert
    DENY CONTROL ON SYMMETRIC KEY::EncryptTestTable TO Notallowded_ToseeData;
    GO

    getting below error:Cannot grant, deny or revoke permissions to or from special roles.

    So the question is how I restrict access just to Authorised group?

    OPEN SYMMETRIC KEY EncryptTestTable DECRYPTION
    BY CERTIFICATE EncryptTestCert
    GRANT Alter ON SYMMETRIC KEY::EncryptTestTable to Autho_Group;

    Many thanks

    Reply
  • Tamar was on the right track. If a hacker was able to get your username and password to your database, what would be preventing him from decrypting the column just the way a legit user would, since there’s no need to pass in any kind of password to decrypt?

    Reply

Leave a Reply

Menu