Data security is a critical task for any organization, especially when it comes to protecting customer personal data. SQL Server provides encryption solutions to protect unauthorized access to data within and outside the organization. In this article, we will explore column level SQL Server encryption using symmetric keys.

Environment Setup

Before we begin, let’s prepare the environment. We will create a new database and a table called CustomerInfo:

CREATE DATABASE CustomerData;
GO

USE CustomerData;
GO

CREATE TABLE CustomerData.dbo.CustomerInfo (
    CustID INT PRIMARY KEY,
    CustName VARCHAR(30) NOT NULL,
    BankACCNumber VARCHAR(10) NOT NULL
);
GO

INSERT INTO CustomerData.dbo.CustomerInfo (CustID, CustName, BankACCNumber)
VALUES
    (1, 'Rajendra', '11111111'),
    (2, 'Manoj', '22222222'),
    (3, 'Shyam', '33333333'),
    (4, 'Akshita', '44444444'),
    (5, 'Kashish', '55555555');

Column Level Encryption Steps

We will follow these steps to implement column level SQL Server encryption:

1. Create a database master key
2. Create a self-signed certificate for SQL Server
3. Configure a symmetric key for encryption
4. Encrypt the column data
5. Query and verify the encryption

Create a Database Master Key

In the first step, we need to create a database master key to protect the private keys and asymmetric keys. We can use the following SQL statement to create the master key:

USE CustomerData;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQLShack@1';

We can verify the existence of the database master key using the sys.symmetric_keys catalog view:

SELECT name AS KeyName, symmetric_key_id AS KeyID, key_length AS KeyLength, algorithm_desc AS KeyAlgorithm
FROM sys.symmetric_keys;

Create a Self-Signed Certificate

In the next step, we create a self-signed certificate using the CREATE CERTIFICATE statement. This certificate will be used for encryption. Execute the following query to create the certificate:

USE CustomerData;
GO

CREATE CERTIFICATE Certificate_test WITH SUBJECT = 'Protect my data';

We can verify the certificate using the sys.certificates catalog view:

SELECT name AS CertName, certificate_id AS CertID, pvt_key_encryption_type_desc AS EncryptType, issuer_name AS Issuer
FROM sys.certificates;

Configure a Symmetric Key

In this step, we will define a symmetric key for encryption. The symmetric key uses a single key for both encryption and decryption. We use the CREATE SYMMETRIC KEY statement to configure the symmetric key:

CREATE SYMMETRIC KEY SymKey_test
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Certificate_test;

We can check the existing keys using the sys.symmetric_keys catalog view:

SELECT name AS KeyName, symmetric_key_id AS KeyID, key_length AS KeyLength, algorithm_desc AS KeyAlgorithm
FROM sys.symmetric_keys;

Data Encryption

Now that we have created the necessary encryption keys, we can proceed with encrypting the column data. In our CustomerData table, the BankACCNumber column data type is VARCHAR(10). We will add a new column with the VARBINARY(MAX) data type to store the encrypted data:

ALTER TABLE CustomerData.dbo.CustomerInfo
ADD BankACCNumber_encrypt VARBINARY(MAX);

To encrypt the data, we need to open the symmetric key and use the EncryptByKey function. Execute the following SQL statements:

OPEN SYMMETRIC KEY SymKey_test
DECRYPTION BY CERTIFICATE Certificate_test;

UPDATE CustomerData.dbo.CustomerInfo
SET BankACCNumber_encrypt = EncryptByKey(Key_GUID('SymKey_test'), BankACCNumber);

Finally, we can close the symmetric key using the CLOSE SYMMETRIC KEY statement:

CLOSE SYMMETRIC KEY SymKey_test;

Data Decryption

To decrypt the encrypted data, we need to open the symmetric key and use the DecryptByKey function. Execute the following SQL statements:

OPEN SYMMETRIC KEY SymKey_test
DECRYPTION BY CERTIFICATE Certificate_test;

SELECT CustID, CustName, BankACCNumber_encrypt AS 'Encrypted data', CONVERT(varchar, DecryptByKey(BankACCNumber_encrypt)) AS 'Decrypted Bank account number'
FROM CustomerData.dbo.CustomerInfo;

Make sure to grant appropriate permissions to the symmetric key and certificate to allow users to decrypt the data.

Conclusion

In this article, we explored column level SQL Server encryption using symmetric keys. By following the steps outlined in this article, you can protect sensitive data within your organization. It is important to consider the specific requirements of your organization and choose the appropriate encryption mechanism.