SQL Server Security

Getting Started with Always Encrypted

Always Encrypted is a client-side encryption feature that protects sensitive data in Azure SQL Database and SQL Server, such as credit card numbers, social security numbers, or other personal identification information. Using Always Encrypted, developers can encrypt data using "Always Encrypted keys" that are stored in a key store outside of SQL Server. When data is sent to SQL Server, it is automatically encrypted by the Always Encrypted client driver. When the data is retrieved, it is automatically decrypted by the client driver. This guide will walk you through the essential steps to get started.

Prerequisites

Before you begin, ensure you have the following:

• SQL Server Management Studio (SSMS) version 17.x or later.
• .NET Framework 4.6 or later, or .NET Core 2.0 or later.
• A SQL Server instance (Azure SQL Database, SQL Server 2016 or later).
• A master key (Column Master Key) stored in a supported key store:
  • Windows Certificate Store
  • Azure Key Vault
  • Hardware Security Module (HSM)

Step 1: Set up Your Key Store

First, you need to configure your chosen key store and create a Column Master Key (CMK).

Using Windows Certificate Store:

1. Open the Certificate Manager (run certmgr.msc).
2. Navigate to Certificates - Current User -> Personal -> Certificates.
3. Right-click on Certificates, select All Tasks -> Request New Certificate....
4. Follow the wizard to create a self-signed certificate. For testing, a basic certificate will suffice.
5. Ensure the certificate is accessible by the user account running SQL Server Management Studio or your application.

Using Azure Key Vault:

1. Create an Azure Key Vault instance if you don't have one.
2. Create a new Key Vault Key (RSA type is recommended).
3. Grant necessary permissions to your SQL Server or application principal to access the key.

Step 2: Create a Column Master Key (CMK) in SQL Server

Once your key store is ready, you can create a CMK definition within SQL Server that points to your external key.

-- Example using Windows Certificate Store
CREATE COLUMN MASTER KEY MyCMK
WITH (
    KEY_STORE_PROVIDER_NAME = N'CERTIFICATE_STORE',
    KEY_PATH = N'CurrentUser/My/<CertificateThumbprint>' -- Replace with your certificate's thumbprint
);

-- Example using Azure Key Vault
CREATE COLUMN MASTER KEY MyKeyVaultCMK
WITH (
    KEY_STORE_PROVIDER_NAME = N'AZURE_KEY_VAULT',
    KEY_PATH = N'https://mykeyvault.vault.azure.net/keys/mykeyname/mykeyversion' -- Replace with your Key Vault URL and key details
);
                

Step 3: Create a Column Encryption Key (CEK)

A Column Encryption Key (CEK) is used to encrypt the actual data. It is itself encrypted by the Column Master Key (CMK).

-- Example using the CMK created above
CREATE COLUMN ENCRYPTION KEY MyCEK
WITH VALUES
(
    COLUMN_MASTER_KEY = MyCMK,
    KEY_STORE_PROVIDER_NAME = N'CERTIFICATE_STORE', -- Must match CMK provider
    ENCRYPTED_VALUE = -- Base64 encoded encrypted CEK, generated by client tools
    -- Placeholder for actual encrypted value
    -- This value is typically generated by SSMS or client applications.
    -- You would run a command like:
    -- EXEC sp_create_column_encryption_key 'MyCEK', 'MyCMK';
    -- and copy the output.
);
                

To generate the ENCRYPTED_VALUE, you typically use SSMS or client-side tools. For example, in SSMS:

-- In SSMS, use this command to generate the CEK and its encrypted value
EXEC sp_create_column_encryption_key 'MyCEK', 'MyCMK';
                

The output of this command will provide the necessary values to complete the CREATE COLUMN ENCRYPTION KEY statement.

Step 4: Create or Alter Table Columns

Define which columns will be encrypted using Always Encrypted.

CREATE TABLE SensitiveData (
    ID INT PRIMARY KEY,
    FirstName VARCHAR(100) ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, COLUMN_ENCRYPTION_KEY = MyCEK),
    LastName VARCHAR(100) ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED, COLUMN_ENCRYPTION_KEY = MyCEK),
    CreditCardNumber VARCHAR(16) ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, COLUMN_ENCRYPTION_KEY = MyCEK)
);

-- Or alter an existing table
-- ALTER TABLE ExistingTable
-- ALTER COLUMN SensitiveColumn VARCHAR(50) ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, COLUMN_ENCRYPTION_KEY = MyCEK);
                

• DETERMINISTIC: Allows for equality lookups, grouping, and joining on encrypted columns.
• RANDOMIZED: Provides a higher level of security but does not support lookups, grouping, or joining on encrypted columns.

Step 5: Insert and Query Data

When you insert data into encrypted columns, the client driver encrypts it before sending it to SQL Server. When you query these columns, the driver automatically decrypts the data.

-- Insert data (SSMS or application handles encryption)
INSERT INTO SensitiveData (ID, FirstName, LastName, CreditCardNumber)
VALUES (1, 'John', 'Doe', '1234567890123456');

-- Query data (SSMS or application handles decryption)
SELECT ID, FirstName, LastName FROM SensitiveData WHERE ID = 1;
                

Next Steps

Explore advanced configurations, performance considerations, and best practices for using Always Encrypted in production environments.

• Key Management
• Performance Tuning
• Troubleshooting