使用 Azure Key Vault 設定 Always EncryptedConfigure Always Encrypted by using Azure Key Vault

適用於: yesAzure SQL Database yesAzure SQL 受控執行個體 APPLIES TO: yesAzure SQL Database yesAzure SQL Managed Instance

本文說明如何使用SQL Server Management Studio (SSMS)中的Always Encrypted wizard ,以資料加密來保護資料庫中的敏感性資料 Azure SQL Database。This article shows you how to secure sensitive data in a database in Azure SQL Database with data encryption by 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.

Always Encrypted 是一種資料加密技術,可在用戶端與伺服器之間移動時,以及在資料使用中時,協助保護伺服器上待用的敏感性資料。Always Encrypted is a data encryption technology 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 Database 或 SQL 受控執行個體中設定資料庫的 Always Encrypted。Follow the steps in this article and learn how to set up Always Encrypted for your database in Azure SQL Database or SQL Managed Instance. 在本文章中,您將學習到如何執行下列工作:In this article you will learn how to perform the following tasks:

PrerequisitesPrerequisites

啟用用戶端應用程式存取Enable client application access

您必須藉由設定 Azure Active Directory (Azure AD)應用程式,並複製驗證應用程式所需的應用程式識別碼金鑰,讓您的用戶端應用程式能夠在 SQL Database 中存取您的資料庫。You must enable your client application to access your database in SQL Database by setting up an Azure Active Directory (Azure AD) application and copying the Application ID and key that you will need to authenticate your application.

若要取得「應用程式識別碼」** 和「金鑰」**,請遵循建立可存取資源的 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

既然您的用戶端應用程式已完成設定,您也已取得應用程式識別碼,現在即可建立金鑰保存庫並設定其存取原則,以便讓您和您的應用程式能夠存取保存庫的密碼 (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). 若要使用 SQL Server Management Studio 來建立新的資料行主要金鑰及設定加密,必須要有 create**、get**、list**、sign**、verify**、wrapKey** 及 unwrapKey** 權限。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. 如需這些命令的詳細說明,以及有關建立及設定金鑰保存庫的詳細資訊,請參閱什麼是 Azure Key Vault?For a detailed explanation of these commands and more information about creating and configuring a key vault, see What is Azure Key Vault?.

重要

Azure SQL Database 仍然支援 PowerShell Azure Resource Manager (RM)模組,但所有未來的開發都是針對 Az .Sql 模組。The PowerShell Azure Resource Manager (RM) module is still supported by Azure SQL Database, but all future development is for the Az.Sql module. AzureRM 模組會繼續收到錯誤修正,直到2020年12月為止。The AzureRM module will continue to receive bug fixes until at least December 2020. Az 模組和 AzureRm 模組中命令的引數本質上完全相同。The arguments for the commands in the Az module and in the AzureRm modules are substantially identical. 如需其相容性的詳細資訊,請參閱新的 Azure PowerShell Az 模組簡介For more about their compatibility, see Introducing the new Azure PowerShell Az module.

$subscriptionName = '<subscriptionName>'
$userPrincipalName = '<username@domain.com>'
$applicationId = '<applicationId from AAD application>'
$resourceGroupName = '<resourceGroupName>' # use the same resource group name when creating your SQL Database below
$location = '<datacenterLocation>'
$vaultName = '<vaultName>'

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

使用 SSMS 連線Connect with SSMS

開啟 SQL Server Management Studio (SSMS),並連接到伺服器,或使用您的資料庫進行管理。Open SQL Server Management Studio (SSMS) and connect to the server or managed with your 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 or instance name and credentials.

    複製連接字串

如果 [新增防火牆規則] **** 視窗開啟,請登入 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. 以滑鼠右鍵按一下資料庫,然後按一下 [追加查詢]。Right-click the 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. 在 [Patients]**** 資料表上按一下滑鼠右鍵,然後選取 [加密資料行]**** 以開啟「一律加密精靈」:Right-click the Patients table and select Encrypt Columns to open the Always Encrypted wizard:

    加密資料行

「一律加密」精靈包含下列區段︰[資料行選取]****、主要金鑰組態****、[驗證]**** 及 [摘要]****。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.

請加密每個病患的 SSNBirthDate 資訊。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:

  • 建立資料行主要金鑰 (CMK) 並將它儲存在「Azure 金鑰保存庫」中。Created a column master key and stored it in Azure Key Vault.
  • 建立資料行加密金鑰 (CMK) 並將它儲存在「Azure 金鑰保存庫」中。Created a column encryption key and stored it in Azure Key Vault.
  • 設定選取的資料行以進行加密。Configured the selected columns for encryption. Patients 資料表目前沒有任何資料,但在所選資料行中的所有現有資料現在都已加密。The Patients table currently has no data, but any existing data in the selected columns is now encrypted.

您可以藉由展開 [ 實務] [ > 安全性] > Always Encrypted 金鑰,來確認 SSMS 中的金鑰建立。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

既然已設定好「一律加密」,您現在即可建置會在加密資料行上執行「插入」** 和「選取」** 動作的應用程式。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.

在 [套件管理員主控台] 中執行下列兩行程式碼: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.

若要啟用「一律加密」,您必須將 Column Encryption Setting 關鍵字新增到您的連接字串中,並將它設定為 EnabledTo 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 設定為 Enabled 來啟用「一律加密」。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. 從 Azure 入口網站,針對 Main 方法前一行中的全域 connectionString 變數,使用有效的連接字串來取代其連接字串。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 來查詢 Patients 資料 (使用尚未啟用「資料行加密設定」 **** 的目前連線),快速檢查伺服器上的實際資料是否已加密。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 的適當權限:getunwrapKeyverifyTo 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.

    新的主控台應用程式

後續步驟Next steps

在您的資料庫設定為使用 Always Encrypted 之後,您可能會想要執行下列動作:After your database is configured to use Always Encrypted, you may want to do the following: