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條件值自動加密
回傳資料也順利解密:
最後,把剛剛新增的指令碼執行兩次,可以觀察兩種加密型態的差異:
- 固定加密DETERMINISTIC:相同的值加密結果相同。
- 動態加密RANDOMIZED:相同的值,不同的加密結果(但要注意這個欄位的分組排序語法會失效)
排序RANDOMIZED欄位時會得到以下訊息:
7.最後就是回到DB Server把存放CMK的憑證刪除。
繞口令:
- AP Server有CMK,但沒有CEK及資料。
- DB Server有加密後的CEK、加密後的資料,但沒有CMK可以解開CEK。
小結:
- 以後Stored procedure存取資料要注意。
- Bulkcopy也可正常加密寫入。
感想:很類似常碰到的檔案加密流程,依據加密效能及加密程度彈性選用對稱式及非對稱式加密檢演算法
1.產生Session key。
2.用Session key+對稱式演算法加密檔案 。
3.用非對稱式驗算法加密Session key。
4.將加密後的資料和加密後的Session key給遠端系統解密。
參考: