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.
ms.custom
ms.date	11/17/2022
ms.prod	sql
ms.prod_service	database-engine, sql-database
ms.reviewer	vanto
ms.suite	sql
ms.technology	security
ms.tgt_pltfrm
ms.topic	tutorial
author	jaszymas
ms.author	jaszymas
monikerRange	>= sql-server-ver15

Tutorial: Getting started with Always Encrypted

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

This tutorial teaches you how to get started with Always Encrypted. It will show you:

[!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:

Tutorial: Getting started with Always Encrypted with secure enclaves in Azure SQL Database
Tutorial: Getting started with Always Encrypted with secure enclaves in SQL Server

Prerequisites

For this tutorial, you need:

An empty database in Azure SQL Database, Azure SQL Managed Instance, or SQL Server. The below instructions assume the database name is ContosoHR. You need to be an owner of the database (a member of the db_owner role). See Quickstart: Create a single database - Azure SQL Database or Create a database in SQL Server.
Optional, but recommended, especially if your database is in Azure: a key vault in Azure Key Vault. See Quickstart: Create a key vault using the Azure portal.
The latest version of SQL Server Management Studio (SSMS) or the latest version of the SqlServer and Az PowerShell modules. (The Az module is required only if you're using Azure Key Vault.)
- If your key vault uses the access policy permissions model, make sure you have the following key permissions in the key vault: get, list, create, unwrap key, wrap key, verify, sign - see Assign a Key Vault access policy.
- If you're using the Azure role-based access control (RBAC) permission model, make unassign you sure you are a member of the Key Vault Crypto Officer role for your key vault. See Provide access to Key Vault keys, certificates, and secrets with an Azure role-based access control.

Step 1: Create and populate the database schema

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

SSMS

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 Managed Instance using SQL Server Management Studio (SSMS) or Quickstart: Connect and query a SQL Server instance using SQL Server Management Studio (SSMS).
Open a new query window for the ContosoHR database.

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];

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 use a connection string that is valid for your database.

Import-Module "SqlServer"

# Set your database connection string
$connectionString = "<your connection string>"

# Create a new table, named Employees.
$query = @'
    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];
'@
Invoke-SqlCmd -ConnectionString $connectionString -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 $connectionString -Query $query

Step 2: Encrypt columns

In this step, you will provision a column master key and a column encryption key for Always Encrypted, and then you will 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.

Expand Databases > ContosoHR > Tables.
Right-click the Employees table and select Encrypt Columns to open the Always Encrypted wizard.
Click Next on the Introduction page of the wizard.
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. Click Next.
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 and Windows certificate store. In general, Azure Key Vault is a recommended option, especially if your database is in Azure.
1. To use Azure Key Vault:
  1. Select Azure Key Vault.
  2. Click 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. Click Next.
2. 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.
3. Click Next.
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 click Next.
On the Summary page, the wizard informs you about the actions it will execute. Check all the information is correct and click Finish.
On the Results page, you can monitor the progress of wizard's operations. Wait until all operations complete successfully and click Close.
(Optional.) You can check out the metadata objects, the wizard has created for your keys, by expanding ContosoHR > Security > Always Encrypted Keys.

PowerShell

Create a column master key in your key store.

If you're using Azure Key Vault, execute the below commands to create an asymetric 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.

Import-Module Az
Connect-AzAccount
$subscriptionId = "<your Azure subscription id"
$resourceGroup = "your resource group name containing your key vault"
$keyVaultName = "your vault name"
$keyVaultKeyName = "HRCMK"

# Switch to your subscription.
$azureCtx = Set-AzConteXt -SubscriptionId $SubscriptionId

# To validate the above key vault settings, get the key vault properties.
Get-AzKeyVault -VaultName $keyVaultName -ResourceGroupName $resourceGroup 

# Create a key in the key vault.
$keyVaultKey = Add-AzKeyVaultKey -VaultName $keyVaultName -Name $keyVaultKeyName -Destination "Software"
$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

Connect to your database, using the SqlServer PowerShell module. Make sure you provide a valid connection string for your database.
```
Import-Module "SqlServer"
$database = Get-SqlDatabase -ConnectionString $connectionString
$database
```

Provision a column master key metadata object (that references the physical column master, you've created in your key store) in your database.

If you are using Azure Key Vault, execute the below commands.

# Sign in to Azure for the SqlServer PowerShell module
Add-SqlAzureAuthenticationContext -Interactive

# Create a SqlColumnMasterKeySettings in-memory object refencing the key, you've created in your key vault. 
$cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyURL $keyVaultKey.Key.Kid

# Create column master key metadata object (referencing your certificate), named CMK1, in the database.
$cmkName = "CMK1"
New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings

If you are using Windows certificate store, execute the below commands.

# Create a SqlColumnMasterKeySettings in-memory object referencing your certificate.
$cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation "CurrentUser" -Thumbprint $cert.Thumbprint

# Create column master key metadata object, named CMK1, in the database.
$cmkName = "CMK1"
New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings

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.
```
$cekName = "CEK1"
New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName
```

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.Emeployees.SSN" -EncryptionType "Deterministic" -EncryptionKey $cekName
$ces += New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.Salary" -EncryptionType "Randomized" -EncryptionKey $cekName
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory .

Step 3: Query encrypted columns

SSMS

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) is not selected.
6. Click Connect
Paste in and execute the following query. The query should return binary encrypted data.
```
SELECT [SSN], [Salary] FROM [HR].[Employees]
```
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. Click Connect.
Re-run the same query. Since you are connected with Always Encrypted enabled for your database connection, the client driver in SSMS will attempt decrypt data stored in both encrypted columns. If you use Azure Key Vault, you may be prompted to sign in to Azure.
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.
Paste in and execute the below query, which filters data by the encrypted SSN column. The query should return one row with containing plaintext values.
```
DECLARE @SSN [char](11) = '795-73-9838'
SELECT [SSN], [Salary] FROM [HR].[Employees]
WHERE [SSN] = @SSN
```
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 - 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

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
```
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
```

Next steps

Develop applications using Always Encrypted

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Tutorial: Getting started with Always Encrypted

Prerequisites

Step 1: Create and populate the database schema

SSMS

PowerShell

Step 2: Encrypt columns

SSMS

PowerShell

Step 3: Query encrypted columns

SSMS

PowerShell

Next steps

See also

FilesExpand file tree

always-encrypted-tutorial-getting-started.md

Latest commit

History

always-encrypted-tutorial-getting-started.md

File metadata and controls

Tutorial: Getting started with Always Encrypted

Prerequisites

Step 1: Create and populate the database schema

SSMS

PowerShell

Step 2: Encrypt columns

SSMS

PowerShell

Step 3: Query encrypted columns

SSMS

PowerShell

Next steps

See also