SQL Server Best Practices: Using Asymmetric Keys to Implement Column Encryption

In this article of the SQL Server best practices series, we will learn how to solve common security problems with symmetric encryption by using asymmetric keys.

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];
GO

Create 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;
GO

Create 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
GO

Create 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
GO

View Asymmetric Keys

You can use the following query statement to view asymmetric keys:

USE [TestDb]
GO
SELECT *
FROM  sys.asymmetric_keys

The 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
GO

Initialize 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.CustomerInfo

We 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'
GO

Delete 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;
GO

SELECT 
    *,
    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;
GO

CREATE 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]
GO

SELECT 
    *,
    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]
GO

GRANT 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]
GO

SELECT 
    *,
    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.

: Dikky Ryan Pratama May 8, 2023 at 3:50 pm

Saya ingin mengambil waktu sejenak untuk mengungkapkan rasa terima kasih saya atas artikel luar biasa yang baru-baru ini Anda publikasikan di Blog Alibaba Cloud. Tulisan Anda menarik dan berwawasan, dan saya menemukan diri saya sepenuhnya tenggelam dalam konten dari awal sampai akhir.Cara Anda menyajikan informasinya informatif dan mudah dipahami, yang membuatnya menjadi bacaan yang menyenangkan bagi saya. Kerja keras dan dedikasi Anda untuk menyediakan konten berkualitas tinggi sangat kami hargai.Terima kasih sekali lagi untuk berbagi pengetahuan dan keahlian tentang hal ini. Saya berharap dapat membaca lebih banyak karya Anda di masa mendatang.

0 0

Community