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.
Figure 1. Create a database in Toad for SQL Server
Set the new database as the current database:
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.
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.
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).
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).
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).
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).
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 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).
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).
Figure 10. CREATE SYMMETRIC KEY
Verify that the symmetric key got created by querying the sys.symmetric_keyscatalog view:
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_Encryptedand email_Encryptedto 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 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.
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:
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 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'
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 passwordcolumn, and the decrypted email is the same as the unencrypted email in the emailcolumn.
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.
Already in a trial? Talk to sales or buy now online.
Already a loyal fan of Toad for Oracle? Renew now.
- 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.