Always Encrypted:保护敏感数据并将加密密钥存储在 Windows 证书存储中Always Encrypted: Protect sensitive data and store encryption keys in the Windows certificate store

本文演示如何使用 SQL Server Management Studio (SSMS) 中的始终加密向导,通过数据加密来保护 SQL 数据库中的敏感数据。This article shows you how to secure sensitive data in a SQL database with database encryption by using the Always Encrypted Wizard in SQL Server Management Studio (SSMS). 它还演示如何将加密密钥存储在 Windows 证书存储中。It also shows you how to store your encryption keys in the Windows certificate store.

始终加密是 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, ensuring that sensitive data never appears as plaintext inside the database system. 加密数据之后,仅客户端应用程序或应用服务器(具有密钥访问权限)能够访问明文数据。After you encrypt data, 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 configuring 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 to 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

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

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

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

  2. 单击“创建资源” > “数据 + 存储” > “SQL 数据库” 。Click Create a resource > Data + Storage > SQL Database.

  3. 在新服务器或现有服务器上创建名为 Clinic数据库。Create a Blank database named Clinic on a new or existing server. 如需在 Azure 门户中创建数据库的详细说明,请参阅第一个 Azure SQL 数据库For detailed instructions about creating a database in the Azure portal, see Your first Azure SQL database.

    创建空数据库

本教程后面的步骤中会用到连接字符串。You will need the connection string later in the tutorial. 创建数据库后,转到新的 Clinic 数据库并复制连接字符串。After the database is created, go 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 when you're in the Azure portal.

  1. 单击“SQL 数据库” > “Clinic” > “显示数据库连接字符串” 。Click 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. (单击“连接” > “数据库引擎” 以打开“连接到服务器” 窗口)(如果它未打开)。(Click Connect > Database Engine to open the Connect to Server window if it is not 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.chinacloudapi.cnType the complete server name including database.chinacloudapi.cn.

    复制连接字符串

如果“新建防火墙规则” 窗口打开,请登录到 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. 这最初是一个普通表 - 可在下一部分配置加密。This will be a normal table initially--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 提供了一个向导,通过设置 CMK、CEK 和已加密列即可轻松地配置始终加密。SSMS provides a wizard to easily configure Always Encrypted by setting up the CMK, CEK, 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 向导包括以下几部分:列选择主密钥配置 (CMK)、验证摘要The Always Encrypted wizard includes the following sections: Column Selection, Master Key Configuration (CMK), 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 密钥保管库中。Currently, you can store a CMK in the Windows certificate store, or Azure Key Vault. 本教程演示如何将密钥存储在 Windows 证书存储中。This tutorial shows how to store your keys in the Windows certificate store.

验证是否选中了“Windows 证书存储” ,并单击“下一步” 。Verify that Windows certificate store is selected and 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:

  • 创建 CMK。Created a CMK.
  • 创建 CEK。Created a CEK.
  • 配置了所选列的加密。Configured the selected columns for encryption. “Patients” 表目前尚无数据,但所选列中的任何现有数据都会进行加密。Your 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 going to Clinic > Security > Always Encrypted Keys. 现在,可以看到向导生成的新密钥了。You can now see the new keys that the wizard generated for you.

创建处理已加密数据的客户端应用程序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. 若要成功运行此示例应用程序,则必须在运行过始终加密向导的计算机上运行它。To successfully run the sample application, you must run it on the same computer where you ran the Always Encrypted wizard. 要在其他计算机上运行,则必须将始终加密证书部署到运行客户端应用的计算机上。To run the application on another computer, you must deploy your Always Encrypted certificates to the computer running the client app.

重要

通过始终加密列将明文数据传递到服务器时,应用程序必须使用 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# 控制台应用程序。Open Visual Studio and create a new C# console application. 确保将项目设置为 .NET Framework 4.6 或更高版本。Make sure your project is set to .NET Framework 4.6 or later.
  2. 将项目命名为 AlwaysEncryptedConsoleApp,并单击“确定” 。Name the project AlwaysEncryptedConsoleApp and click OK.

新建控制台应用程序

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

本节介绍如何在数据库连接字符串中启用始终加密。This section explains how to enable Always Encrypted in your database connection string. 在下一节(即“始终加密示例控制台应用程序”)中,你会修改刚创建的控制台应用。You will modify the console app you just created in the next section, "Always Encrypted sample console application."

要启用“始终加密”,你需要将“列加密设置” 关键字添加到连接字符串中,并将其设置为“启用” 。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 a SqlConnectionStringBuilder. 下一节中的示例应用程序演示如何使用 SqlConnectionStringBuilderThe sample application in the next section shows how to use SqlConnectionStringBuilder.

备注

在特定于始终加密的客户端应用程序中,这是需要完成的唯一更改。This is the only change required in a client application specific to Always Encrypted. 如果某个现有应用程序将其连接字符串存储在外部(即存储在配置文件中),则可在不更改任何代码的情况下启用始终加密。If you have an existing application that stores its connection string externally (that is, in a config file), you might be able to enable Always Encrypted without changing any code.

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

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

Column Encryption Setting=Enabled

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

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

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

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

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

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

  • 修改连接字符串以启用始终加密。Modify your connection string to enable Always Encrypted.
  • 将数据插入已加密列。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 directly above 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.Data;
using System.Data.SqlClient;
using System.Globalization;

namespace AlwaysEncryptedConsoleApp
{
    class Program
    {
        // Update this line with your Clinic database connection string from the Azure portal.
        static string connectionString = @"Data Source = SPE-T640-01.sys-sqlsvr.local; Initial Catalog = Clinic; Integrated Security = true";

        static void Main(string[] args)
        {
            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...");

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


            // 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 let's 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. 123-45-6789):");
                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();
        }


        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. (使用尚未在其中启用列加密设置的当前连接。)(Use your current connection where the 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 来访问明文数据,可将 Column Encryption Setting=enabled 参数添加到连接中。To use SSMS to access the plaintext data, you can add the Column Encryption Setting=enabled parameter to the connection.

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

  2. 单击“连接” > “数据库引擎” 打开“连接到服务器” 窗口,并单击“选项” 。Click Connect > Database Engine to open the Connect to Server window, and then 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.

    新建控制台应用程序

备注

如果从其他计算机使用 SSMS(或任何客户端)进行连接,则无法访问加密密钥,并且无法解密数据。If you connect with SSMS (or any client) from a different computer, it will not have access to the encryption keys and will not be able to decrypt the data.

后续步骤Next steps

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