[SQL SERVER]SQL2016-Always Encrypted

2017-04-30

Always Encrypted功能將資料庫一些敏感資料永遠加密，

有效防止DBA或未經授權人員查看這些敏感資料，

因APP(需使用.NETFramework4.6以上版本)將資料送進DB時已經加密，且透過APP讀取也能自動解密。

SQL2016 Always Encrypted使用了以下核心技術

1.Column Master Key:保護欄位加密的主要金鑰

2.Column Encryption Key:實際保護欄位加密的加密金鑰

3.Column-level encryption setting:2種欄位加密方式

Deterministic:相同的加密文字內容，是說文字內容相同，經過加密後也會產生相同加密文字，

如果資料內容單純，很容易可以推理出原始文字內容，且必須搭配BIN2的資料行定序，僅支援lookups, distinct, group by操作(可索引)。

Randomized:隨機的加密文字內容，比Deterministic加密方式更為安全，但不支上訴操作(僅寫和顯示)也無法被索引。

4.Connection string:為了讓client了解欄位加密處理，連線字串必須加入Column Encryption Setting = enabled; 屬性

現在我就快速進行設定並測試

A.Create Column Master Key

產生憑證。

B.Create Column Encryption Key

c.Create Table with encrypted and insert data

create table empinfo
(
empid int not null primary key
,empname nvarchar(20) not null
,empsecurity varchar(10) not null
)
insert into empinfo
values(1,N'rico','A123456789')

--select * from empinfo


create table Encryptedempinfo
(
empid int not null primary key
,empname nvarchar(20) COLLATE Latin1_General_BIN2 
    ENCRYPTED WITH 
    (
       ENCRYPTION_TYPE = DETERMINISTIC, 
       ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
       COLUMN_ENCRYPTION_KEY = CEK--請依個人環境修改
    ) not null     
,empsecurity varchar(10) 
    ENCRYPTED WITH 
    (
       ENCRYPTION_TYPE = RANDOMIZED, 
       ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
       COLUMN_ENCRYPTION_KEY = CEK--請依個人環境修改
    )not null 
);

insert into Encryptedempinfo
values(1,N'rico','A123456789')

新增資料至欄位加密會發生錯誤，這裡必須透過使用Column Encryption Setting屬性的連線字串APP才能新增資料。

未使用欄位加密即可很輕鬆透過SSMS對資料進行相關操作

連線字串新增Column Encryption Setting=Enabled

透過Ado.net新增資料

int empid=int.Parse(textBox1.Text);
            string empname=textBox2.Text;
            string empsecurity=textBox3.Text;
            using (SqlConnection conn = new SqlConnection(connstring))
            {
                string sqlstatement = "insert into Encryptedempinfo values(@p1,@p2,@p3)";
                using (SqlCommand cmd = new SqlCommand(sqlstatement, conn))
                {
                    SqlParameter p1 = new SqlParameter("@p1", System.Data.SqlDbType.Int);
                    p1.Value = empid;
                    cmd.Parameters.Add(p1);

                    SqlParameter p2 = new SqlParameter("@p2", System.Data.SqlDbType.NVarChar,20);
                    p2.Value = empname;
                    cmd.Parameters.Add(p2);

                    SqlParameter p3 = new SqlParameter("@p3", System.Data.SqlDbType.VarChar,10);
                    p3.Value = empsecurity;
                    cmd.Parameters.Add(p3);
                    //show error message:Encryption scheme mismatch for columns/variables '@p1'XXX
                    //cmd.Parameters.AddWithValue("@p1", empid);
                    //cmd.Parameters.AddWithValue("@p2", empname);
                    //cmd.Parameters.AddWithValue("@p3", empsecurity);
                    if (conn.State == ConnectionState.Closed)
                        conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            MessageBox.Show("success");