DEV Community

Cover image for MSSQL Encryptions, Certificates, A-Symmetric Keys and more (Vol 1)
Amar Abaz
Amar Abaz

Posted on • Edited on

MSSQL Encryptions, Certificates, A-Symmetric Keys and more (Vol 1)

#sqlserver #database #encryption #learning

In today's world, protecting sensitive data is a top priority.
I'll be covering this topic in two volumes. I will explore encryption strategies within Microsoft SQL Server, focusing on best practices for securing your data. The second volume will dive into practical examples and some extra tips. In this section, we will focus on the best practices and available options, including

  • Service Master Key (SMK) and Database Master Key (DMK): Key components of key management in SQL Server. Important in securing your database and ensuring key recovery.

  • Symmetric and Asymmetric Keys: Difference and use cases for symmetric (single key) encryption versus asymmetric (public/private key) encryption, and how they protect data at rest and in transit.

  • Certificates: They are a more secure and scalable solution for key management, and they improve your encryption strategy.

  • Transparent Data Encryption (TDE): TDE automatically encrypts an entire database to protect data at rest.

  • TLS Encryption: Protecting data in transit, ensuring that the data moving between your SQL Server instance and client applications is encrypted and secure.

  • Column-Level Encryption: A look at how you can protect sensitive data at the column level, offering flexibility for encryption on specific data within your database.

Properly managing Certificates, Symmetric keys, and Asymmetric keys can be tricky. That is why it's important to avoid relying on simple passwords, and why you should instead leverage Database Master Keys (DMK), Service Master Keys (SMK), and Certificates for a more robust and recoverable encryption strategy.

Database Master Key (DMK) and Service Master Key (SMK)

Both the DMK and SMK are crucial for key management in SQL Server.
The DMK is used to protect certificates and symmetric keys within a database. It's essential for managing encryption at the database level.
The SMK is tied to the SQL Server instance and is used to encrypt the DMK and other server level secrets. After restarting SQL Server if the SMK and DMK are in place and were correctly created, they are reloaded into memory automatically when SQL Server starts. All your certificates (including those signed by the DMK) should be accessible because the SMK and DMK are available in memory to decrypt them.

Why Backups Matter
It's important to back up both the SMK and DMK to ensure that your encryption strategy is recoverable. Backups are critical for disaster recovery, key restoration, and server migrations. They are especially necessary if your server crashes, is rebuilt, or if keys are accidentally deleted or corrupted.

To check if you have SMK and DMK use this command:

SELECT * FROM sys.symmetric_keys WHERE name = '##MS_ServiceMasterKey##';
SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DBMasterKey##';
Enter fullscreen mode Exit fullscreen mode

Command to create them:

CREATE MASTER KEY ENCRYPTION BY PASSWORD='Password$1';

BACKUP MASTER KEY 
TO FILE = 'C:\ABEDMK' ENCRYPTION BY PASSWORD = 'Password';

BACKUP SERVICE MASTER KEY 
TO FILE = 'C:\ABESMK' ENCRYPTION BY PASSWORD = 'Password';
Enter fullscreen mode Exit fullscreen mode

To check whether your certificates are protected by password or DMK use this command:

SELECT name, pvt_key_encryption_type_desc FROM sys.certificates;
Enter fullscreen mode Exit fullscreen mode

Certificates

While SQL Server allows you to use passwords for encryption, using Certificates for key management is a much more secure and scalable solution.
The flexibility of certificates ensures that you can easily back up and recover keys without relying on passwords alone.
Using certificates not only enhances encryption but also allows you to elevate User rights by signing stored procedures. This can be incredibly useful in special cases where users need elevated permissions to perform certain tasks at the system level, such as sending emails or executing jobs from user database.
Certificates offer multiple encryption options, including algorithms like SHA-256 and RSA.
These algorithms ensure that your encryption processes are not only secure but also meet industry standards, providing a higher level of protection for your data.

Create and Backup your certificate with PK and secure it by this command.
Don't forget to write expiry date because by default it is one year.
RSA 2048 is the default encryption algorithm.

CREATE MASTER KEY ENCRYPTION BY PASSWORD='dbPassword$1';

CREATE CERTIFICATE ABECert
WITH SUBJECT='Data_Encryption',
EXPIRY_DATE = '2040-12-31';

BACKUP CERTIFICATE ABECert
TO FILE = 'C:\ABECert'
WITH PRIVATE KEY (file='C:\ABECert_key.pvk',
ENCRYPTION BY PASSWORD='Password');
Enter fullscreen mode Exit fullscreen mode

Because i haven't use ENCRYPTION BY PASSWORD, my certificate is protected by DMK.

Symmetric Keys combined with Certificate (for server-side encryption)

With symmetric keys, you can encrypt sensitive columns at the database level, ensuring that data is encrypted at rest and can only be accessed by authorized users or applications.
My advice for you is not to use encryption by passwords while creating them but rely solely on your protected Certificate.
This approach ensures that sensitive information is kept secure even if the database is compromised, as the data will only be readable when the permission to Certificate is available to user.
Once set up, you can encrypt your columns and decrypt them during SELECT, UPDATE, or INSERT operations.
We'll dive into a practical example of how to implement this case in the next volume.

List all symmetric key by this command:

SELECT * FROM sys.symmetric_keys;
Enter fullscreen mode Exit fullscreen mode

With this command, I am creating a symmetric key that will rely on my certificate that I created earlier.

CREATE SYMMETRIC KEY ABESymmKey
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE ABECert;
Enter fullscreen mode Exit fullscreen mode

Asymmetric Keys and Always Encrypted (for client-side encryption)

Always Encrypted is an excellent feature that uses asymmetric keys for encrypting and decrypting sensitive data transparently.
This way allows you to encrypt sensitive columns directly from the application, ensuring that the decryption key never leaves the client-side application. This ensures maximum data protection.
Always Encrypted ensures that data is encrypted on the client side and stays encrypted when it’s stored in the database, with no possibility of anyone (including DBAs) reading the sensitive data without the decryption keys.

Check if exist by using this command:

SELECT * FROM sys.column_master_keys;
SELECT * FROM sys.column_encryption_keys;
Enter fullscreen mode Exit fullscreen mode

When you INSERT, UPDATE or SELECT, SQL Server will automatically encrypt it using the column encryption key (CEK).
There are two ways of encryption, DTERMINISTIC and RANDOMIZED. Difference is that with Deterministic encryption, identical values (example "Abeamar", "Abeamar") will always encrypt to the same hash.

Once you create your Certificate (in my case CMK_ABEAE_Cert), import it into the server's credentials. Then, to enable Always Encrypted on a column, use the following command:

CREATE COLUMN MASTER KEY [AbeMK]
WITH
(
    KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE', 
    KEY_PATH = 'LocalMachine/My/AbeMK_Cert');

CREATE COLUMN ENCRYPTION KEY [AbeCEK]
WITH VALUES
(
    COLUMN_MASTER_KEY = [AbeMK],
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F0064003900620064006600370032003100650033003400660031003000640033006600390065003200310062003800310035003100310033003300640062003700610061003700660039003100620035001532B80FF86E6773B5DDB0E4A83B1568DA8646400A9612556FED5210F598D8198F7F89E440E891A3DC99ACE3B381FD40E92FFCE7351F1F74DADBE7C591CBA95C94F9640181AA8D3111F20B39FF4F4A0DBA34F1CB1402B7429893BCA3F89607D208FE63236FA13032CB546F5D521360E761E4555DA78D42C061D09B3C55287928CE25FE182C237CA94B57697BE6F0935655DD0F208E2EEEDA357EB99FC27816D79D217E8D913FCDD1690AF7291EF8D452D340266F4A9AE9F964F05B7718F211A400850AB4C95AF417F5F3080CFAD51CA7AE01634FE24DBF050D7C2FAB6759FB92D1302EB4467E55591C1AD44E3F44051BC545F979A6B951962C6C7237B92ABF1FD94E1197AF7C26A3D9C9BF6CF6C7C42C4FE1032084F49233743C5831F38199633A0F86145FEF95EA4187E6F98A67BF206DE5A36258D0A37B3E2D6E353ACF357F1FAEE7B9E59A81C8D223B3A4F70E55C3614AD7134A7C564F0A386C61998CB9E859423D855F23E57E069A70FD568BA9783472EC44354DACA35D5D4FA109DFC444F39143E2A4BD76810D184F7A36CFBF1449D25A0C011DB502D355AB41C47BCF48A6A74D9B53D51C68C431D4CE47FD5C7CF3A050B2CB58B540CDFBDE977C3BCB1CD3C7C7EB7783F30B8BCA9FD3BD30CBF5C8A71AC0480357CB50410E832DD70F790888FE9F14825E64639B5CA59980AAD20C20C22B9207E98AB4CF1D817EF104B4
)
GO

CREATE TABLE Abe_Users
(
    ID INT PRIMARY KEY,
    Name NVARCHAR(100),
    phones NVARCHAR(11)
);
Enter fullscreen mode Exit fullscreen mode

Now, all you need to do is provide your clients with the CEK and inform them to enable Always Encrypted so they can decrypt the data.

To encrypt a column "phones", simply right click on the table and select "Encrypt Column".

Keep in mind that if your application involves frequent data imports or exports, or handling large volumes of data every day the encryption and decryption process may slow down overall performance. Each time data is accessed, it must be decrypted for processing and then encrypted again before storage, which can introduce overhead.

Final thoughts on A-Symmetric options

  • Use Symmetric Encryption when performance is a priority and the database or infrastructure team controls the environment, with appropriate safeguards for key management.

  • Use Always Encrypted with Asymmetric Keys if data security is more important than performance, especially if you need to ensure that even DBAs can't access the data.

And if the level of security required isn't extremely high, and you're more focused on preventing unauthorized access in a lighter way, data masking function is an excellent option. It strikes a balance between usability and security, with practically no performance overhead, no encryption needed. Make it simple, just mask it.

ALTER TABLE [Abe_Users]
ALTER COLUMN [phones] ADD MASKED WITH (FUNCTION = 'partial(2,"XXXXXXX",1)');
Enter fullscreen mode Exit fullscreen mode

Transparent Data Encryption (TDE)

This is another powerful encryption method that encrypts the entire database, including the transaction log, automatically and transparently.
It relies on a Database Encryption Key (DEK), which is encrypted by a certificate stored in the master database.
TDE is useful for securing data at rest, particularly in scenarios where you need to protect the physical files of the database.
We'll dive into a practical example of how to implement TDE in the next volume.
You can check if your database is encrypted, or how much time would it take after triggering encryption using this command:

        SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
        encryption_state_desc =
        CASE encryption_state
                 WHEN '0' THEN 'No encryption'
                 WHEN '1' THEN 'Unencrypted'
                 WHEN '2' THEN 'In progress'
                 WHEN '3' THEN 'Encrypted'
         WHEN '4' THEN 'Key change in progress'
         WHEN '5' THEN 'Decryption in progress'
         WHEN '6' THEN 'Protection change in progress'
                 ELSE 'No Status'
              END,
        percent_complete, 
        create_date, 
        key_algorithm, key_length,
        encryptor_thumbprint, 
        encryptor_type  
        FROM sys.dm_database_encryption_keys;
Enter fullscreen mode Exit fullscreen mode

By using certificates for encryption, employing Database Master Keys (DMK) and Service Master Keys (SMK) for recovery and security, and leveraging Transparent Data Encryption (TDE) for database protection, you can create a robust, recoverable encryption strategy that ensures your data is safe even in the worst case scenario.

😎
Don't miss my other posts for more tips and best practices.
I'll be covering this topic in two volumes, with the second one focusing more on practical examples and also on TLS protections to encrypt data transmitted between a SQL Server instance and a client application, ensuring data privacy and integrity.
Stay tuned..

References:

Top comments (0)