always-encrypted-tutorial-getting-started.md

title	Tutorial: Getting started with Always Encrypted
description	This tutorial teaches you how to create a basic environment for Always Encrypted.
ms.custom
ms.date	11/15/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.

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.
• The latest version of SQL Server Management Studio (SSMS) or the latest version of the SqlServer PowerShell module.
• 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.

Step 1: Create and populate the database schema

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

   USE [ContosoHR];
   GO
   
   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.

Import-Module "SqlServer"

# Set your database connection string
$connectionString = "Data Source = .; Initial Catalog = ContosoHRT; Integrated Security = true";

# 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
	PRIMARY KEY CLUSTERED ([EmployeeID] ASC) ON [PRIMARY] );
GO
'@
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

---