SQL Server Best Practices: Using Asymmetric Keys to Implement Column Encryption
By Wang Jianming, Senior Engineer
SQL Server can use symmetric keys to encrypt columns, but this approach suffers from low security. In this article, we show how to use asymmetric keys to implement column encryption in SQL Server and protect users’ critical core privacy data columns.
Symmetric Encryption vs. Asymmetric Encryption
Symmetric encryption is an encryption algorithm that uses the same cryptographic keys for both encryption of plaintext and decryption of ciphertext, while asymmetric encryption uses different keys for encryption and decryption. Therefore, symmetric encryption usually provides relatively low security, and asymmetric encryption provides relatively high security. The following section describes the procedures for symmetric encryption and asymmetric encryption.
Symmetric Encryption Process
Symmetric encryption uses the same keys to encrypt and decrypt data, as shown in the following diagram:
A symmetric encryption process is as follows:
- A data sender uses keys to encrypt plaintext into ciphertext.
- The sender sends the data ciphertext and keys to a recipient over a network.
- The recipient uses the same keys to decrypt the ciphertext and get the plaintext data.
Judging from the entire encryption procedure, the likelihood is high that the keys for encrypting data may be stolen, for example:
- The keys may be stolen during the network transmission process.
- Attackers may use big data analysis to identify ciphertext data patterns using brute-force and break the encryption algorithms.
Therefore, symmetric keys can be stolen, thus, providing a relatively low security level.
Asymmetric Encryption Process
Unlike symmetric encryption, asymmetric encryption uses different keys for encryption and decryption. The keys used for encryption are called public keys, and the keys for decryption are called private keys. Asymmetric encryption provides better security. The following diagram shows an asymmetric encryption process:
The asymmetric encryption process is as follows:
- A data recipient first creates private and public keys and then sends the public key to a data sender (not shown in the diagram).
- The data sender uses the public key to encrypt the plaintext into ciphertext.
- The sender sends the ciphertext to the recipient over a network.
- The recipient receives the ciphertext and uses the private key to decrypt the data to get the final plaintext data.
During the process of asymmetric encryption, private keys are not passed over networks. Therefore, private keys cannot be stolen, providing higher security.
Implementing Asymmetric-Key Column Encryption
The following section provides a detailed description of how to use asymmetric encryption to implement column encryption in SQL Server.
SQL Server 2005 or later supports column encryption by using both symmetric keys and asymmetric keys. The following section explains specific implementation steps and procedures for using asymmetric keys to encrypt mobile phone numbers.
Create a Test Database
Create a dedicated test database named TestDb.
-- Step 1 ¨C Create MSSQL sample database
USE master
GO
IF DB_ID('TestDb') IS NOT NULL
DROP DATABASE [TestDb]
GO
CREATE DATABASE [TestDb];
GOCreate a Test Table
In TestDb, create a dedicated test table named CustomerInfo
-- Step 2 ¨C Create Test Table, init data & verify
USE [TestDb]
GO
IF OBJECT_ID('dbo.CustomerInfo', 'U') IS NOT NULL
DROP TABLE dbo.CustomerInfo
CREATE TABLE dbo.CustomerInfo
(
CustomerId INT IDENTITY(10000,1) NOT NULL PRIMARY KEY,
CustomerName VARCHAR(100) NOT NULL,
CustomerPhone CHAR(11) NOT NULL
);-- Init Table
INSERT INTO dbo.CustomerInfo
VALUES ('CustomerA','13402872514')
,('CustomerB','13880674722')
,('CustomerC','13487759293')
GO-- Verify data
SELECT *
FROM dbo.CustomerInfo
GO
In the original data, user phone numbers are stored in plaintext, which means that whoever has access to the table data can get the phone numbers, as shown below:
Create Instance-Level Master Keys
Create Master Keys at the instance level in the SQL Server database (under the Master database by using the CREATE MASTER KEY statement):
-- Step 3 ¨C Create SQL Server Service Master Key
USE master;
GO
IF NOT EXISTS(
SELECT *
FROM sys.symmetric_keys
WHERE name = '##MS_ServiceMasterKey##')
BEGIN
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'MSSQLSerivceMasterKey'
END;
GOCreate Database-Level Master Keys
Under TestDb in the user database, create Master Keys:
-- Step 4 ¨C Create MSSQL Database level master key
USE [TestDb]
GO
IF NOT EXISTS (SELECT *
FROM sys.symmetric_keys
WHERE name LIKE '%MS_DatabaseMasterKey%')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TestDbMasterKey@3*';
END
GOCreate Asymmetric Keys
Under the user database, create asymmetric keys and encrypt them with a password:
-- Step 5 ¨C Create MSSQL Symmetric Key
USE [TestDb]
GO
IF NOT EXISTS (SELECT *
FROM sys.asymmetric_keys
WHERE name = 'AsymKey_TestDb')
BEGIN
CREATE ASYMMETRIC KEY AsymKey_TestDb
WITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = 'Password4@Asy'
;
END
GOView Asymmetric Keys
You can use the following query statement to view asymmetric keys:
USE [TestDb]
GO
SELECT *
FROM sys.asymmetric_keysThe result is shown as follows:
You can also use SSMS GUIs to view certificates and asymmetric key objects by choosing Security > Certificates > Asymmetric Keys under the user database, as shown in the following screenshot:
Modify Table Structure
Next we need to modify the stable structure and add a new column of type varbinary(max) for storing encrypted phone number ciphertext (assume that we name the new column “EncryptedCustomerPhone”).
-- Step 6 ¨C Change your table structure
USE [TestDb]
GO
ALTER TABLE CustomerInfo
ADD EncryptedCustomerPhone varbinary(MAX) NULL
GOInitialize Data in the New Column
After the new column is added, we encrypt the archived data in the CustomerPhone column of the table into ciphertext and store it in the new column EncryptedCustomerPhone. We do so by using the EncryptByAsymKey function to encrypt the CustomerPhone column, as shown in the following statements:
-- Step 7 ¨C init the encrypted data into the newly column
USE [TestDb]
GO
UPDATE A
SET EncryptedCustomerPhone = ENCRYPTBYASYMKEY(ASYMKEY_ID('AsymKey_TestDb'), CustomerPhone)
FROM dbo.CustomerInfo AS A;
GO
-- Double-check the encrypted data of the new column
SELECT * FROM dbo.CustomerInfoWe can see that the data in the EncryptedCustomerPhone column of the table has become the ciphertext data of the CustomerPhone column after encryption using the asymmetric keys, as show in the following screenshot:
View Encrypted Data
After phone numbers are encrypted into ciphertext, we need to use the DecryptByAsymKey function to decrypt them into plaintext. Let us see whether we can successfully decrypt the EncryptedCustomerPhone field.
-- Step 8 ¨C Reading the SQL Server encrypted data
USE [TestDb]
GO -- Now, it is time to list the original phone number, encrypted phone number, and decrypted phone number.
SELECT
*,
DecryptedCustomerPhone = CONVERT(CHAR(11), DECRYPTBYASYMKEY(ASYMKEY_ID('AsymKey_TestDb'), EncryptedCustomerPhone, N'Password4@Asy'))
FROM dbo.CustomerInfo;
GO
The query statement shows the following results, where data in the CustomerPhone column and the DecryptedCustomerPhone column is identical, indicating that the encryption and decryption are successful.
Adding New Data
Now that the archived data is identical after the encryption and decryption, let us see what happens if new data is added:
-- Step 9 ¨C What if we add a new record to the table.
USE [TestDb]
GO -- Performs the update of the record
INSERT INTO dbo.CustomerInfo (CustomerName, CustomerPhone, EncryptedCustomerPhone)
VALUES ('CustomerD', '13880975623', ENCRYPTBYASYMKEY( ASYMKEY_ID('AsymKey_TestDb'), '13880975623'));
GO
Update Phone Numbers
Now, we try to update user phone numbers:
-- Step 10 ¨C So, what if we update the phone number
USE [TestDb]
GO
-- Performs the update of the record
UPDATE A
SET EncryptedCustomerPhone = ENCRYPTBYASYMKEY( ASYMKEY_ID('AsymKey_TestDb'), '13880971234')
FROM dbo.CustomerInfo AS A
WHERE CONVERT(CHAR(11), DECRYPTBYASYMKEY(ASYMKEY_ID('AsymKey_TestDb'), EncryptedCustomerPhone, N'Password4@Asy')) = '13880975623'
GODelete the Phone Number Plaintext Column
Assuming everything goes as expected, we can delete the plaintext phone number column “CustomerPhone”:
-- Step 11 ¨C Remove old column
USE [TestDb]
GO
ALTER TABLE CustomerInfo
DROP COLUMN CustomerPhone;
GOSELECT
*,
DecryptedCustomerPhone = CONVERT(CHAR(11), DECRYPTBYASYMKEY(ASYMKEY_ID('AsymKey_TestDb'), EncryptedCustomerPhone, N'Password4@Asy'))
FROM dbo.CustomerInfo
GO
The result is shown as follows:
The archived data, the newly added data, the updated data, and everything go as expected. Theoretically, this article could end at this point. However, two questions remain. Can a newly created user access the table data? If not, how can we grant the new user access to the table data?
Adding a New User
Assume that we add a new user named EncryptedDbo:
-- Step 12 ¨C Create a new user and access the encrypted data
USE [TestDb]
GO
CREATE LOGIN EncryptedDbo
WITH PASSWORD=N'EncryptedDbo@3*', CHECK_POLICY = OFF;
GOCREATE USER EncryptedDbo FOR LOGIN EncryptedDbo;GRANT SELECT ON OBJECT::dbo.CustomerInfo TO EncryptedDbo;
GO
Query Data as a New User
We use the newly created user and open a new connection in SSMS to query data:
-- Step 13 ¨C OPEN a new connection query window using the new user and query data
USE [TestDb]
GOSELECT
*,
DecryptedCustomerPhone = CONVERT(CHAR(11), DECRYPTBYASYMKEY(ASYMKEY_ID('AsymKey_TestDb'), EncryptedCustomerPhone, N'Password4@Asy'))
FROM dbo.CustomerInfo
GO
This new user cannot successfully decrypt the EncryptedCustomerPhone. The decrypted value of the DecryptedCustomerPhone field is NULL. This means that new users cannot view the user phone numbers in plaintext, preventing unknown users from getting such core data.
Granting Permission to a New User
A newly added user has no permission to view an encrypted column. To grant a newly added user permission to view data in an encrypted column, we can use the following statements:
--Step 14 ¨C Grant permissions to EncryptedDbo
USE [TestDb]
GOGRANT VIEW DEFINITION ON
ASYMMETRIC KEY::[AsymKey_TestDb] TO [EncryptedDbo];
GO
GRANT CONTROL ON
ASYMMETRIC KEY::[AsymKey_TestDb] TO [EncryptedDbo];
GO
Querying Data Again as a New User
After the required permission is granted, the new user can get the plaintext data in the encrypted column if the user runs the query statement in the “Query data as a new user” section.
-- Step 13 ¨C OPEN a new connection query window using the new user and query data
USE [TestDb]
GOSELECT
*,
DecryptedCustomerPhone = CONVERT(CHAR(11), DECRYPTBYASYMKEY(ASYMKEY_ID('AsymKey_TestDb'), EncryptedCustomerPhone, N'Password4@Asy'))
FROM dbo.CustomerInfo
GO
The result of the second query is shown as follows:
Summary
This monthly report showed how symmetric encryption and asymmetric encryption work and how SQL Server implements column encryption by using asymmetric keys to protect user core data.
