SQL Server supports data encryption to encrypt, or obfuscate, data so that confidential data may be protected. Data encryption is provided using Transact-SQL functions, passwords, cryptographic keys, and certificates. SQL Server supports hierarchical encryption with which each layer encrypts the layer beneath it. Various encryption mechanisms and paths are available for encrypting data. Data encryption is useful if confidential data is accessed over a public internet. Only authenticated and authorized users may access and decrypt such data. Data encryption may not be suitable in an internal intranet which is already isolated. SQL Server data encryption is in addition to the operating system level password, and SQL Server instance setup/login password protection.

Types of data encryption

SQL Server supports various types of data encryption.

Column Level Data Encryption

Column level encryption encrypts data at the column level using a combination of database master key (DMK), certificates, and cryptographic keys (symmetric/asymmetric keys).

Always Encrypted Data Encryption

Always Encrypted encrypts data inside a client application. The SQL Server Data Engine never knows anything about the encryption.

Transparent Data Encryption

Transparent Data Encryption (TDE) is the encryption of data at rest, including database data and log files.

SSL Transport Data Encryption

SSL Transport Encryption uses Secure Socket Layers (SSL) to encrypt data being transferred between client applications and a database instance.

Backup Data Encryption

Backup Encryption is like Transparent Data Encryption that encrypts SQL Server database backups, including log files instead of active or in-use data and log files.

In this article we’ll discuss Column Level Encryption using Toad for SQL Server. We’ll use the encryption mechanisms of a Database Master Key (DMK)->Certificate->Symmetric key as illustrated at Encryption Hierarchy.

As a preliminary setup, download and install Toad for SQL Server. Create a SQL Server instance in a local machine, or cloud service (Microsoft Azure, Amazon Web Services or other). Create a connection to the SQL Server instance in Toad for SQL Server.

The procedure for Column Level Encryption is as follows:

1. Create sample data
2. Create a Database Master Key
3. Create a Certificate
4. Create a Symmetric Key
5. Add table columns for encrypted data
6. Encrypt column data
7. Verify encrypted columns
8. Decrypt data
9. Drop non encrypted columns

Create sample data

We’ll use an example table called UserLogin to encrypt user password and email for a user. Login to Toad for SQL Server as Admin and open a SQL worksheet or Editor window. Create a new database called USERDB:

CREATE DATABASE USERDB;

Run the SQL script with Execute scripts as shown in Figure 1.

Figure 1. Create a database in Toad for SQL Server

Set the new database as the current database:

USE USERDB;

Create a table called UserLogin with columns userName, password, and email:

CREATE TABLE UserLogin(

                 userName VARCHAR(30) PRIMARY KEY NOT NULL,

                 password VARCHAR(10) NOT NULL,

                 email VARCHAR(55) NOT NULL

                 );

Run the SQL Script in Toad for SQL Server to create a table as shown in Figure 2.

Figure 2. Create a database table called UserLogin

Add sample data to the table:

INSERT INTO UserLogin (userName, password, email)

VALUES ('John', 'a4c6xlm', 'john@example.com'),

       ('Jon', 'fsx6hb78', 'jon@example.com');

Select the sample data:

SELECT * from UserLogin

As shown in Toad for SQL Server (Figure 3), the result set for unencrypted sample data displays username, password and email.

Figure 3. Query Result set for unencrypted sample data

Next, we’ll start to encrypt the sample data, especially the password and email.

Create a Database Master Key

The database master key in the master database protects the private keys of certificates and other cryptographic keys. The database master key is created with the CREATE MASTER KEY Transact-SQL.

First, verify from the sys.symmetric_keys catalog view that a master key does not already exist.

SELECT * FROM sys.symmetric_keys WHERE name LIKE '%MS_DatabaseMasterKey%'

The result from the previous SQL should not list any master key. If a master key does already exist and a new master key is preferred, the previous key may be dropped with:

DROP MASTER KEY

The master key is encrypted by using the AES_256 algorithm and a user provided password. Next, create the master key using the CREATE MASTER KEY statement; supply a suitable password instead of the <master key password> placeholder in Transact-SQL:

CREATE MASTER KEY ENCRYPTION BY  

PASSWORD = '<master key password>';

The output message from the SQL is shown in Toad for SQL Server (Figure 4).

Figure 4. Run CREATE MASTER KEY Transact-SQL

Query the sys.symmetric_keys catalog view again :

SELECT * FROM sys.symmetric_keys WHERE name LIKE '%MS_DatabaseMasterKey%'

A master key should get listed (Figure 5).

Figure 5. Master key listed

It is best to back up the master key to a file. To do this, you’ll first need to open the master key with OPEN MASTER KEY, using the DECRYPTION BY PASSWORD clause to decrypt using the same password with which the master key was encrypted:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<master key password>';

Back up the master key with BACKUP MASTER KEY Transact-SQL in which provide the file name to backup to with the TO FILE clause, and provide the password to use to encrypt the master key in the file with the ENCRYPTION BY PASSWORD clause:

BACKUP MASTER KEY TO FILE = 'masterkey.mk'

   ENCRYPTION BY PASSWORD = '<master key backup password>';

The output message from the SQL is shown in Toad for SQL Server (Figure 6).

Figure 6. BACKUP MASTER KEY TO FILE Transact-SQL

Create a Certificate

Create a self-signed certificate, which is a database securable that follows the X.509 standard. Use the CREATE CERTIFICATE Transact-SQL statement to create a certificate in which provides the certificate subject name with the WITH SUBJECT clause:

CREATE CERTIFICATE user_login_cert    

   WITH SUBJECT = 'User Login Data';

A certificate gets created as shown in Toad for SQL Server (Figure 7).

Figure 7. Create certificate

Verify that the certificate got added from the sys.certificates catalog view:

SELECT * FROM sys.certificates where name = ‘user_login_cert’

The certificate gets listed (Figure 8).

Figure 8. Certificate listed

Backup or export the certificate to a file with BACKUP CERTIFICATE statement in which the file to backup to is provided with the TO FILE clause. The WITH PRIVATE KEY optional clause specifies that the private key of the certificate is also to be saved to a file; the FILE sub-clause specifies the file to which the private key is exported, and the ENCRYPTION BY PASSWORD sub-clause specifies the password used to encrypt the private key:

BACKUP CERTIFICATE user_login_cert TO FILE = 'user_login_cert.cer'

   WITH PRIVATE KEY (

         FILE = 'user_login_cert.pvk',

         ENCRYPTION BY PASSWORD = '<private key encryption password>');

The certificate gets exported to a file, and the password encrypted private key for the certificate gets exported to a different file when SQL is run with output as shown in Toad for SQL Server (Figure 9).

Figure 9. Backing up certificate

Create a Symmetric Key

Create a Symmetric key with CREATE SYMMETRIC KEY Transact-SQL statement. A symmetric key must be encrypted by one or more of the following: password, certificate, symmetric key, asymmetric key or PROVIDER. The WITH ALGORITHM clause specifies the algorithm to use; no default encryption algorithm is used. As of SQL Server 2016, all algorithms other than AES_128, AES_192 and AES_256 are deprecated. Use the ENCRYPTION BY CERTIFICATE clause to encrypt the symmetric key with a certificate:

CREATE SYMMETRIC KEY login_Key_01

   WITH ALGORITHM = AES_256

   ENCRYPTION BY CERTIFICATE user_login_cert;

A symmetric key gets created (Figure 10).

Figure 10. CREATE SYMMETRIC KEY

Verify that the symmetric key got created by querying the sys.symmetric_keys catalog view:

SELECT name,

   symmetric_key_id,

   key_length,

   algorithm_desc

FROM sys.symmetric_keys;

The symmetric key gets listed (Figure 11).

Figure 11. Symmetric key listed

Add table columns for encrypted data

The table column data that is encrypted is stored in a new column as encrypted data. Add new columns called password_Encrypted and email_Encrypted to the UserLogin table:

ALTER TABLE UserLogin  

   ADD password_Encrypted varbinary(MAX),

   email_Encrypted varbinary(MAX);

As output in Figure 12 indicates, new columns get added.

Figure 12. New columns added

Encrypt data

Next, encrypt the data in the password and email columns and store the encrypted data in the password_Encrypted, and email_Encrypted columns respectively. But first, open the symmetric key with which to encrypt the data with the OPEN SYMMETRIC KEY SQL statement; the certificate to decrypt the symmetric key is the same certificate with which it was encrypted:

OPEN SYMMETRIC KEY login_Key_01

   DECRYPTION BY CERTIFICATE user_login_cert;

The symmetric key gets opened as shown by output in Figure 13.

Figure 13. Open symmetric key

Encrypt the value in the column password, using the symmetric key and save the result in the column password_Encrypted. Similarly, encrypt the value in the column email using the symmetric key and save the result in the column email_Encrypted. Use the EncryptByKey() function to do the encryption. Use an UPDATE … SET SQL statement to run SQL:

UPDATE UserLogin

SET password_Encrypted = EncryptByKey(Key_GUID('login_Key_01')

   ,password), email_Encrypted = EncryptByKey(Key_GUID('login_Key_01')

   ,email);

As the output in Figure 14 indicates, data gets encrypted and stored in the new columns.

Figure 14. Encrypted data added to new columns

Verify encrypted columns

Verify encryption with a simple SELECT statement to list the encrypted column data:

SELECT password, password_Encrypted  

   AS 'Encrypted password', email, email_Encrypted  

   AS 'Encrypted email'

   FROM UserLogin;

The encrypted column data gets listed, but at first it may be Excluded as shown in Figure 15.

Figure 15. Encrypted column data excluded at first

Double-click on an Excluded column and a dialog gets displayed as shown in Figure 16. Click on ReadLOBs. The option Options>Database>General>Read LOBs could also be configured prior to running the query.

Figure 16. ReadLOBs

The encrypted data gets displayed in Result Sets (Figure 17).

Figure 17. Encrypted data listed

Decrypt data

How would you know if the encrypted data actually represents the original data that is encrypted? The only way to find it is by decrypting the encrypted data. Use the DecryptByKey() function to decrypt the encrypted data.

SELECT password, password_Encrypted  

   AS 'Encrypted password',

   CONVERT(varchar, DecryptByKey(password_Encrypted))  

   AS 'Decrypted password', email, email_Encrypted  

   AS 'Encrypted email',

   CONVERT(varchar, DecryptByKey(email_Encrypted))  

   AS 'Decrypted email'

   FROM UserLogin;  

As shown by the result set (Figure 18), the decrypted password is the same as the unencrypted password in the password column, and the decrypted email is the same as the unencrypted email in the email column.

Figure 18. Decrypted data same as unencrypted data

Drop the unencrypted columns

Because we have encrypted the password and email columns’ data and stored them in new columns, we don’t need the original password and email columns for the unencrypted data, and these columns may be removed/dropped with an ALTER TABLE …. DROP COLUMN statement:

ALTER TABLE UserLogin DROP COLUMN password, email;

Close the symmetric key if it is not needed with CLOSE SYMMETRIC KEY statement:

CLOSE SYMMETRIC KEY login_Key_01;

In this article we discussed SQL Server data encryption with an example for column level encryption with Toad for SQL Server. 

Related Links

Blog: Free database webinar: Toad® loves ApexSQL 

Blog: Toad for Oracle Sensitive Data Protection Module FAQ 

Blog: Cyber Security – Why updating your Toad® matters – Part 3 

Blog: Optimizing SQL Server code in Toad for SQL Server 

Have questions, comments? 

Head over to the Toad for Oracle forum on Toad World®!  Chat with Toad developers, and lots of experienced users. 

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post.

What’s Next?

1. Explore and Select a Toad product at https://support.quest.com/download-product-select 
2. Toad Edge download Free Trial Version from https://www.quest.com/register/111545/ 
3. Buy Online or Request Pricing for Toad Edge at https://shop.quest.com/682/purl-toad-edge-selection
4. Get Product Support for Toad Edge at https://support.quest.com/
5. Have questions about Toad Developer Tools? Click Start Discussion and this blog topic will be transferred to the Toad World Forums.