Skip to content

tutorial-always-encrypted-enclaves-develop-net-apps.md

Latest commit

 

History

History
138 lines (98 loc) · 7.34 KB

tutorial-always-encrypted-enclaves-develop-net-apps.md

File metadata and controls

138 lines (98 loc) · 7.34 KB
description Tutorial: Develop a .NET application using Always Encrypted with secure enclaves
title Tutorial: Develop a .NET application using Always Encrypted with secure enclaves | Microsoft Docs
ms.custom
ms.date 01/15/2021
ms.reviewer v-kaywon
ms.prod sql
ms.prod_service connectivity
ms.technology connectivity
ms.tgt_pltfrm
ms.topic tutorial
author karinazhou
ms.author v-jizho2

Tutorial: Develop a .NET application using Always Encrypted with secure enclaves

[!INCLUDE sqlserver2019-windows-only-asdb]

[!INCLUDE appliesto-netfx-netcore-xxxx-md]

This tutorial teaches you how to develop an application that issues database queries that use a server-side secure enclave for Always Encrypted with secure enclaves.

Note

Always Encrypted with secure enclaves is only supported on Windows.

Prerequisites

Make sure you've completed one of the below tutorials before following the below steps in this tutorial:

In addition, you need Visual Studio (version 2019 is recommended) - you can download it from https://visualstudio.microsoft.com/. Your application development environment must use .NET Framework 4.6 or later or .NET Core 2.1 or later.

Step 1: Set up your Visual Studio Project

To use Always Encrypted with secure enclaves in a .NET Framework application, you need to make sure your application targets .NET Framework 4.6 or higher. To use Always Encrypted with secure enclaves in a .NET Core application, you need to make sure your application targets .NET Core 2.1 or higher.

In addition, if you store your column master key in Azure Key Vault, you also need to integrate your application with the Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider NuGet.

  1. Open Visual Studio.

  2. Create a new C# Console App (.NET Framework / Core) project.

  3. Make sure your project targets at least .NET Framework 4.6 or .NET Core 2.1. Right-click on the project in Solution Explorer, select Properties and set the Target framework.

  4. Install the following NuGet package by going to Tools (main menu) > NuGet Package Manager > Package Manager Console. Run the following code in the Package Manager Console.

    Install-Package Microsoft.Data.SqlClient -Version 1.1.0

  5. If you use Azure Key Vault for storing your column master keys, install the following NuGet packages by going to Tools (main menu) > NuGet Package Manager > Package Manager Console. Run the following code in the Package Manager Console.

    Install-Package Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider -Version 1.0.0
Install-Package Microsoft.IdentityModel.Clients.ActiveDirectory

Step 2: Implement your application logic

Your application will connect to the ContosoHR database from Tutorial: Getting started with Always Encrypted with secure enclaves using SSMS or - Tutorial: Getting started with Always Encrypted with secure enclaves in Azure SQL Database and it will run a query that contains the LIKE predicate on the SSN column and a range comparison on the Salary column.

  1. Replace the content of the Program.cs file (generated by Visual Studio) with the following code.

    using System;
using Microsoft.Data.SqlClient;
using System.Data;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {

            // Connection string for SQL Server
            string connectionString = "Data Source = myserver; Initial Catalog = ContosoHR; Column Encryption Setting = Enabled;Attestation Protocol = HGS; Enclave Attestation Url = http://hgs.bastion.local/Attestation; Integrated Security = true";

            // Connection string for Azure SQL Database
            //string connectionString = "Data Source = myserver.database.windows.net; Initial Catalog = ContosoHR; Column Encryption Setting = Enabled;Attestation Protocol = AAS; Enclave Attestation Url = https://myattestationprovider.uks.attest.azure.net/attest/SgxEnclave; User ID=user; Password=password";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = @"SELECT [SSN], [FirstName], [LastName], [Salary] FROM [HR].[Employees] WHERE [SSN] LIKE @SSNPattern AND [Salary] > @MinSalary;";

                SqlParameter paramSSNPattern = cmd.CreateParameter();

                paramSSNPattern.ParameterName = @"@SSNPattern";
                paramSSNPattern.DbType = DbType.AnsiStringFixedLength;
                paramSSNPattern.Direction = ParameterDirection.Input;
                paramSSNPattern.Value = "%9838";
                paramSSNPattern.Size = 11;

                cmd.Parameters.Add(paramSSNPattern);

                SqlParameter MinSalary = cmd.CreateParameter();

                MinSalary.ParameterName = @"@MinSalary";
                MinSalary.DbType = DbType.Currency;
                MinSalary.Direction = ParameterDirection.Input;
                MinSalary.Value = 20000;

                cmd.Parameters.Add(MinSalary);
                cmd.ExecuteNonQuery();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())

                {
                    Console.WriteLine(reader[0] + ", " + reader[1] + ", " + reader[2] + ", " + reader[3]);
                }
                Console.ReadKey();
            }
        }
    }
}

  2. Update the database connection string.

    1. Set the valid server name and your database authentication settings.
    2. Set the value of the Attestation Protocol keyword to:
      • HGS - if you're using [!INCLUDEssnoversion-md] and Host Guardian Service (HGS).
      • AAS - if you're using [!INCLUDEssSDSfull] and Microsoft Azure Attestation.
    3. Set Enclave Attestation URL to an attestation URL for your environment.

  3. Build and run the application.

See also