[SQL Server]Always Encrypted筆記(SQL Server 2016新功能)

2016-03-20

11712
0
SQL Server
2016-10-23

SQL Server 提供許多層次的加密功能及對稱非對稱加密演算法，從欄位的加密到整個資料庫檔案加密都有對應功能；但除非有額外的KM(鍵值管理)模組，

資料庫本身其實同時把持著解密用的"金鑰"及"加密後"的資料本身，這因此存在一種系統風險。

SQL Server 2016多了一個加密欄位的新功能Always Encrypted，讓加密工作避免過度集權在資料庫身上。

Always Encrypted:

顧名思義，在資料庫中的資料一直是加密的狀態，資料庫資料的加解密會AP端的ADO.NET 應用程式端執行，資料庫只負責接收或儲存AP端加密後的資料。

加密流程:

AP加密資料時會使用欄位加密金鑰(CEK)對資料進行對應式加密，這把CEK會在實際交易前從資料庫傳送過來，不過資料庫只有被加密的版本；接著AP端將自己憑證中的CMK取出並解開DB傳送過來的加密版CEK作為資料加解密操作金鑰，資料庫只負責提供加密後的CEK以及接收或儲存AP前端加密後的資料。

帶走整個資料庫伺服器，少了解開欄位加密金鑰(CEK)的主要金鑰(CMK)；
帶走整個應用程式伺服器，少了已加密的資料和加密後的欄位加密金鑰(CEK)。

當然如果資料庫伺服器和應用程式伺服器在同一台，就比較傷腦筋了。

*CMK:Column Master Key

*CEK:Column Encryption Key

設定步驟

1.DB Server 取得或設定可以存放"欄位加密主要金鑰(CMK:Column Master Key)"的憑證(Certifcate)。

2.DB Server 建立"欄位加密主要金鑰(CMK:Column Master Key)"，並且儲存在步驟1的憑證(Certifcate)。

3.DB Server 建立"欄位加密金鑰(CEK:Column Encryption Key)"並且用步驟2的CMK加密。

4.DB Server設定欄位使用 Always Encrypted

5.DB Server 匯出憑證、AP Server匯入憑證

6.AP Server 開始使用ADO.NET寫入資料、查資料。

7.別忘了刪除DB Server憑證。

1.取得或設定可以存放"欄位加密主要金鑰(CMK)"的憑證: 這1步可以一起和第2步CMK透過精靈產生

或是執行以下Windows SDK 內建的憑證建立工具Makecert.exe:

makecert -r -pe -n "CN=AlwaysEncryptedMasterKey" -b 01/01/2016 -e 01/01/2025 -sky exchange -ss my

上述的命令會建立自動簽名憑證、指定 "CN=AlwaysEncryptedMasterKey" 的主體名稱、指定開始2016/1/1和結束2015/1/1驗證的期間、將金鑰放置在 my 存放區、指定且交換金鑰，並讓私密金鑰可以匯出。

Makecert.exe : 通常會在C:\Program Files\Microsoft SDKs\Windows\v7.1\Bin\makecert.exe

(如果找不到Makecert.exe，可以到以下網址找適合自己的版本https://dev.windows.com/en-us/downloads/sdk-archive)

2.建立欄位加密主要金鑰(CMK:Column Master Key)

如果你有執行第1步，請先查看指紋。執行MMC

新增管理單位Certificates

檢視Thumbprint指紋，並且付複製下來

建立CMK1指令碼

USE [dbAlwaysEncrypted]
CREATE COLUMN MASTER KEY [CMK1]
WITH
(
	KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
	KEY_PATH = N'CurrentUser/My/A3ACEBC21EBB839B2C4F5C637B47E8F9497D9C70'
)
GO

或是可以用精靈:SSMS物件總管對應資料庫下Security\Always Encrypted Keys右鍵New Column Master Key 。

剛剛沒有產生憑證了話，可以按下面第二個圖下方的Generate Certificate。

執行成功後，SSMS物件總管Security\Always Encrypted Keys多出一個CMK1

3.建立欄位加密金鑰(CEK:Column Encryption Key)

精靈:SSMS物件總管對應資料庫下Security\Always Encrypted Keys右鍵New Column Encryption Key 。

(這邊如果要用指令碼會很麻煩，必須填CEK用CMK加密後的值)

輸入Column Encryption Key名稱CEK1，然後選擇CMK1加密。

4.設定特定資料表的欄位使用 Always Encrypted

這邊同時測試兩種加密類型:

固定加密DETERMINISTIC:相同的值加密結果相同。
動態加密RANDOMIZED:相同的值，不同的加密結果(這和這和二戰德軍使用的恩尼格瑪Enigma密碼機很像，遇到重複的值轉盤會再加1)

USE dbAlwaysEncrypted

CREATE TABLE [dbo].[Customers](
 [CustomerId] [int] IDENTITY(1,1), 
 [SSN] [char](11) COLLATE Latin1_General_BIN2 
 ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, 
 ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
 COLUMN_ENCRYPTION_KEY = CEK1) NOT NULL,
 [Name] [nvarchar](50) NULL,
 [BirthDate] [date] 
 ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED, 
 ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
 COLUMN_ENCRYPTION_KEY = CEK1) NOT NULL
 PRIMARY KEY CLUSTERED ([CustomerId] ASC) ON [PRIMARY] )
 GO

回到CEK1查看屬性: 下面也多了兩個欄位(SSN及BirthDate)

5.DB Server 匯出憑證，AP Server匯入憑證

A. DB Server: 透過MMC管理UI將憑證匯出(Export)

一併匯出private Key

格式不拘，選Binary Encoded就好

輸入密碼(待會AP Server匯入時要輸入的)

輸入憑證檔案名稱:

DB Server匯出CMK憑證成功:

B.將匯出的憑證複製到AP Server，然後點兩下憑證檔案，按下安裝憑證。

此時AP Server會跳出憑證匯入精靈，這邊選目前使用者。

選取剛剛複製進來的憑證檔案:

輸入剛剛DB匯出時鍵入的密碼

憑證存放區:個人

匯入成功!

6.測試資料新增及查詢:

通常以前這一筆都可以用T-SQL串Insert，但現在不行了!必須要有ADO.NET的環境。

我們用Visual Studio 2015開一個Console專案，需要特別注意目標Framework一定要4.6。

新增資料程式碼:　重點有兩個:

連線字串中的Column Encryption Setting=Enabled
一定要用Sql parameter的方式給值

using (SqlConnection conn = new SqlConnection(@"Data Source=ImDBServer;Initial Catalog=dbAlwaysEncrypted;User Id=User;Password=密碼;Column Encryption Setting=Enabled"))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand("", conn))
    {
        cmd.CommandText = @"INSERT INTO [dbo].[Customers] ([SSN], [Name], [BirthDate]) VALUES (@SSN, @Name, @BirthDate);";

        SqlParameter paramSSN = cmd.CreateParameter();
        paramSSN.ParameterName = @"@SSN";
        paramSSN.DbType = DbType.AnsiStringFixedLength;
        paramSSN.Direction = ParameterDirection.Input;
        paramSSN.Value = "00000000001";
        cmd.Parameters.Add(paramSSN);

        SqlParameter paramName = cmd.CreateParameter();
        paramName.ParameterName = @"@Name";
        paramName.DbType = DbType.AnsiStringFixedLength;
        paramName.Value = "Stanley";
        cmd.Parameters.Add(paramName);

        SqlParameter paramBirthdate = cmd.CreateParameter();
        paramBirthdate.ParameterName = @"@BirthDate";
        paramBirthdate.SqlDbType = SqlDbType.Date;
        paramBirthdate.Direction = ParameterDirection.Input;
        paramBirthdate.Value = "11-17-1979";
        cmd.Parameters.Add(paramBirthdate);

        cmd.ExecuteNonQuery();
    }
}

程式完成，準備測試，測試第一步是開啟SQL profiler錄ADO.NET傳送上的語法，然後第二步執行剛剛的Console專案:

果然SSN(社會安全號碼)及生日欄位值是已經加密後的結果：　

補上查詢資料，讀取資料程式碼:

using (SqlConnection conn = new SqlConnection(@"Data Source=ImDBServer;Initial Catalog=dbAlwaysEncrypted;User Id=User;Password=密碼;Column Encryption Setting=Enabled"))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand("", conn))
    {
        cmd.CommandText = @"SELECT [SSN],  [Name], [BirthDate] FROM [dbo].[Customers] WHERE [SSN] = @SSN;";
        SqlParameter paramSSNQuery = cmd.CreateParameter();
        paramSSNQuery.ParameterName = @"@SSN";
        paramSSNQuery.DbType = DbType.AnsiStringFixedLength;
        paramSSNQuery.Direction = ParameterDirection.Input;
        paramSSNQuery.Value = "00000000001";
        paramSSNQuery.Size = 11;
        cmd.Parameters.Add(paramSSNQuery);

        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = cmd;
        DataTable table = new DataTable();
        adapter.Fill(table);    }
}

ADO.NET條件值自動加密