How to encrypt and decrypt sensitive data in SQL Server

Why do sensitive data need to be encrypted?

In recent years, there have been a lot of news about personal data leakage (personal data usually includes name, address, ID number, financial information, etc.), which has had a significant impact on the company and the person being leaked.

Many countries and regions have introduced laws and regulations on the protection of personal data, such as the EU's General Data Protection Regulation (GDPR). Whether it is for compliance with regulations, data protection or privacy protection, encryption of personal data helps companies comply with these regulations and avoid penalties for violations. After encryption, the thief cannot read the contents even if the data is stolen or intercepted during transmission. Protect personal privacy and prevent sensitive information from being abused.

Therefore, no matter the size of the company, it is very recommended to encrypt sensitive data or personal data.

How to encrypt sensitive data in SQL Server?

Here we take the example of the three fields Name, email, and phone encryption and decryption in the Employee table in the [Person] database of SQL Server as an example to explain the operation in detail.

To encrypt and decrypt fields in the database, you need to follow the following steps and example SQL statements:

1. Create a Master Key: The master key is the key that encrypts other keys, which is the highest level key in the database.

USE Person; -- Specify the database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';

2. Create a certificate: Certificates are usually used to encrypt symmetric keys, which contain both public and private keys.

CREATE CERTIFICATE MyCert
   WITH SUBJECT = 'My Certificate for Encryption',
   EXPIRY_DATE = '20991231';

3. Create a Symmetric Key: Encrypt the symmetric key with a certificate, and then encrypt the data using a symmetric key.

CREATE SYMMETRIC KEY MySymmetricKey
   WITH ALGORITHM = AES_256
   ENCRYPTION BY CERTIFICATE MyCert;

4. Encrypt data: Encrypt the data using a symmetric key.

Suppose we have oneEmployeeTable, we want to encryptName, email, phoneField. First, we need to create a session for the symmetric key and open the key in this session:

OPEN SYMMETRIC KEY MySymmetricKey
   DECRYPTION BY CERTIFICATE MyCert;

Then, we can useENCRYPTBYKEYFunctions to encrypt data:

UPDATE Employee
SET Name = ENCRYPTBYKEY(Key_GUID('MySymmetricKey'), Name),
    email = ENCRYPTBYKEY(Key_GUID('MySymmetricKey'), email),
    phone = ENCRYPTBYKEY(Key_GUID('MySymmetricKey'), phone);

After completing the encrypted data operation, you need to close the session of this symmetric key:

CLOSE SYMMETRIC KEY MySymmetricKey

In this way, the three fields Name, email, and phone encryption and decryption operations in the Employee table in the [Person] database of SQL Server are completed.

How to decrypt the encrypted fields?

Decrypt the data: Decrypt the data using a symmetric key.

To decrypt the data, we useDECRYPTBYKEYfunction:

SELECT 
    CONVERT(NVARCHAR, DECRYPTBYKEY(NAME_ENCRYPTED_FIELD)) as Name,
    CONVERT(NVARCHAR, DECRYPTBYKEY(EMAIL_ENCRYPTED_FIELD)) as email,
    CONVERT(NVARCHAR, DECRYPTBYKEY(PHONE_ENCRYPTED_FIELD)) as phone
FROM Employee;

In this way, you can query the three fields Name, email, and phone decrypted data in the Employee table, and the operation is very simple.

Please note that YourStrongPassword1 in the above steps should be replaced with a strong password to ensure security. At the same time, ensure that in practical applications, encryption and decryption operations comply with organizational security policies and best practices.

When performing the above, make sure you have sufficient permissions to create keys and certificates and understand the encryption mechanism of SQL Server. Depending on actual needs, it may also be necessary to consider backing up and restoring encryption keys, as well as migrating certificates and keys between different databases or servers.

The above is the detailed information on the operation method of encrypting and decrypting sensitive data in SQL Server. For more information about encrypting and decrypting sensitive data in SQL Server, please pay attention to my other related articles!