Skip to content

always-encrypted-tutorial-getting-started.md

Latest commit

 

History

History
539 lines (414 loc) · 25.6 KB

always-encrypted-tutorial-getting-started.md

File metadata and controls

539 lines (414 loc) · 25.6 KB
Raw
title Tutorial: Getting started with Always Encrypted
description This tutorial teaches you how to encrypt columns using Always Encrypted and how to query encrypted columns in SQL Server, Azure SQL Database, and Azure SQL Managed Instance.
author jaszymas
ms.author jaszymas
ms.reviewer vanto
ms.date 6/17/2026
ms.service sql
ms.subservice security
ms.topic tutorial
ms.custom
sfi-image-nochange
sfi-ropc-blocked

Tutorial: Getting started with Always Encrypted

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]

This tutorial shows you how to get started with Always Encrypted. The tutorial covers:

[!div class="checklist"]

  • How to encrypt selected columns in your database.
  • How to query encrypted columns.

Note

If you're looking for information on Always Encrypted with secure enclaves, see the following tutorials instead:

Prerequisites

For this tutorial, you need:

Note

Microsoft recommends using PowerShell 7 or later when running Always Encrypted PowerShell scripts. PowerShell 7 provides improved cross-platform support, better performance, and the latest compatibility with the SqlServer module (v22+), which is required for many Always Encrypted scenarios.

Step 1: Create and populate the database schema

In this step, you'll create the HR schema and the Employees table. Then, you'll populate the table with some data.

SSMS

  1. Connect to your database. For instructions on how to connect to a database from SSMS, see Quickstart: Connect and query an Azure SQL Database or an Azure SQL Managed Instance using SQL Server Management Studio (SSMS) or Quickstart: Connect and query a SQL Server instance using SQL Server Management Studio (SSMS).

  2. Open a new query window for the ContosoHR database.

  3. Paste in and execute the below statements to create a new table, named Employees.

    CREATE SCHEMA [HR];
GO

CREATE TABLE [HR].[Employees]
(
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL
    , [SSN] [char](11) NOT NULL
    , [FirstName] [nvarchar](50) NOT NULL
    , [LastName] [nvarchar](50) NOT NULL
    , [Salary] [money] NOT NULL
) ON [PRIMARY];

  4. Paste in and execute the below statements to add a few employee records to the Employees table.

    INSERT INTO [HR].[Employees]
(
    [SSN]
    , [FirstName]
    , [LastName]
    , [Salary]
)
VALUES
(
    '795-73-9838'
    , N'Catherine'
    , N'Abel'
    , $31692
);

INSERT INTO [HR].[Employees]
(
    [SSN]
    , [FirstName]
    , [LastName]
    , [Salary]
)
VALUES
(
    '990-00-6818'
    , N'Kim'
    , N'Abercrombie'
    , $55415
);

PowerShell

In a PowerShell session, execute the following commands. Make sure you update the connection string with the address of your server and authentication settings that are valid for your database.

Import-Module "SqlServer" -MinimumVersion 22.0.50

# Connect to your database
# Set the valid server name, database name and authentication keywords in the connection string
$serverName = "<server name>.database.windows.net"
$databaseName = "ContosoHR"
$connStr = "Server=tcp:$serverName,1433;Database=$databaseName;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Interactive"

try {
	$database = Get-SqlDatabase -ConnectionString $connStr -Encrypt Mandatory -ErrorAction Stop
}
catch {
	Write-Error "Failed to connect. Verify server name, database name, Azure SQL firewall access, and Microsoft Entra permissions."
	throw
}


$query = @'
 IF SCHEMA_ID(N'HR') IS NULL
BEGIN
    EXEC(N'CREATE SCHEMA [HR]');
END;

IF OBJECT_ID(N'HR.Employees', N'U') IS NULL
BEGIN
    CREATE TABLE [HR].[Employees]
    (
        [EmployeeID] INT IDENTITY(1,1) NOT NULL,
        [SSN] CHAR(11) NOT NULL,
        [FirstName] NVARCHAR(50) NOT NULL,
        [LastName] NVARCHAR(50) NOT NULL,
        [Salary] MONEY NOT NULL,
        CONSTRAINT [PK_HR_Employees] PRIMARY KEY CLUSTERED ([EmployeeID])
    );
END;
'@

#write-host @query

Invoke-SqlCmd -ConnectionString $connStr -Query $query

# Add a few rows to the Employees table.
$query = @'
    INSERT INTO [HR].[Employees]
    (
        [SSN]
        , [FirstName]
        , [LastName]
        , [Salary]
    )
    VALUES
    (
        '795-73-9838'
        , N'Catherine'
        , N'Abel'
        , $31692
    );

    INSERT INTO [HR].[Employees]
    (
        [SSN]
        , [FirstName]
        , [LastName]
        , [Salary]
    )
    VALUES
    (
        '990-00-6818'
        , N'Kim'
        , N'Abercrombie'
        , $55415
    );
'@
Invoke-SqlCmd -ConnectionString $connStr -Query $query

Write-Host 'Completed successfully.'

Step 2: Encrypt columns

In this step, you'll provision a column master key and a column encryption key for Always Encrypted. Then, you'll encrypt the SSN and Salary columns in the Employees table.

SSMS

SSMS provides a wizard that helps you easily configure Always Encrypted by setting up a column master key, a column encryption key, and encrypt selected columns.

  1. In Object Explorer, expand Databases > ContosoHR > Tables.

  2. Right-click the Employees table and select Encrypt Columns to open the Always Encrypted wizard.

    :::image type="content" source="media/always-encrypted-database-engine/always-encrypted-wizard-opening.png" alt-text="Screenshot of opening the Always Encrypted Wizard.":::

  3. Select Next on the Introduction page of the wizard.

  4. On the Column Selection page.

    1. Select the SSN and Salary columns. Choose deterministic encryption for the SSN column and randomized encryption for the Salary column. Deterministic encryption supports queries, such as point lookup searches that involve equality comparisons on encrypted columns. Randomized encryption doesn't support any computations on encrypted columns.
    2. Leave CEK-Auto1 (New) as the column encryption key for both columns. This key doesn't exist yet and will be generated by the wizard.
    3. Select Next.

    :::image type="content" source="media/always-encrypted-database-engine/always-encrypted-wizard-column-selection.png" alt-text="Screenshot of the Always Encrypted Wizard column selection.":::

  5. On the Master Key Configuration page, configure a new column master key that will be generated by the wizard. First, you need to select where you want to store your column master key. The wizard supports two key store types:

    • Azure Key Vault - recommended if your database is in Azure
    • Windows certificate store

    In general, Azure Key Vault is the recommended option, especially if your database is in Azure.

    • To use Azure Key Vault:

      1. Select Azure Key Vault.
      2. Select Sign in and complete signing in to Azure.
      3. After you've signed in, the page will display the list of subscriptions and key vaults, you have access to. Select an Azure subscription containing the key vault, you want to use.
      4. Select your key vault.
      5. Select Next.

      :::image type="content" source="media/always-encrypted-database-engine/always-encrypted-wizard-configuring-master-key-in-key-vault.png" alt-text="Screenshot of the Always Encrypted Wizard master key selection using Azure Key Vault.":::

    • To use Windows certificate store:

      1. Select Windows certificate store.

      2. Leave the default selection of Current User - this will instruct the wizard to generate a certificate (your new column master key) in the Current User store.

        :::image type="content" source="media/always-encrypted-database-engine/always-encrypted-wizard-configuring-master-key-in-certificate-store.png" alt-text="Screenshot of the Always Encrypted Wizard master key selection using the certificate store.":::

      3. Select Next.

  6. On the In-Place Encryption Settings page, no additional configuration is required because the database does not have an enclave enabled. Select Next.

  7. On the Run Settings page, you're asked if you want to proceed with encryption or generate a PowerShell script to be executed later. Leave the default settings and select Next.

  8. On the Summary page, the wizard informs you about the actions it will execute. Check all the information is correct and select Finish.

  9. On the Results page, you can monitor the progress of the wizard's operations. Wait until all operations complete successfully and select Close.

    :::image type="content" source="media/always-encrypted-database-engine/always-encrypted-wizard-summary.png" alt-text="Screenshot of the Always Encrypted Wizard summary.":::

  10. (Optional) Explore the changes the wizard has made in your database.

    1. Expand ContosoHR > Security > Always Encrypted Keys to explore the metadata objects for the column master key and the column encryption that the wizard created.

    2. You can also run the below queries against the system catalog views that contain key metadata.

      SELECT * FROM sys.column_master_keys;
SELECT * FROM sys.column_encryption_keys
SELECT * FROM sys.column_encryption_key_values

    3. In Object Explorer, right-click the Employees table and select Script Table as > CREATE To > New Query Editor Window. This will open a new query window with the CREATE TABLE statement for the Employees table. Note the ENCRYPTED WITH clause that appears in the definitions of the SSN and Salary columns.

    4. You can also run the below query against sys.columns to retrieve column-level encryption metadata for the two encrypted columns.

      SELECT
[name]
, [encryption_type]
, [encryption_type_desc]
, [encryption_algorithm_name]
, [column_encryption_key_id]
FROM sys.columns
WHERE [encryption_type] IS NOT NULL;

PowerShell

  1. Create a column master key in your key store.

    • If you're using Azure Key Vault, execute the below commands to create an asymmetric key in your key vault. Make sure you provide the correct ID of your subscription, the name of the resource group containing your key vault, and your key vault name.

      [CmdletBinding()]
  param(
      [Parameter(Mandatory = $true)]
      [ValidateNotNullOrEmpty()]
      [string]$SubscriptionId,

      [Parameter(Mandatory = $true)]
      [ValidateNotNullOrEmpty()]
      [string]$ResourceGroupName,

      [Parameter(Mandatory = $true)]
      [ValidateNotNullOrEmpty()]
      [string]$KeyVaultName,

      [Parameter(Mandatory = $true)]
      [ValidateNotNullOrEmpty()]
      [string]$KeyVaultKeyName,

      [Parameter(Mandatory = $false)]
      [ValidateSet('RSA', 'RSA-HSM', 'EC', 'EC-HSM', 'oct')]
      [string]$KeyType = 'RSA',

      [Parameter(Mandatory = $false)]
      [ValidateSet('Software', 'HSM')]
      [string]$Destination = 'Software'
  )

  Set-StrictMode -Version Latest
  $ErrorActionPreference = 'Stop'

  Import-Module Az.Accounts -ErrorAction Stop
  Import-Module Az.KeyVault -ErrorAction Stop
  Import-Module Az.Resources -ErrorAction Stop

  Write-Host "[AKV] Signing in to Azure"
  Connect-AzAccount -ErrorAction Stop | Out-Null

  Write-Host "[AKV] Setting subscription context to '$SubscriptionId'"
  Set-AzContext -SubscriptionId $SubscriptionId -ErrorAction Stop | Out-Null

  Write-Host "[AKV] Validating Key Vault '$KeyVaultName' in resource group '$ResourceGroupName'"
  $vault = Get-AzKeyVault -VaultName $KeyVaultName -ResourceGroupName $ResourceGroupName -ErrorAction Stop

  Write-Host "[AKV] Checking if key '$KeyVaultKeyName' already exists"
  $existingKey = Get-AzKeyVaultKey -VaultName $KeyVaultName -Name $KeyVaultKeyName -ErrorAction SilentlyContinue

  if ($existingKey) {
      Write-Host "[AKV] Key already exists. Returning existing key metadata."
      $existingKey
      return
  }

  Write-Host "[AKV] Creating key '$KeyVaultKeyName' (Type=$KeyType, Destination=$Destination)"
  $keyVaultKey = Add-AzKeyVaultKey `
      -VaultName $KeyVaultName `
      -Name $KeyVaultKeyName `
      -Destination $Destination `
      -KeyType $KeyType `
      -ErrorAction Stop

  Write-Host '[AKV] Key created successfully.'
  $keyVaultKey

    • If you're using Windows certificate store, execute the below commands to create a certificate in your Current User store.

      $cert = New-SelfSignedCertificate -Subject "HRCMK" -CertStoreLocation Cert:CurrentUser\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage DataEncipherment -KeySpec KeyExchange

  2. Connect to your database by using the SqlServer PowerShell module. Make sure you provide a valid connection string for your database. Create a column master key metadata object that references the physical column master key you created in your key store.

    • If you're using Azure Key Vault, execute the below commands.

      Write-Host "[AE] Connecting to Azure SQL and creating metadata"
$keyVaultAccessToken = (Get-AzAccessToken -ResourceUrl "https://vault.azure.net").Token

$serverName = "<server name>.database.windows.net"
$databaseName = "ContosoHR"
$connStr = "Server=tcp:$serverName,1433;Database=$databaseName;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Interactive"

try {
    $database = Get-SqlDatabase -ConnectionString $connStr -Encrypt Mandatory -ErrorAction Stop
}
catch {
    Write-Error "Failed to connect. Verify server name, database name, Azure SQL firewall access, and Microsoft Entra permissions."
    throw
}

$cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyURL $keyVaultKey.Key.Kid

$existingCmk = Get-SqlColumnMasterKey -InputObject $database | Where-Object { $_.Name -eq $CmkName }
if (-not $existingCmk) {
    New-SqlColumnMasterKey -Name $CmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings | Out-Null
}
      • If you're using Windows certificate store, run the following commands.
      Write-Host "[AE] Connecting to Azure SQL and creating metadata"
$serverName = "<server name>.database.windows.net"
$databaseName = "ContosoHR"
$connStr = "Server=tcp:$serverName,1433;Database=$databaseName;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Interactive"

try {
    $database = Get-SqlDatabase -ConnectionString $connStr -Encrypt Mandatory -ErrorAction Stop
}
catch {
    Write-Error "Failed to connect. Verify server name, database name, Azure SQL firewall access, and Microsoft Entra permissions."
    throw
}

$cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation "CurrentUser" -Thumbprint $cert.Thumbprint

$existingCmk = Get-SqlColumnMasterKey -InputObject $database | Where-Object { $_.Name -eq $CmkName }
if (-not $existingCmk) {
    New-SqlColumnMasterKey -Name $CmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings | Out-Null
}

  3. Generate a column encryption key, encrypt it with the column master key you've created, and create a column encryption key metadata object in the database.

    $existingCek = Get-SqlColumnEncryptionKey -InputObject $database | Where-Object { $_.Name -eq $CekName }
if (-not $existingCek) {
	New-SqlColumnEncryptionKey -Name $CekName -InputObject $database -ColumnMasterKey $CmkName -KeyVaultAccessToken $keyVaultAccessToken | Out-Null
}

  4. Encrypt SSN and Salary columns in the Employees Table. Choose deterministic encryption for the SSN column and randomized encryption for the Salary column. Deterministic encryption supports queries, such as point lookup searches that involve equality comparisons on encrypted columns. Randomized encryption doesn't support any computations on encrypted columns.

    # Encrypt the SSN and Salary columns 
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.SSN" -EncryptionType "Deterministic" -EncryptionKey $cekName
$ces += New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.Salary" -EncryptionType "Randomized" -EncryptionKey $cekName
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory .

  5. (Optional) Explore the changes, you've made in your database.

    • Run the below commands to query system catalog views that contain metadata about the column master key and the column encryption key that you created.

      $query = @'
SELECT * FROM sys.column_master_keys;
SELECT * FROM sys.column_encryption_keys
SELECT * FROM sys.column_encryption_key_values
'@

Invoke-SqlCmd -ConnectionString $connectionString -Query $query

    • Run the below commands to query sys.columns to retrieve column-level encryption metadata for the two encrypted columns.

      $query = @'
SELECT
[name]
, [encryption_type]
, [encryption_type_desc]
, [encryption_algorithm_name]
, [column_encryption_key_id]
FROM sys.columns
WHERE [encryption_type] IS NOT NULL;
'@

Invoke-SqlCmd -ConnectionString $connectionString -Query $query

Step 3: Query encrypted columns

SSMS

  1. Connect to your database with Always Encrypted disabled for your connection.

    1. Open a new query window.
    2. Right-click anywhere in the query window and select Connection > Change Connection. This will open the Connect to Database Engine dialog.
    3. Select Options <<. This will show additional tabs in the Connect to Database Engine dialog.
    4. Select the Always Encrypted tab.
    5. Make sure Enable Always Encrypted (column encryption) isn't selected.
    6. Select Connect.

    :::image type="content" source="media/always-encrypted-database-engine/always-encrypted-ssms-connect-disabled.png" alt-text="Screenshot of the SSMS connection option for Always Encrypted disabled.":::

  2. Paste in and execute the following query. The query should return binary encrypted data.

    SELECT [SSN], [Salary] FROM [HR].[Employees]

    :::image type="content" source="media/always-encrypted-database-engine/always-encrypted-ssms-retrieving-ciphertext.png" alt-text="Screenshot of cipher text results from encrypted columns.":::

  3. Connect to your database with Always Encrypted enabled for your connection.

    1. Right-click anywhere in the query window and select Connection > Change Connection. This will open the Connect to Database Engine dialog.
    2. Select Options <<. This will show additional tabs in the Connect to Database Engine dialog.
    3. Select the Always Encrypted tab.
    4. Select Enable Always Encrypted (column encryption).
    5. Select Connect.

    :::image type="content" source="media/always-encrypted-database-engine/always-encrypted-ssms-connect-enabled.png" alt-text="Screenshot of the SSMS connection option for Always Encrypted enabled.":::

  4. Rerun the same query. Since you're connected with Always Encrypted enabled for your database connection, the client driver in SSMS will attempt to decrypt data stored in both encrypted columns. If you use Azure Key Vault, you may be prompted to sign into Azure.

    :::image type="content" source="media/always-encrypted-database-engine/always-encrypted-ssms-retrieving-plaintext.png" alt-text="Screenshot of plaintext results from encrypted columns.":::

  5. Enable Parameterization for Always Encrypted. This feature allows you to run queries that filter data by encrypted columns (or insert data to encrypted columns).

    1. Select Query from the main menu of SSMS.
    2. Select Query Options....
    3. Navigate to Execution > Advanced.
    4. Make sure Enable Parameterization for Always Encrypted is checked.
    5. Select OK.

    :::image type="content" source="media/always-encrypted-database-engine/always-encrypted-ssms-enable-parameterization-query.png" alt-text="Screenshot enabling parameterization in an existing query window.":::

  6. Paste in and execute the below query, which filters data by the encrypted SSN column. The query should return one row containing plaintext values.

    DECLARE @SSN [char](11) = '795-73-9838'
SELECT [SSN], [Salary] FROM [HR].[Employees]
WHERE [SSN] = @SSN

  7. Optionally, if you're using Azure Key Vault configured with the access policy permissions model, follow the below steps to see what happens when a user tries to retrieve plaintext data from encrypted columns without having access to the column master key protecting the data.

    1. Remove the key unwrap permission for yourself in the access policy for your key vault. For more information, see Assign a Key Vault access policy.
    2. Since the client driver in SSMS caches the column encryption keys acquired from a key vault for 2 hours, close SSMS and open it again. This will ensure the key cache is empty.
    3. Connect to your database with Always Encrypted enabled for your connection.
    4. Paste in and execute the following query. The query should fail with the error message indicating you're missing the required unwrap permission.
    SELECT [SSN], [Salary] FROM [HR].[Employees]

PowerShell

  1. Connect to your database with Always Encrypted disabled and run a query to read data from encrypted columns. The query should return encrypted data as binary arrays.

    $query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
Invoke-SqlCmd -ConnectionString $connectionString -Query $query

  2. Connect to your database with Always Encrypted enabled and run a query to read data from encrypted columns. Since you have access to the column master key protecting your encrypted columns, the query should return plaintext data.

    $query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
Invoke-SqlCmd -ConnectionString "$connectionString; Column Encryption Setting = Enabled" -Query $query

Note

Invoke-SqlCmd doesn't support queries that can filter by or insert data to encrypted columns. Such queries need to be parameterized, and Invoke-SqlCmd doesn't support parameterized queries.

Next steps

See also