Always Encrypted:保護機密資料,並將加密金鑰儲存在 Azure Key Vault 中Always Encrypted: Protect sensitive data and store encryption keys in Azure Key Vault

本文說明如何使用 SQL Server Management Studio (SSMS) 中的一律加密精靈,藉由資料加密來保護 SQL Database 中的機密資料。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 Database 和 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.

重要

Azure SQL Database,仍然支援 PowerShell 的 Azure Resource Manager 模組,但所有未來的開發是 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. 這些指令程式,請參閱 < 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 Database 服務Enable your client application to access the SQL Database service

您必須讓用戶端應用程式能夠存取 SQL Database 服務,方法是設定 Azure Active Directory (AAD) 應用程式,並複製驗證應用程式所需的「應用程式識別碼」 和「金鑰」 。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.

若要取得「應用程式識別碼」 和「金鑰」 ,請遵循建立可存取資源的 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. 如需這些 Cmdlet 的詳細說明,以及有關建立及設定金鑰保存庫的詳細資訊,請參閱什麼是 Azure Key Vault?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 DatabaseCreate a blank SQL database

  1. 登入 Azure 入口網站Sign in to the Azure portal.

  2. 移至 [建立資源] > [資料庫] > [SQL Database] 。Go to Create a resource > Databases > SQL Database.

  3. 在新的或現有伺服器上建立名稱為 Clinic (診所) 的空白資料庫。Create a Blank database named Clinic on a new or existing server. 如需有關如何在 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 Database] > [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 並連接到包含實務課程資料庫的伺服器。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. 展開 [資料庫] > 空白 > ,藉由資料庫加密來保護 SQL Database 中的機密資料。Expand Databases > Clinic > Tables.

  2. 在 [Patients] 資料表上按一下滑鼠右鍵,然後選取 [加密資料行] 以開啟「一律加密精靈」: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.

請加密每個病患的 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.

您可以確認 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

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

後續步驟Next steps

建立使用「一律加密」的資料庫之後,您可以執行下列操作:After you create a database that uses Always Encrypted, you may want to do the following: