Jul 28, 2022 9:09:16 AM by Deepak Vohra
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.
SQL Server supports various types of 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 encrypts data inside a client application. The SQL Server Data Engine never knows anything about the encryption.
Transparent Data Encryption (TDE) is the encryption of data at rest, including database data and log files.
SSL Transport Encryption uses Secure Socket Layers (SSL) to encrypt data being transferred between client applications and a database instance.
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:
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:
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', 'firstname.lastname@example.org'),
('Jon', 'fsx6hb78', 'email@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.
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 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 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:
The symmetric key gets listed (Figure 11).
Figure 11. Symmetric key listed
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),
As output in Figure 12 indicates, new columns get added.
Figure 12. New columns added
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:
SET password_Encrypted = EncryptByKey(Key_GUID('login_Key_01')
,password), email_Encrypted = EncryptByKey(Key_GUID('login_Key_01')
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 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'
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
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',
AS 'Decrypted password', email, email_Encrypted
AS 'Encrypted email',
AS 'Decrypted email'
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
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.
Head over to the Toad for Oracle forum on Toad World®! Chat with Toad developers, and lots of experienced users.
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.
Already in a trial? Talk to sales or buy now online.
Already a loyal fan of Toad for Oracle? Renew now.
Tags: Toad for SQL Server
Written by Deepak Vohra
Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.