Testing Always Encrypted with Parameterized Queries in SQL Server

Introduction

Always Encrypted is a feature in SQL Server designed to protect sensitive data, such as Social Security numbers or credit card information. In this guide, we’ll focus on testing Always Encrypted using parameterized queries to ensure data remains secure during common operations like searching, inserting, and updating.

Understanding Encryption Types

When working with encrypted columns, it’s important to understand the two types of encryption:

• Deterministic Encryption: Encrypts data so that the same plaintext value always results in the same ciphertext. This allows for equality searches and grouping.
• Randomized Encryption: Encrypts data differently each time, providing higher security but not supporting search operations.

For testing purposes, especially when performing searches in the WHERE clause, use deterministic encryption.

Updating and Inserting Encrypted Data

To insert or update data in an encrypted column, the SQL client must be “Always Encrypted aware,” meaning it can handle encryption and decryption operations.

Enabling Parameterized Queries

When using applications like .NET, the client-side framework must support Always Encrypted to allow updates and inserts. Ensure your application uses parameterized queries to pass encrypted values so that encryption happens transparently.

• Use the latest version of Microsoft.Data.SqlClient, which fully supports Always Encrypted.

Configuring the Client

Both SQL Server Management Studio (SSMS) and applications must have the Always Encrypted option enabled.

• For applications: Add Column Encryption Setting=Enabled; to your connection string.
• For SSMS: Adjust the query options to enable Always Encrypted.

Enabling Always Encrypted in SSMS

1. Open SSMS and connect to your database.
2. Open a new query window.
3. In the menu bar, click on Query and select Query Options.
4. Expand the Execution tab and select Advanced.
5. Check Enable Parameterization for Always Encrypted.
6. Click OK.

Setting Up Always Encrypted

1. Configure a Column Master Key

1. In SSMS, expand your database and navigate to Security > Always Encrypted Keys.
2. Right-click Column Master Keys and select New Column Master Key….
3. Name the key CMK1.
4. Choose Generate Self-Signed Certificate.
5. Click OK.

This creates a self-signed certificate and stores it in your personal certificate store.

2. Configure a Column Encryption Key

1. Right-click Column Encryption Keys and select New Column Encryption Key….
2. Name the key CEK1.
3. Select CMK1 as the column master key.
4. Click OK.

Creating a Table with Encrypted Columns

Create a table with columns encrypted using deterministic and randomized encryption.

CREATE TABLE [dbo].[Clients](
    [PatientId] [int] IDENTITY(1,1) NOT NULL,
     COLLATE Latin1_General_BIN2 
        ENCRYPTED WITH (
            COLUMN_ENCRYPTION_KEY = [CEK1], 
            ENCRYPTION_TYPE = Deterministic, 
            ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
        ) NOT NULL,
     NOT NULL,
     NOT NULL,
     NULL,
     NULL,
     NULL,
    [ZipCode] [int] NULL,
     NULL,
     
        ENCRYPTED WITH (
            COLUMN_ENCRYPTION_KEY = [CEK1], 
            ENCRYPTION_TYPE = Randomized, 
            ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
        ) NOT NULL,
    PRIMARY KEY CLUSTERED ([PatientId] ASC)
) ON [PRIMARY];

– SSN uses deterministic encryption to allow for searches.
– BirthDate uses randomized encryption for higher security.

Inserting Records Using Parameterized Queries

To insert data, use parameterized queries so that the client handles encryption transparently.

Using T-SQL in SSMS

DECLARE @SSN nvarchar(11) = '795-73-9838';
DECLARE @BirthDate datetime2(7) = '1980-01-01';

INSERT INTO [dbo].[Clients] (
    [SSN],
    [FirstName],
    [LastName],
    [MiddleName],
    [StreetAddress],
    [City],
    [ZipCode],
    [State],
    [BirthDate]
)
VALUES (
    @SSN,
    'John',
    'Doe',
    'M',
    '123 Main St',
    'New York',
    10001,
    'NY',
    @BirthDate
);

Note: Ensure that Enable Parameterization for Always Encrypted is checked in your query options.

Using PowerShell

$connectionString = "Data Source=YourServer;Initial Catalog=AlwaysEncryptedDb;Integrated Security=True;Column Encryption Setting=Enabled;"

$query = @"
INSERT INTO dbo.Clients (
    [SSN],
    [FirstName],
    [LastName],
    [MiddleName],
    [StreetAddress],
    [City],
    [ZipCode],
    [State],
    [BirthDate]
) VALUES (
    @SSN,
    @FirstName,
    @LastName,
    @MiddleName,
    @StreetAddress,
    @City,
    @ZipCode,
    @State,
    @BirthDate
);
"@

$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$command = $connection.CreateCommand()
$command.CommandText = $query

$command.Parameters.Add("@SSN", [System.Data.SqlDbType]::NVarChar, 11).Value = "123-45-6789"
$command.Parameters.Add("@FirstName", [System.Data.SqlDbType]::NVarChar, 50).Value = "Yvonne"
# Add other parameters as needed

$connection.Open()
$command.ExecuteNonQuery()
$connection.Close()

Reading Encrypted Data

To read encrypted data, ensure your connection includes Column Encryption Setting=Enabled.

SELECT * FROM [dbo].[Clients];

– Without encryption enabled: Encrypted columns display as binary data.
– With encryption enabled: Encrypted columns display decrypted values.

Updating Encrypted Data Using Parameterized Queries

Using PowerShell

$connectionString = "Data Source=YourServer;Initial Catalog=AlwaysEncryptedDb;Integrated Security=True;Column Encryption Setting=Enabled;"

$patientId = 1
$newSSN = "123-45-1234"
$query = "UPDATE dbo.Clients SET SSN = @newSSN WHERE PatientId = @patientId"

$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$command = $connection.CreateCommand()
$command.CommandText = $query

$command.Parameters.Add("@newSSN", [System.Data.SqlDbType]::NVarChar, 11).Value = $newSSN
$command.Parameters.Add("@patientId", [System.Data.SqlDbType]::Int).Value = $patientId

$connection.Open()
$command.ExecuteNonQuery()
$connection.Close()

Using C#

using System.Data.SqlClient;

string connectionString = "Data Source=YourServer;Initial Catalog=AlwaysEncryptedDb;Integrated Security=True;Column Encryption Setting=Enabled;";

int patientId = 1;
string newSSN = "123-45-1234";

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    string sql = "UPDATE dbo.Clients SET SSN = @newSSN WHERE PatientId = @patientId";
    using (SqlCommand cmd = new SqlCommand(sql, conn))
    {
        cmd.Parameters.AddWithValue("@newSSN", newSSN);
        cmd.Parameters.AddWithValue("@patientId", patientId);
        cmd.ExecuteNonQuery();
    }
}

Using T-SQL in SSMS

DECLARE @SSN nvarchar(11) = '795-73-9838';
UPDATE [dbo].[Clients] SET [SSN] = @SSN
WHERE [PatientId] = 1;

Ensure that Enable Parameterization for Always Encrypted is checked in your query options.

Conclusion

Testing Always Encrypted with parameterized queries ensures that encryption and decryption processes happen transparently, without requiring developers to manage cryptographic details. This enhances security and reduces the risk of data leaks, even if unauthorized access to the database occurs.

By following these steps, you can securely implement and test Always Encrypted in SQL Server, protecting your sensitive data while maintaining application functionality and performance.

References:

• Always Encrypted (Microsoft Docs)
• Using Always Encrypted with the .NET Framework Data Provider for SQL Server