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:

Create sample data
Create a Database Master Key
Create a Certificate
Create a Symmetric Key
Add table columns for encrypted data
Encrypt column data
Verify encrypted columns
Decrypt data
Drop non encrypted columns

Create sample data

We’ll use an example table called UserLoginto 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.

data encryption 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 UserLoginwith 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.

data encryption 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.

data encryption 3

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 masterdatabase protects the private keys of certificates and other cryptographic keys. The database master key is created with the CREATE MASTER KEYTransact-SQL.

First, verify from the sys.symmetric_keyscatalog 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).

data encryption 4

Figure 4. Run CREATE MASTER KEY Transact-SQL

Query the sys.symmetric_keys catalogview again :

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

A master key should get listed (Figure 5).

data encryption 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 PASSWORDclause 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 KEYTransact-SQL in which provide the file name to backup to with the TO FILEclause, and provide the password to use to encrypt the master key in the file with the ENCRYPTION BY PASSWORDclause:

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).

data encryption 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 CERTIFICATETransact-SQL statement to create a certificate in which provides the certificate subject name with the WITH SUBJECTclause:

CREATE CERTIFICATE user_login_cert

WITH SUBJECT = 'User Login Data';

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

data encryption 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).

data encryption 8

Figure 8. Certificate listed

Backup or export the certificate to a file with BACKUP CERTIFICATEstatement in which the file to backup to is provided with the TO FILEclause. The WITH PRIVATE KEYoptional clause specifies that the private key of the certificate is also to be saved to a file; the FILEsub-clause specifies the file to which the private key is exported, and the ENCRYPTION BY PASSWORDsub-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).

data encryption 9

Figure 9. Backing up certificate

Create a Symmetric Key

Create a Symmetric key with CREATE SYMMETRIC KEYTransact-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 ALGORITHMclause 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 CERTIFICATEclause 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).

data encryption 10

Figure 10. CREATE SYMMETRIC KEY

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

SELECT name,

symmetric_key_id,

key_length,

algorithm_desc

FROM sys.symmetric_keys;

The symmetric key gets listed (Figure 11).

data encryption 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_Encryptedand email_Encryptedto 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.

data encryption 12

Figure 12. New columns added

Encrypt data

Next, encrypt the data in the passwordand emailcolumns and store the encrypted data in the password_Encrypted, and email_Encryptedcolumns respectively. But first, open the symmetric key with which to encrypt the data with the OPEN SYMMETRIC KEYSQL 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.

data encryption 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 emailusing the symmetric key and save the result in the column email_Encrypted. Use the EncryptByKey() function to do the encryption. Use an UPDATE … SET SQLstatement 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.

data encryption 14

Figure 14. Encrypted data added to new columns

Verify encrypted columns

Verify encryption with a simple SELECTstatement 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.

data encryption 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.

data encryption 16

Figure 16. ReadLOBs

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

data encryption 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 passwordcolumn, and the decrypted email is the same as the unencrypted email in the emailcolumn.

data encryption 18

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 COLUMNstatement:

ALTER TABLE UserLogin DROP COLUMN password, email;

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

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.

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.

Try Toad free for 30 days.

Free 30-day Trial

Already in a trial? Talk to sales or buy now online.

Already a loyal fan of Toad for Oracle? Renew now.

What’s Next?

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