Skip to content

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

Latest commit

 

History

History
155 lines (120 loc) · 8.36 KB

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

File metadata and controls

155 lines (120 loc) · 8.36 KB
title Tutorial: Develop a .NET Framework application using Always Encrypted with secure enclaves | Microsoft Docs
ms.custom
ms.date 10/15/2019
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 || = sqlallproducts-allversions

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

[!INCLUDE tsql-appliesto-ssver15-xxxx-xxxx-xxx-winonly]

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

Prerequisites

This tutorial is the continuation of Tutorial: Getting Started with Always Encrypted with secure enclaves using SSMS. Make sure you've completed it, before following the below steps.

In addition, you need Visual Studio (version 2019 is recommended) - you can download it from https://visualstudio.microsoft.com/. You application development machine must run .NET Framework 4.7.2 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 is built against .NET Framework 4.7.2 and is integrated with the Microsoft.SqlServer.Management.AlwaysEncrypted.EnclaveProviders NuGet. In addition, if you store you column master key in Azure Key Vault, you also need to integrate your application with the Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider NuGet, version 2.2.0 or later.

  1. Open Visual Studio.

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

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

  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.SqlServer.Management.AlwaysEncrypted.EnclaveProviders -IncludePrerelease

  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.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider -IncludePrerelease -Version 2.2.0
Install-Package Microsoft.IdentityModel.Clients.ActiveDirectory

  6. Open the App.config file for your project.

  7. Locate the <configuration> section and add or update the <configSections> sections.

    a. If the <configuration> section does not contain the <configSections> section, add the following content immediately below <configuration>.

    <configSections>
   <section name="SqlColumnEncryptionEnclaveProviders" type="System.Data.SqlClient.SqlColumnEncryptionEnclaveProviderConfigurationSection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
</configSections>

    b. If the <configruation> section already contains the <configSections> section, add the following line within the <configSections>:

    <section name="SqlColumnEncryptionEnclaveProviders"  type="System.Data.SqlClient.SqlColumnEncryptionEnclaveProviderConfigurationSection, System.Data,  Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /\>

  8. Inside the configuration section, below the <configSections>, add the following section, which specific an enclave provider to be used to attest and interact with VBS enclaves:

    <SqlColumnEncryptionEnclaveProviders>
    <providers>
        <add name="VBS"  type="Microsoft.SqlServer.Management.AlwaysEncrypted.EnclaveProviders.HostGuardianServiceEnclaveProvider,  Microsoft.SqlServer.Management.AlwaysEncrypted.EnclaveProviders,    Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
    </providers>
</SqlColumnEncryptionEnclaveProviders>

Here is a complete example of an app.config file for a simple console application.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="SqlColumnEncryptionEnclaveProviders" type="System.Data.SqlClient.SqlColumnEncryptionEnclaveProviderConfigurationSection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
  </configSections>
  <SqlColumnEncryptionEnclaveProviders>
    <providers>
      <add name="VBS"  type="Microsoft.SqlServer.Management.AlwaysEncrypted.EnclaveProviders.HostGuardianServiceEnclaveProvider,  Microsoft.SqlServer.Management.AlwaysEncrypted.EnclaveProviders,    Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
    </providers>
  </SqlColumnEncryptionEnclaveProviders>
  <startup> 
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
  </startup>
</configuration>

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 and it will run a query that contains the LIKE predicate on the SSN column and and a range comparison on the Salary column.

  1. Replace the content of the Program.cs file (generated by Visual Studio) with the below code. Update the database connection string with your server name and the an enclave attestation URL for your environment. You may also update database authentication settings.

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

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

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

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

                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = @"SELECT [SSN], [FirstName], [LastName], [Salary] FROM [dbo].[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 = "%1111";
                paramSSNPattern.Size = 11;

                cmd.Parameters.Add(paramSSNPattern);

                SqlParameter MinSalary = cmd.CreateParameter();

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

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

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

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

  2. Build and run the application.

See Also