您现在访问的是微软AZURE全球版技术文档网站,若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站,请访问 https://docs.azure.cn.

Always Encrypted:保护敏感数据并将加密密钥存储在 Azure Key Vault 中Always Encrypted: Protect sensitive data and store encryption keys in Azure Key Vault

本文演示如何使用 SQL Server Management Studio (SSMS) 中的始终加密向导,通过数据加密来保护 SQL 数据库中的敏感数据。This article shows you how to secure sensitive data in a SQL database with data encryption using the Always Encrypted Wizard in SQL Server Management Studio (SSMS). 它还包括如何将每个加密密钥存储在 Azure 密钥保管库的说明。It also includes instructions that will show you how to store each encryption key in Azure Key Vault.

始终加密是 Azure SQL 数据库和 SQL Server 中一项新的数据加密技术,用于保护服务器上的敏感静态数据、在客户端和服务器之间进行移动的敏感数据,以及正在使用中的数据。Always Encrypted is a new data encryption technology in Azure SQL Database and SQL Server that helps protect sensitive data at rest on the server, during movement between client and server, and while the data is in use. 始终加密可以确保敏感数据永远不会在数据库系统中以明文形式显示。Always Encrypted ensures that sensitive data never appears as plaintext inside the database system. 配置数据加密之后,仅客户端应用程序或应用服务器(具有密钥访问权限)能够访问明文数据。After you configure data encryption, only client applications or app servers that have access to the keys can access plaintext data. 有关详细信息,请参阅始终加密(数据库引擎)For detailed information, see Always Encrypted (Database Engine).

将数据库配置为使用始终加密后,将通过 Visual Studio 在 C# 中创建一个客户端应用程序,以便处理加密的数据。After you configure the database to use Always Encrypted, you will create a client application in C# with Visual Studio to work with the encrypted data.

按本文所述步骤进行操作,了解如何为 Azure SQL 数据库设置始终加密。Follow the steps in this article and learn how to set up Always Encrypted for an Azure SQL database. 在本文中,学习如何执行以下任务:In this article you will learn how to perform the following tasks:

先决条件Prerequisites

备注

本文进行了更新,以便使用新的 Azure PowerShell Az 模块。This article has been updated to use the new Azure PowerShell Az module. 你仍然可以使用 AzureRM 模块,至少在 2020 年 12 月之前,它将继续接收 bug 修补程序。You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. 若要详细了解新的 Az 模块和 AzureRM 兼容性,请参阅新 Azure Powershell Az 模块简介To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. 有关 Az 模块安装说明,请参阅安装 Azure PowerShellFor Az module installation instructions, see Install Azure PowerShell.

重要

PowerShell Azure 资源管理器模块仍受 Azure SQL 数据库的支持,但所有未来的开发都是针对 Az.Sql 模块的。The PowerShell Azure Resource Manager module is still supported by Azure SQL Database, but all future development is for the Az.Sql module. 若要了解这些 cmdlet,请参阅 AzureRM.SqlFor these cmdlets, see AzureRM.Sql. Az 模块和 AzureRm 模块中的命令参数大体上是相同的。The arguments for the commands in the Az module and in the AzureRm modules are substantially identical.

在本教程中,需要:For this tutorial, you'll need:

使客户端应用程序可以访问 SQL 数据库服务Enable your client application to access the SQL Database service

首先必须通过设置 Azure Active Directory (AAD) 应用程序并复制对应用程序进行身份验证所需的应用程序 ID 和密钥,使客户端应用程序可以访问 SQL 数据库服务。You must enable your client application to access the SQL Database service by setting up an Azure Active Directory (AAD) application and copying the Application ID and key that you will need to authenticate your application.

若要获取应用程序 ID 和密钥,请按照创建可访问资源的 Azure Active Directory 应用程序和服务主体中的步骤进行操作。To get the Application ID and key, follow the steps in create an Azure Active Directory application and service principal that can access resources.

创建密钥保管库以存储密钥Create a key vault to store your keys

至此,已配置客户端应用并且已拥有应用程序 ID,接下来,可以创建密钥保管库并配置其访问策略,以便你和你的应用程序可以访问保管库的机密(Always Encrypted 密钥)。Now that your client app is configured and you have your application ID, it's time to create a key vault and configure its access policy so you and your application can access the vault's secrets (the Always Encrypted keys). creategetlistsignverifywrapKeyunwrapKey 权限是用于创建新的列主密钥以及通过 SQL Server Management Studio 设置加密所必需的。The create, get, list, sign, verify, wrapKey, and unwrapKey permissions are required for creating a new column master key and for setting up encryption with SQL Server Management Studio.

通过运行以下脚本,可以快速创建密钥保管库。You can quickly create a key vault by running the following script. 有关这些 cmdlet 的详细说明以及有关创建和配置密钥保管库的详细信息,请参阅什么是 Azure 密钥保管库For a detailed explanation of these cmdlets and more information about creating and configuring a key vault, see What is Azure Key Vault?.

    $subscriptionName = '<your Azure subscription name>'
    $userPrincipalName = '<username@domain.com>'
    $applicationId = '<application ID from your AAD application>'
    $resourceGroupName = '<resource group name>'
    # Use the same resource group name when creating your SQL Database below
    $location = '<datacenter location>'
    $vaultName = 'AeKeyVault'


    Connect-AzAccount
    $subscriptionId = (Get-AzSubscription -SubscriptionName $subscriptionName).Id
    Set-AzContext -SubscriptionId $subscriptionId

    New-AzResourceGroup -Name $resourceGroupName -Location $location
    New-AzKeyVault -VaultName $vaultName -ResourceGroupName $resourceGroupName -Location $location

    Set-AzKeyVaultAccessPolicy -VaultName $vaultName -ResourceGroupName $resourceGroupName -PermissionsToKeys create,get,wrapKey,unwrapKey,sign,verify,list -UserPrincipalName $userPrincipalName
    Set-AzKeyVaultAccessPolicy  -VaultName $vaultName  -ResourceGroupName $resourceGroupName -ServicePrincipalName $applicationId -PermissionsToKeys get,wrapKey,unwrapKey,sign,verify,list

创建空的 SQL 数据库Create a blank SQL database

  1. 登录到 Azure 门户Sign in to the Azure portal.

  2. 转到“创建资源” > “数据库” > “SQL 数据库”。Go to Create a resource > Databases > SQL Database.

  3. 在新服务器或现有服务器上创建名为 Clinic数据库。Create a Blank database named Clinic on a new or existing server. 若要深入了解如何在 Azure 门户中创建数据库,请参阅 Azure SQL 数据库For detailed directions about how to create a database in the Azure portal, see Your first Azure SQL database.

    创建空数据库

在本教程中稍后将需要连接字符串,因此在创建数据库后,浏览到新的 Clinic 数据库并复制连接字符串。You will need the connection string later in the tutorial, so after you create the database, browse to the new Clinic database and copy the connection string. 可以在任何时候获取连接字符串,但很容易将其复制到 Azure 门户。You can get the connection string at any time, but it's easy to copy it in the Azure portal.

  1. 转到“SQL 数据库” > “Clinic” > “显示数据库连接字符串”。Go to SQL databases > Clinic > Show database connection strings.

  2. 复制 ADO.NET 的连接字符串。Copy the connection string for ADO.NET.

    复制连接字符串

使用 SSMS 连接到数据库Connect to the database with SSMS

打开 SSMS,连接到包含 Clinic 数据库的服务器。Open SSMS and connect to the server with the Clinic database.

  1. 打开 SSMS。Open SSMS. (转到“连接” > “数据库引擎”以打开“连接到服务器”窗口)(如果它未打开。)(Go to Connect > Database Engine to open the Connect to Server window if it isn't open.)

  2. 输入服务器名称和凭据。Enter your server name and credentials. 服务器名称可以在 SQL 数据库边栏选项卡以及此前复制的连接字符串中找到。The server name can be found on the SQL database blade and in the connection string you copied earlier. 键入完整的服务器名称,包括 database.windows.netType the complete server name, including database.windows.net.

    复制连接字符串

如果“新建防火墙规则”窗口打开,请登录到 Azure,让 SSMS 创建新的防火墙规则。If the New Firewall Rule window opens, sign in to Azure and let SSMS create a new firewall rule for you.

创建表Create a table

在本部分中,将创建一个表以保存患者数据。In this section, you will create a table to hold patient data. 它最初尚未加密 - 可在下一部分配置加密。It's not initially encrypted--you will configure encryption in the next section.

  1. 展开“数据库”。Expand Databases.
  2. 右键单击“Clinic”数据库,并单击“新建查询”。Right-click the Clinic database and click New Query.
  3. 将以下 Transact-SQL (T-SQL) 粘贴到新查询窗口中,然后“执行”它。Paste the following Transact-SQL (T-SQL) into the new query window and Execute it.
        CREATE TABLE [dbo].[Patients](
         [PatientId] [int] IDENTITY(1,1),
         [SSN] [char](11) NOT NULL,
         [FirstName] [nvarchar](50) NULL,
         [LastName] [nvarchar](50) NULL,
         [MiddleName] [nvarchar](50) NULL,
         [StreetAddress] [nvarchar](50) NULL,
         [City] [nvarchar](50) NULL,
         [ZipCode] [char](5) NULL,
         [State] [char](2) NULL,
         [BirthDate] [date] NOT NULL
         PRIMARY KEY CLUSTERED ([PatientId] ASC) ON [PRIMARY] );
         GO

加密列(配置始终加密)Encrypt columns (configure Always Encrypted)

SSMS 提供了一个向导,通过设置列主密钥、列加密密钥和已加密列即可轻松地配置始终加密。SSMS provides a wizard that helps you easily configure Always Encrypted by setting up the column master key, column encryption key, and encrypted columns for you.

  1. 展开“数据库” > “Clinic” > “表”。Expand Databases > Clinic > Tables.

  2. 右键单击“患者”表,并选择“加密列”以打开始终加密向导:Right-click the Patients table and select Encrypt Columns to open the Always Encrypted wizard:

    加密列

Always Encrypted 向导包括以下部分:列选择主密钥配置验证摘要The Always Encrypted wizard includes the following sections: Column Selection, Master Key Configuration, Validation, and Summary.

列选择Column Selection

单击“简介”页上的“下一步”,可以打开“列选择”页。Click Next on the Introduction page to open the Column Selection page. 在此页上,选择想要加密的列,加密类型和要使用的列加密密钥 (CEK)On this page, you will select which columns you want to encrypt, the type of encryption, and what column encryption key (CEK) to use.

加密每位患者的“SSN”和“出生日期”信息。Encrypt SSN and BirthDate information for each patient. SSN 列将使用确定性加密,该加密支持相等性查找、联接和分组方式。The SSN column will use deterministic encryption, which supports equality lookups, joins, and group by. BirthDate 列将使用随机加密,该加密不支持操作。The BirthDate column will use randomized encryption, which does not support operations.

将 SSN 列的“加密类型”设置为“确定”,并将 BirthDate 列设置为“随机”。Set the Encryption Type for the SSN column to Deterministic and the BirthDate column to Randomized. 单击“下一步”。Click Next.

加密列

主密钥配置Master Key Configuration

主密钥配置页是设置 CMK 和选择密钥存储提供程序(在其中存储 CMK)的地方。The Master Key Configuration page is where you set up your CMK and select the key store provider where the CMK will be stored. 目前,可以将 CMK 存储在 Windows 证书存储、Azure 密钥保管库或硬件安全模块 (HSM) 中。Currently, you can store a CMK in the Windows certificate store, Azure Key Vault, or a hardware security module (HSM).

本教程演示如何将密钥存储在 Azure 密钥保管库中。This tutorial shows how to store your keys in Azure Key Vault.

  1. 选择“Azure 密钥保管库”。Select Azure Key Vault.
  2. 从下拉列表中选择所需密钥保管库。Select the desired key vault from the drop-down list.
  3. 单击“下一步”。Click Next.

主密钥配置

验证Validation

可以现在就加密这些列,也可以保存 PowerShell 脚本供以后运行。You can encrypt the columns now or save a PowerShell script to run later. 对于本教程,请选择“现在完成”,并单击“下一步”。For this tutorial, select Proceed to finish now and click Next.

总结Summary

验证设置是否全都正确,并单击“完成”以完成“始终加密”的设置。Verify that the settings are all correct and click Finish to complete the setup for Always Encrypted.

总结

验证向导的操作Verify the wizard's actions

向导完成后,数据库就会设置为始终加密。After the wizard is finished, your database is set up for Always Encrypted. 该向导执行以下操作:The wizard performed the following actions:

  • 创建列主密钥并将其存储在 Azure 密钥保管库中。Created a column master key and stored it in Azure Key Vault.
  • 创建列加密密钥并将其存储在 Azure 密钥保管库中。Created a column encryption key and stored it in Azure Key Vault.
  • 配置了所选列的加密。Configured the selected columns for encryption. “患者”表目前尚无数据,但所选列中的任何现有数据都会进行加密。The Patients table currently has no data, but any existing data in the selected columns is now encrypted.

可以验证 SSMS 中密钥的创建,只需展开“Clinic” > “安全” > “始终加密密钥”即可。You can verify the creation of the keys in SSMS by expanding Clinic > Security > Always Encrypted Keys.

创建处理已加密数据的客户端应用程序Create a client application that works with the encrypted data

现在已设置始终加密,可以生成一个应用程序,用其在已加密列上执行某些 inserts 操作和 selects 操作。Now that Always Encrypted is set up, you can build an application that performs inserts and selects on the encrypted columns.

重要

通过始终加密列将明文数据传递到服务器时,应用程序必须使用 SqlParameter 对象。Your application must use SqlParameter objects when passing plaintext data to the server with Always Encrypted columns. 在不使用 SqlParameter 对象的情况下传递文本值会导致异常。Passing literal values without using SqlParameter objects will result in an exception.

  1. 打开 Visual Studio 并创建新的 C# 控制台应用程序(Visual Studio 2015 和更低版本)或控制台应用 (.NET Framework) (Visual Studio 2017 和更高版本)。Open Visual Studio and create a new C# Console Application (Visual Studio 2015 and earlier) or Console App (.NET Framework) (Visual Studio 2017 and later). 确保将项目设置为 .NET Framework 4.6 或更高版本。Make sure your project is set to .NET Framework 4.6 or later.
  2. 将项目命名为 AlwaysEncryptedConsoleAKVApp,然后单击“确定”。Name the project AlwaysEncryptedConsoleAKVApp and click OK.
  3. 通过转到“工具” > “NuGet 包管理器” > “包管理器控制台”来安装以下 NuGet 包。Install the following NuGet packages by going to Tools > NuGet Package Manager > Package Manager Console.

在包管理器控制台中运行以下 2 行代码。Run these two lines of code in the Package Manager Console.

    Install-Package Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider
    Install-Package Microsoft.IdentityModel.Clients.ActiveDirectory

修改连接字符串以启用始终加密Modify your connection string to enable Always Encrypted

本节介绍如何在数据库连接字符串中启用始终加密。This section explains how to enable Always Encrypted in your database connection string.

要启用“始终加密”,需要将“列加密设置”关键字添加到连接字符串中,并将其设置为“启用”。To enable Always Encrypted, you need to add the Column Encryption Setting keyword to your connection string and set it to Enabled.

可以在连接字符串中直接进行该设置,也可以使用 SqlConnectionStringBuilder 进行设置。You can set this directly in the connection string, or you can set it by using SqlConnectionStringBuilder. 下一节中的示例应用程序演示如何使用 SqlConnectionStringBuilderThe sample application in the next section shows how to use SqlConnectionStringBuilder.

在连接字符串中启用始终加密Enable Always Encrypted in the connection string

将以下关键字添加到连接字符串中。Add the following keyword to your connection string.

Column Encryption Setting=Enabled

通过 SqlConnectionStringBuilder 启用始终加密Enable Always Encrypted with SqlConnectionStringBuilder

以下代码显示了如何通过将 SqlConnectionStringBuilder.ColumnEncryptionSetting 设置为启用来启用“始终加密”。The following code shows how to enable Always Encrypted by setting SqlConnectionStringBuilder.ColumnEncryptionSetting to Enabled.

    // Instantiate a SqlConnectionStringBuilder.
    SqlConnectionStringBuilder connStringBuilder =
       new SqlConnectionStringBuilder("replace with your connection string");

    // Enable Always Encrypted.
    connStringBuilder.ColumnEncryptionSetting =
       SqlConnectionColumnEncryptionSetting.Enabled;

注册 Azure 密钥保管库提供程序Register the Azure Key Vault provider

下面的代码演示如何使用 ADO.NET 驱动程序注册 Azure 密钥保管库提供程序。The following code shows how to register the Azure Key Vault provider with the ADO.NET driver.

    private static ClientCredential _clientCredential;

    static void InitializeAzureKeyVaultProvider()
    {
       _clientCredential = new ClientCredential(applicationId, clientKey);

       SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider =
          new SqlColumnEncryptionAzureKeyVaultProvider(GetToken);

       Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providers =
          new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();

       providers.Add(SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider);
       SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);
    }

始终加密示例控制台应用程序Always Encrypted sample console application

此示例演示了如何执行以下操作:This sample demonstrates how to:

  • 修改连接字符串以启用始终加密。Modify your connection string to enable Always Encrypted.
  • 将 Azure 密钥保管库注册为应用程序的密钥存储提供程序。Register Azure Key Vault as the application's key store provider.
  • 将数据插入已加密列。Insert data into the encrypted columns.
  • 通过在已加密列中筛选出特定的值来选择记录。Select a record by filtering for a specific value in an encrypted column.

Program.cs 的内容替换为以下代码。Replace the contents of Program.cs with the following code. 将 Main 方法前一行中全局 connectionString 变量的连接字符串替换为 Azure 门户中的有效连接字符串。Replace the connection string for the global connectionString variable in the line that directly precedes the Main method with your valid connection string from the Azure portal. 这是需要对此代码进行的唯一更改。This is the only change you need to make to this code.

运行该应用以在操作中查看始终加密。Run the app to see Always Encrypted in action.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Data;
    using System.Data.SqlClient;
    using Microsoft.IdentityModel.Clients.ActiveDirectory;
    using Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider;

    namespace AlwaysEncryptedConsoleAKVApp
    {
    class Program
    {
        // Update this line with your Clinic database connection string from the Azure portal.
        static string connectionString = @"<connection string from the portal>";
        static string applicationId = @"<application ID from your AAD application>";
        static string clientKey = "<key from your AAD application>";


        static void Main(string[] args)
        {
            InitializeAzureKeyVaultProvider();

            Console.WriteLine("Signed in as: " + _clientCredential.ClientId);

            Console.WriteLine("Original connection string copied from the Azure portal:");
            Console.WriteLine(connectionString);

            // Create a SqlConnectionStringBuilder.
            SqlConnectionStringBuilder connStringBuilder =
                new SqlConnectionStringBuilder(connectionString);

            // Enable Always Encrypted for the connection.
            // This is the only change specific to Always Encrypted
            connStringBuilder.ColumnEncryptionSetting =
                SqlConnectionColumnEncryptionSetting.Enabled;

            Console.WriteLine(Environment.NewLine + "Updated connection string with Always Encrypted enabled:");
            Console.WriteLine(connStringBuilder.ConnectionString);

            // Update the connection string with a password supplied at runtime.
            Console.WriteLine(Environment.NewLine + "Enter server password:");
            connStringBuilder.Password = Console.ReadLine();


            // Assign the updated connection string to our global variable.
            connectionString = connStringBuilder.ConnectionString;


            // Delete all records to restart this demo app.
            ResetPatientsTable();

            // Add sample data to the Patients table.
            Console.Write(Environment.NewLine + "Adding sample patient data to the database...");

            InsertPatient(new Patient()
            {
                SSN = "999-99-0001",
                FirstName = "Orlando",
                LastName = "Gee",
                BirthDate = DateTime.Parse("01/04/1964")
            });
            InsertPatient(new Patient()
            {
                SSN = "999-99-0002",
                FirstName = "Keith",
                LastName = "Harris",
                BirthDate = DateTime.Parse("06/20/1977")
            });
            InsertPatient(new Patient()
            {
                SSN = "999-99-0003",
                FirstName = "Donna",
                LastName = "Carreras",
                BirthDate = DateTime.Parse("02/09/1973")
            });
            InsertPatient(new Patient()
            {
                SSN = "999-99-0004",
                FirstName = "Janet",
                LastName = "Gates",
                BirthDate = DateTime.Parse("08/31/1985")
            });
            InsertPatient(new Patient()
            {
                SSN = "999-99-0005",
                FirstName = "Lucy",
                LastName = "Harrington",
                BirthDate = DateTime.Parse("05/06/1993")
            });


            // Fetch and display all patients.
            Console.WriteLine(Environment.NewLine + "All the records currently in the Patients table:");

            foreach (Patient patient in SelectAllPatients())
            {
                Console.WriteLine(patient.FirstName + " " + patient.LastName + "\tSSN: " + patient.SSN + "\tBirthdate: " + patient.BirthDate);
            }

            // Get patients by SSN.
            Console.WriteLine(Environment.NewLine + "Now lets locate records by searching the encrypted SSN column.");

            string ssn;

            // This very simple validation only checks that the user entered 11 characters.
            // In production be sure to check all user input and use the best validation for your specific application.
            do
            {
                Console.WriteLine("Please enter a valid SSN (ex. 999-99-0003):");
                ssn = Console.ReadLine();
            } while (ssn.Length != 11);

            // The example allows duplicate SSN entries so we will return all records
            // that match the provided value and store the results in selectedPatients.
            Patient selectedPatient = SelectPatientBySSN(ssn);

            // Check if any records were returned and display our query results.
            if (selectedPatient != null)
            {
                Console.WriteLine("Patient found with SSN = " + ssn);
                Console.WriteLine(selectedPatient.FirstName + " " + selectedPatient.LastName + "\tSSN: "
                    + selectedPatient.SSN + "\tBirthdate: " + selectedPatient.BirthDate);
            }
            else
            {
                Console.WriteLine("No patients found with SSN = " + ssn);
            }

            Console.WriteLine("Press Enter to exit...");
            Console.ReadLine();
        }


        private static ClientCredential _clientCredential;

        static void InitializeAzureKeyVaultProvider()
        {

            _clientCredential = new ClientCredential(applicationId, clientKey);

            SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider =
              new SqlColumnEncryptionAzureKeyVaultProvider(GetToken);

            Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providers =
              new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();

            providers.Add(SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider);
            SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);
        }

        public async static Task<string> GetToken(string authority, string resource, string scope)
        {
            var authContext = new AuthenticationContext(authority);
            AuthenticationResult result = await authContext.AcquireTokenAsync(resource, _clientCredential);

            if (result == null)
                throw new InvalidOperationException("Failed to obtain the access token");
            return result.AccessToken;
        }

        static int InsertPatient(Patient newPatient)
        {
            int returnValue = 0;

            string sqlCmdText = @"INSERT INTO [dbo].[Patients] ([SSN], [FirstName], [LastName], [BirthDate])
     VALUES (@SSN, @FirstName, @LastName, @BirthDate);";

            SqlCommand sqlCmd = new SqlCommand(sqlCmdText);


            SqlParameter paramSSN = new SqlParameter(@"@SSN", newPatient.SSN);
            paramSSN.DbType = DbType.AnsiStringFixedLength;
            paramSSN.Direction = ParameterDirection.Input;
            paramSSN.Size = 11;

            SqlParameter paramFirstName = new SqlParameter(@"@FirstName", newPatient.FirstName);
            paramFirstName.DbType = DbType.String;
            paramFirstName.Direction = ParameterDirection.Input;

            SqlParameter paramLastName = new SqlParameter(@"@LastName", newPatient.LastName);
            paramLastName.DbType = DbType.String;
            paramLastName.Direction = ParameterDirection.Input;

            SqlParameter paramBirthDate = new SqlParameter(@"@BirthDate", newPatient.BirthDate);
            paramBirthDate.SqlDbType = SqlDbType.Date;
            paramBirthDate.Direction = ParameterDirection.Input;

            sqlCmd.Parameters.Add(paramSSN);
            sqlCmd.Parameters.Add(paramFirstName);
            sqlCmd.Parameters.Add(paramLastName);
            sqlCmd.Parameters.Add(paramBirthDate);

            using (sqlCmd.Connection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlCmd.Connection.Open();
                    sqlCmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    returnValue = 1;
                    Console.WriteLine("The following error was encountered: ");
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(Environment.NewLine + "Press Enter key to exit");
                    Console.ReadLine();
                    Environment.Exit(0);
                }
            }
            return returnValue;
        }


        static List<Patient> SelectAllPatients()
        {
            List<Patient> patients = new List<Patient>();


            SqlCommand sqlCmd = new SqlCommand(
              "SELECT [SSN], [FirstName], [LastName], [BirthDate] FROM [dbo].[Patients]",
                new SqlConnection(connectionString));


            using (sqlCmd.Connection = new SqlConnection(connectionString))

            using (sqlCmd.Connection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlCmd.Connection.Open();
                    SqlDataReader reader = sqlCmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            patients.Add(new Patient()
                            {
                                SSN = reader[0].ToString(),
                                FirstName = reader[1].ToString(),
                                LastName = reader["LastName"].ToString(),
                                BirthDate = (DateTime)reader["BirthDate"]
                            });
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw;
                }
            }

            return patients;
        }


        static Patient SelectPatientBySSN(string ssn)
        {
            Patient patient = new Patient();

            SqlCommand sqlCmd = new SqlCommand(
                "SELECT [SSN], [FirstName], [LastName], [BirthDate] FROM [dbo].[Patients] WHERE [SSN]=@SSN",
                new SqlConnection(connectionString));

            SqlParameter paramSSN = new SqlParameter(@"@SSN", ssn);
            paramSSN.DbType = DbType.AnsiStringFixedLength;
            paramSSN.Direction = ParameterDirection.Input;
            paramSSN.Size = 11;

            sqlCmd.Parameters.Add(paramSSN);


            using (sqlCmd.Connection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlCmd.Connection.Open();
                    SqlDataReader reader = sqlCmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            patient = new Patient()
                            {
                                SSN = reader[0].ToString(),
                                FirstName = reader[1].ToString(),
                                LastName = reader["LastName"].ToString(),
                                BirthDate = (DateTime)reader["BirthDate"]
                            };
                        }
                    }
                    else
                    {
                        patient = null;
                    }
                }
                catch (Exception ex)
                {
                    throw;
                }
            }
            return patient;
        }


        // This method simply deletes all records in the Patients table to reset our demo.
        static int ResetPatientsTable()
        {
            int returnValue = 0;

            SqlCommand sqlCmd = new SqlCommand("DELETE FROM Patients");
            using (sqlCmd.Connection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlCmd.Connection.Open();
                    sqlCmd.ExecuteNonQuery();

                }
                catch (Exception ex)
                {
                    returnValue = 1;
                }
            }
            return returnValue;
        }
    }

    class Patient
    {
        public string SSN { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public DateTime BirthDate { get; set; }
    }
    }

确保数据已加密Verify that the data is encrypted

通过 SSMS 对患者数据进行查询(使用当前的连接,其中,列加密设置尚未启用),可以快速查看服务器上的实际数据是否已加密。You can quickly check that the actual data on the server is encrypted by querying the Patients data with SSMS (using your current connection where Column Encryption Setting is not yet enabled).

针对 Clinic 数据库运行以下查询。Run the following query on the Clinic database.

    SELECT FirstName, LastName, SSN, BirthDate FROM Patients;

可以看到,加密的列不包含任何明文数据。You can see that the encrypted columns do not contain any plaintext data.

新建控制台应用程序

若要使用 SSMS 访问纯文本数据,首先需要确保用户具有 Azure Key Vault 的适当权限:get、unwrapKey 和 verify。To use SSMS to access the plaintext data, you first need to ensure that the user has proper permissions to the Azure Key Vault: get, unwrapKey, and verify. 有关详细信息,请参阅创建和存储列主密钥 (Always Encrypted)For detailed information, see Create and Store Column Master Keys (Always Encrypted).

然后在连接期间添加 Column Encryption Setting=enabled 参数。Then add the Column Encryption Setting=enabled parameter during your connection.

  1. 在 SSMS 中,右键单击“对象资源管理器”中的服务器,并选择“断开连接”。In SSMS, right-click your server in Object Explorer and choose Disconnect.

  2. 单击“连接” > “数据库引擎”打开“连接到服务器”窗口,并单击“选项”。Click Connect > Database Engine to open the Connect to Server window and click Options.

  3. 单击“其他连接参数”,并键入 Column Encryption Setting=enabledClick Additional Connection Parameters and type Column Encryption Setting=enabled.

    新建控制台应用程序

  4. 针对 Clinic 数据库运行以下查询。Run the following query on the Clinic database.

       SELECT FirstName, LastName, SSN, BirthDate FROM Patients;
    

    现在,可以看到已加密列中的明文数据。You can now see the plaintext data in the encrypted columns. 新建控制台应用程序New console application

后续步骤Next steps

创建使用始终加密的数据库以后,可能需要执行以下操作:After you create a database that uses Always Encrypted, you may want to do the following: