在Day07 資料儲存安全(SQL Always Encrypted)，我們把SQL一律加密(Always Encrypted)的環境準備好了，今天來從DB Server端觀察實際AP Server寫入資料及查詢資料的條件值。

記得今年3月老師介紹當時，還只能使用.NET Framework 4.6來加解密資料，半年後，新版的SQL管理工具(SSMS)也支援加密輸入並解密結果集(resultset)了，我們今天一起來試試。

.NET程式端寫入資料

這邊我們使用ADO.NET Sqlcommand。

先打開SQL Server Profiler程式

啟動SQL Server Profiler

執行.NET程式，關鍵有兩個:

• Connectionstring中加了一組參數Column Encryption Setting=Enabled
• .NET專案目標framework必須為.NET 4.6

using (SqlConnection conn = new SqlConnection(@"Data Source=資料庫;Initial Catalog=SecurityDB;User Id=帳號;Password=密碼;Column Encryption Setting=Enabled"))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand("", conn))
    {
        cmd.CommandText = @"INSERT INTO [dbo].[Patients] ([Name],[SSN],[Country]) VALUES (@Name, @SSN, 'USA');";

        SqlParameter paramSSN = cmd.CreateParameter();
        paramSSN.ParameterName = @"@SSN";
        paramSSN.DbType = DbType.AnsiStringFixedLength;

        SqlParameter paramName = cmd.CreateParameter();
        paramName.ParameterName = @"@Name";
        paramName.DbType = DbType.AnsiStringFixedLength;
        
        //Jane Doe
        cmd.Parameters.Clear();
        paramName.Value = "Jane Doe";
        cmd.Parameters.Add(paramName);
        paramSSN.Value = "198-33-0986";
        cmd.Parameters.Add(paramSSN);
        cmd.ExecuteNonQuery();
    }
}

目標framework必須為.NET 4.6

執行程式完畢後，打開SSMS(SQL Server Management Studio)，執行SQL查詢

SELECT * from [dbo].[Patients]

SQL Server回傳的結果集也是加密的。

觀察SQL Server Profiler:

傳送過來的SSN社會安全編號就是加密過後的，這邊算是確保傳輸過程也是加密的。

.NET端查詢資料

剛剛使用ADO.NET Sqlcommand，這次改用輕量化的ORM利器Dapper。

執行.NET程式

 //使用Dapper查詢
using (SqlConnection conn = new SqlConnection(@"Data Source=資料庫;Initial Catalog=SecurityDB;User Id=帳號;Password=密碼;Column Encryption Setting=Enabled"))
 {
     //成功
     string query = "select * from Patients where SSN = @SSN  ";
     //一定要參數化+指定型別才能查詢
     var Results = db.Query<patient>(query, new { SSN = new DbString() { Value = "198-33-0986", IsAnsi = true, Length = 11 } });
     //顯示資料
     foreach (var item in Results)
     {
         Console.WriteLine("Name:{0}   SSN:{1}", item.Name, item.SSN, item.Country);
     }
 }

執行查詢時，DB會回傳已加密的結果集，資料會在AP端自動解密，執行結果:

觀察SQL Profiler:

傳送過來的SSN社會安全編號也是加密過後的，確保傳輸過程也是加密的。

SSMS(SQL Server Management Studio)查詢資料

首先需要安裝SSMS 17.0版本以上

下載 SQL Server Management Studio (SSMS)

安裝中

安裝完畢後，打開SSMS，點開選項

連接屬性 勾選加密連接

選取 [其他屬性] 索引標籤，然後輸入 Column Encryption Setting=Enabled

執行SQL查詢

SELECT * from [dbo].[Patients]

噹噹噹噹!SSN終於可以看得見了!

不過前提是使用SSMS的機器還是有安裝Day07 資料儲存安全(SQL Always Encrypted)上所製作的憑證。

SSMS(SQL Server Management Studio)寫入資料

可以查詢資料，那來試試寫入資料!

從SSMS輸入以下指令，一律加密的資料行SSN社會安全編號必須以參數方式帶入。

DECLARE @SSN  CHAR(11) = '198-33-0987'
INSERT INTO [dbo].[Patients] (Name,SSN,Country)
VALUES ('Jim Gray',@SSN  ,'USA')

DECLARE @SSN2  CHAR(11) = '198-33-0988'
INSERT INTO [dbo].[Patients] (Name,SSN,Country)
VALUES ('John smith',@SSN2 ,'USA')

SELECT * FROM [Patients]

執行結果:

按照圖示來查詢微軟科學家jim gray(致力貢獻資料庫及交易處理研究，1998年獲得圖靈獎)

查詢條件也必須輸入參數。

DECLARE @SSN  CHAR(11) = '198-33-0987'
SELECT * FROM [Patients]
WHERE SSN = @SSN

一律加密(Always Encrypted)的優缺點

優點:

• 傳輸過程加密，儲存加密。
• 與資料庫分開保管資料、金鑰(CEK)及主要金鑰(CMK)。
• 不需要改動AP程式碼邏輯，只需要調整連線字串參數，
• 新版SSMS也支援查詢解密及輸入加密功能。
• 由於SQL參數化的徹底要求，可以避免SQL Injection。

缺點:

• 設定步驟較多。
• SQL Server 2016才開始支援。

參考:

下載 SQL Server Management Studio (SSMS)

使用 SQL Server Management Studio 設定永遠加密

Configure Always Encrypted using SQL Server Management Studio

柳川

2016.04攝於柳川，九州