SQL Server 2012 : Encryption (part 1) - Encryption Primer, Password-Based Encryption

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
7/19/2014 9:28:50 PM

Encryption is the process of obscuring information to make it unreadable to those who do not possess some special knowledge. The history of encryption dates far back to the days of Julius Caesar where he used a special algorithm called the Caesar shift cipher to protect military messages. The algorithm was quite simple. It was simply a shift of the alphabet three spaces to the left, as shown in Table 1.


To encrypt a message, you would just take each letter and match its cipher equivalent. For example, if you were to encrypt the word bomb, it would be erpe.

To decrypt a message, simply reverse the process, and look up the plain alphabet letter that aligns with the cipher letter. For example, the encrypted words odcb grj become lazy dog.

Encryption serves a critical role in some compliance requirements. Consider the case where a client computer sends a password over the network and the server authenticates the user based on this password. If attackers were to packet sniff the network, they would easily obtain the credentials the user used to log into the server. Alternatively, consider the case where one user sends an order over the network. The attacker intercepts the order and changes the shipping address to a house that is in foreclosure close to the attacker’s location. There are countless scenarios where sending data and storing data in clear text is suboptimal. Encryption adds an extra layer of protection.

Before you dive into encryption in SQL Server, understanding a few concepts is important: plain text, algorithms, encryption keys, and ciphertext. In our order-changing scenario, the order itself is in plain text. It is, in fact, the sensitive data you are trying to protect. If you were to encrypt the order, you would need two things: an encryption algorithm and, depending on the algorithm, at least one encryption key. If you throw all three of these into a bowl, not only will you make a tasty soup but you’ll have a stream of data called ciphertext. Ciphertext is the sensitive data once it’s been encrypted. SQL Server leverages encryption functions that are part of the Cryptographic API. This API is part of the operating system and exposes a set of functions that allows you to encrypt and decrypt data.

Encryption Primer

To encrypt plain text, you need the plain text itself, an encryption algorithm, and an encryption key. Together, these will produce ciphertext (or in layman’s terms, a bunch of binary data that doesn’t look all that exciting). If you wanted to decrypt the ciphertext and determine the plain text, you could reverse this process and provide the ciphertext, encryption algorithm, and encryption key. Together, these would produce the plain text that you originally encrypted. If you use the same key for encrypting plain text as you do for decrypting the ciphertext, this key is referred to as a symmetric key.

Symmetric keys and their corresponding algorithms provide the best encryption and decryption performance, but there is an inherent problem with their use. If you use the same key to perform encryption as you do decryption, it is safe to say that the key material is sensitive information itself. You can’t simply copy the symmetric key in the file system or database and call it secure. Anyone who can see the key can encrypt or decrypt the data. Symmetric keys are used extensively within SQL Server because of their performance benefit.

An asymmetric key consists of two keys. One is called a public key, and the other is a private key. The idea is the encryption algorithms used with asymmetric keys take plain text and encrypt with the public key to yield the ciphertext. This ciphertext, or encrypted data, can be decrypted only by the private key. Conversely, you could encrypt plain text using a private key that will be decrypted only by an encryption algorithm plus the public key. With asymmetric keys, the private key is as sensitive as symmetric keys and should always be protected when stored. SQL Server also protects private keys.

A certificate is an asymmetric key with some extra metadata. This metadata defines attributes such as a certificate authority and an expiration date, to name a few. Certificates stored in SQL Server are not validated for expiration or against a certificate authority such as VeriSign for authenticity. Rather, certificates are used for a variety of reasons including protecting and managing encryption keys and signing modules in SQL Server.

Now that you are exposed to the basic definitions of these concepts, let’s walk through an example of encrypting data.

Password-Based Encryption

In this example, say you work for a bank called ContosoBank that has a single table called Customers. The auditor requires you to encrypt the social_security_number column. There will be a single login called BankManagerLogin, which will be mapped to the BankManagerUser database user. Data stored in the Social Security number column will be encrypted such that only BankManagerUser will be able to decrypt the contents. The following is a script that will create the ContosoBank database as well as the Customers table and database user:

USE [master]
CREATE LOGIN BankManagerLogin WITH PASSWORD='g4mqw9K@32!@'
USE [ContosoBank]
CREATE USER BankManagerUser FOR LOGIN BankManagerLogin
(customer_id INT PRIMARY KEY,
first_name varchar(50) NOT NULL,
last_name varchar(50) NOT NULL,
social_security_number varbinary(100) NOT NULL)

If you read this script carefully, you may notice that the data type for the social_security_number column is a varbinary. Since ciphertext is binary, the only data type that will support storing encrypted data is varbinary.

Now, you need to create a symmetric key that will be used to encrypt the data. You can do this using the CREATE SYMMETRIC KEY statement as follows:


The AUTHORIZATION parameter describes who the owner of the key is; in this case, it’s the database user BankManagerUser.

You can use a number of different encryption algorithms. Some of the available options include DES, Triple DES, RC2, AES_128, and AES 256-bit. Each one of these algorithms has unique characteristics. Some are faster than others, and some are weaker encryption. DES, for example, can be broken in 22 hours.

When a symmetric key is created, it needs to be protected because simply leaving it in clear text within the database or file system defeats the purpose of encrypting your data. SQL Server will not allow you to create a symmetric key without first specifying how to protect it. In this example, you are encrypting it by a password. This also means that anytime you want to use this key, you will have to specify this password.

All encryption keys are visible in SSMS under the Security node of a specific database. There are also catalog views that return useful information. Sys.symmetric_keys returns a list of symmetric keys, the encryption algorithm defined for the key, and other useful information.

Now that you have created a symmetric key, you are ready to produce encrypted data. A number of functions within SQL Server allow you to encrypt and decrypt data. These functions simply pass parameters down to a call to Microsoft’s Cryptographic API. The function used to encrypt plain text using a symmetric key is called EncryptByKey. To insert data into your table using this function, consider the following script:

EXECUTE AS USER='BankManagerUser'
INSERT INTO Customers VALUES (1,'Howard','Stern',
INSERT INTO Customers VALUES (2,'Donald','Trump',
INSERT INTO Customers VALUES (3,'Bill','Gates',


The EXECUTE AS statement gives sysadmins or users with IMPERSONATE permissions the ability to change the execution context of the current connection. Since you want to simulate being the BankManagerUser, you issue the EXECUTE AS statement. In this example, we’re using this statement to support the script. In the real world, the execution context would already be set by the user who is actually doing the encryption.

When you want to perform any encryption or decryption operation, SQL Server needs to have the key available in memory. The OPEN SYMMETRIC KEY statement will open the key and place it in memory. Notice, at the end of the script, the CLOSE SYMMETRIC KEY statement is given. This statement will release the memory used by the key.

Note The best practice when using encryption is to open all your keys first, perform your encryption or decryption work, and then issue the close statement. The reason for this is that opening the key requires permission checks and other operations that, if done at every encryption statement within your script, would hinder performance.

The EncryptByKey function takes the GUID of an open key and the plain text that you want to encrypt. Rather than typing the GUID of the key, you can use another function called KEY_GUID to obtain this for you. The EncryptByKey function will return the ciphertext or encrypted binary data.

At this point, if you issue a SELECT * FROM Customers statement, you would get the output shown in Table 2.


To decrypt the data, you can issue the following statements:


SELECT customer_id,first_name + ' ' + last_name AS 'Name',
CONVERT(VARCHAR,DecryptByKey(social_security_number)) as 'Social Security Number'
FROM Customers


These statements will return your original table with the Social Security number decrypted, as shown here:

customer_id    Name    Social Security Number

1    Howard Stern    042-32-1324

2    Donald Trump    035-13-6564

3    Bill Gates    533-13-5784

Notice that, in the DecryptByKey function, you did not have to specify which key was used to encrypt the data. The reason for this is there is a thumbprint of the encryption key stored within the header of the encrypted data. Thus, to decrypt as long as you have the key opened in memory using the OPEN SYMMETRIC KEY statement, SQL Server will be able to figure out which key to use for you.

  •  SQL Server 2012 : Auditing in SQL Server (part 3) - Database Audit Specification Object, User-Defined Audit Event
  •  SQL Server 2012 : Auditing in SQL Server (part 2) - Server Audit Specification Object
  •  SQL Server 2012 : Auditing in SQL Server (part 1) - Auditing Objects, Server Audit Object
  •  SQL Server 2012 : Reordering Nodes within the Hierarchy - The GetReparentedValue Method,Transplanting Subtrees
  •  SQL Server 2012 : Querying Hierarchical Tables - The IsDescendantOf Method
  •  Protecting SQL Server Data : Obfuscation Methods (part 4) - Truncation,Encoding, Aggregation
  •  Protecting SQL Server Data : Obfuscation Methods (part 3) - Numeric Variance,Nulling
  •  Protecting SQL Server Data : Obfuscation Methods (part 2) - Repeating Character Masking
  •  Protecting SQL Server Data : Obfuscation Methods (part 1) - Character Scrambling
  •  SQL Server 2012 : Managing Resources - Limiting Resource Use, Leveraging Data Compression
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    programming4us programming4us