[研究]ASP.NET,WebForm, SQL Server Always Encrypted 加密資料行、加密資料庫試用
2024-06-06
SQL Server Always Encrypted
環境：Visual Studio 2022 + ASP.NET + WebForm + Web Application + C# + SQL Server 2019 + SQL Server Management Studio (SSMS) 19
教學課程：開始使用 Always Encrypted - SQL Server | Microsoft Learn
https://learn.microsoft.com/zh-tw/sql/relational-databases/security/encryption/always-encrypted-tutorial-getting-started?view=sql-server-ver16&tabs=ssms
使用SQL Server 2016的Always Encrypt功能防止系統管理員讀取私密性資料https://www.uuu.com.tw/Public/content/article/19/20190805.htm
********************************************************************************
SQL Server Always Encrypted 是甚麼 ? SQL Server 哪一版開始支援 ?
ChatGPT
SQL Server Always Encrypted 是一種用來保護敏感數據的功能，它使得數據在傳輸過程中和存儲在數據庫中的時候都保持加密狀態。這項技術允許客戶端應用程式將敏感數據加密，然後將其存儲在 SQL Server 中，而數據庫引擎本身無法解密這些數據。這樣，即使數據庫被攻擊者入侵或被未經授權的人訪問，數據依然是不可讀的。
SQL Server Always Encrypted 的主要特點包括：
- 客戶端加密： 敏感數據在客戶端應用程式中進行加密和解密，數據庫引擎無法看到未加密的數據。
- 密鑰管理： 加密密鑰存儲在客戶端或安全密鑰管理器中，而不是 SQL Server 中。這確保了數據庫管理員無法訪問密鑰。
- 透明加密： 對應用程序來說，加密和解密過程是透明的，不需要對應用程序做大量修改。
SQL Server Always Encrypted 支援的版本：
SQL Server Always Encrypted 最早在 SQL Server 2016 版本中引入，並在隨後的版本中進行了改進和擴展。後續版本（如 SQL Server 2017、2019 及更新的版本）在功能性和易用性上進行了進一步的增強。
這項技術主要是為了應對合規性要求和提升數據安全性，是許多企業在處理敏感數據時採用的重要策略之一。
********************************************************************************
SQL Server Always Encrypted 中，資料行加密金鑰 (CEK) 是甚麼？資料行主要金鑰 (CMK) 是甚麼？
ChatGPT
在 SQL Server Always Encrypted 中，資料行加密金鑰（Column Encryption Key, CEK）和資料行主要金鑰（Column Master Key, CMK）是兩種關鍵的加密金鑰，它們在保護敏感數據方面起著重要作用。以下是它們的詳細說明：
資料行加密金鑰 (Column Encryption Key, CEK)
- 定義：CEK 是用來加密資料行中的實際數據的金鑰。每一個需要加密的資料行都會使用一個 CEK 來進行加密操作。
- 特性：CEK 是對稱金鑰，這意味著同一個金鑰用於加密和解密操作。CEK 是存儲在 SQL Server 中的，但它本身是加密的，不能被直接讀取或使用。
- 加密方式：CEK 是使用資料行主要金鑰（CMK）加密的。只有擁有解密 CMK 權限的實體才能解密並使用 CEK 來訪問加密的數據。
資料行主要金鑰 (Column Master Key, CMK)
- 定義：CMK 是用來加密 CEK 的金鑰。它提供了一個額外的安全層，確保即使攻擊者獲取了加密的 CEK 也無法解密數據，除非他們也能訪問 CMK。
- 特性：CMK 是非對稱金鑰，包含一對公鑰和私鑰。通常，私鑰存儲在硬體安全模組（HSM）、Azure Key Vault 或 Windows Certificate Store 等安全位置。
- 加密方式：CMK 的私鑰用於加密 CEK，這樣確保只有擁有 CMK 私鑰的人能解密 CEK，進而使用 CEK 來加密或解密資料行中的數據。
工作流程
- 當需要加密資料行中的數據時，首先生成 CEK 並使用 CMK 加密該 CEK。
- 加密後的 CEK 存儲在 SQL Server 中，而 CMK 保存在外部安全存儲中。
- 當客戶端需要訪問加密數據時，它會向 SQL Server 請求加密的 CEK。
- SQL Server 將加密的 CEK 傳送給客戶端，客戶端使用 CMK 的私鑰解密 CEK。
- 一旦獲取到解密的 CEK，客戶端便可使用它來加密或解密所需的數據。
這種雙層金鑰管理機制使得敏感數據的安全性大大增強，因為即使數據庫遭到攻擊，攻擊者也無法輕易解密數據，除非他們同時擁有 CEK 和 CMK 的私鑰。
********************************************************************************
SQL Server 2019 + SSMS 20.1 (SQL Server Management Studio 20.1) 上測試
建立測試用DB
USE [MyAlwaysEncryptedDB] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable1]') AND type in (N'U')) DROP TABLE [dbo].[MyTable1] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[MyTable1]( [SN] [int] IDENTITY(1,1) NOT NULL, [CNAME] [nvarchar](50) NULL, [CID] [nvarchar](50) NULL, [CITY] [nvarchar](50) NULL, [CTel] [nvarchar](50) NULL, [CEmail] [nvarchar](50) NULL, CONSTRAINT [PK_MyTable1] PRIMARY KEY CLUSTERED ( [SN] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[MyTable1] ON GO INSERT [dbo].[MyTable1] ([SN], [CNAME], [CID], [CITY], [CTel], [CEmail]) VALUES (1, N'趙一', N'F123456789', N'台北', N'0912-345-678', N'a1@mytest.idv'), (2, N'錢二', N'F234567891', N'台北', N'0923-345-678', N'a2@mytest.idv'), (3, N'孫三', N'F345678912', N'台北', N'0934-345-678', N'a3@mytest.idv'), (4, N'李四', N'F456789123', N'台中', N'0945-345-678', N'a4@mytest.idv'), (5, N'周五', N'F567891234', N'台中', N'0956-345-678', N'a5@mytest.idv'), (6, N'吳六', N'F678912345', N'台中', N'0967-345-678', N'a6@mytest.idv'), (7, N'鄭七', N'F789123456', N'高雄', N'0978-345-678', N'a7@mytest.idv'), (8, N'王八', N'F891234567', N'高雄', N'0989-345-678', N'a8@mytest.idv'), (9, N'馮九', N'F912345678', N'高雄', N'0990-345-678', N'a9@mytest.idv') GO SET IDENTITY_INSERT [dbo].[MyTable1] OFF GO |
USE [MyAlwaysEncryptedDB] GO INSERT INTO [dbo].[MyTable1] ([CNAME],[CID],[CITY],[CTel],[CEmail]) VALUES (N'陳十', N'F123456789', N'台北', N'0912-345-678', N'a1@mytest.idv'); |
訊息 206，層級 16，狀態 2，行 3
運算元類型衝突: nvarchar 與 nvarchar(4000) encrypted with (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'MyAlwaysEncryptedDB') 不相容
(下圖)用參數化查詢 (parameterized query) 方式插入一筆資料，成功
USE [MyAlwaysEncryptedDB] GO DECLARE @CNAME nvarchar(50) = N'陳十'; DECLARE @CID nvarchar(50) = N'F112345678'; DECLARE @CITY nvarchar(50) = N'南投'; DECLARE @CTel nvarchar(50) = N'0903-456-789'; DECLARE @CEmail nvarchar(50) = N'a10@mytest.idv'; INSERT INTO [dbo].[MyTable1] ([CNAME],[CID],[CITY],[CTel],[CEmail]) VALUES (@CNAME,@CID,@CITY,@CTel,@CEmail); |
Web.config，要加上 Column Encryption Setting=Enabled
<?xml version="1.0" encoding="utf-8"?>
<!--
如需如何設定 ASP.NET 應用程式的詳細資訊，請前往
https://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<connectionStrings>
<add name="MyAlwaysEncryptedDBConnectionString" connectionString="Data Source=.;Initial Catalog=MyAlwaysEncryptedDB;User ID=sa;Password=P@ssw0rd;Column Encryption Setting=Enabled"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" targetFramework="4.8" />
<httpRuntime targetFramework="4.8" />
</system.web>
<system.codedom>
<compilers>
<compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:1659;1699;1701" />
<compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:41008 /define:_MYTYPE=\"Web\" /optionInfer+" />
</compilers>
</system.codedom>
</configuration>
|
Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication1.Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:MyAlwaysEncryptedDBConnectionString %>"
DeleteCommand="DELETE FROM [MyTable1] WHERE [SN] = @SN"
InsertCommand="INSERT INTO [MyTable1] ([CNAME], [CID], [CITY], [CTel], [CEmail]) VALUES (@CNAME, @CID, @CITY, @CTel, @CEmail)"
SelectCommand="SELECT * FROM [MyTable1]"
UpdateCommand="UPDATE [MyTable1] SET [CNAME] = @CNAME, [CID] = @CID, [CITY] = @CITY, [CTel] = @CTel, [CEmail] = @CEmail WHERE [SN] = @SN">
<DeleteParameters>
<asp:Parameter Name="SN" Type="Int32" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="CNAME" Type="String" />
<asp:Parameter Name="CID" Type="String" />
<asp:Parameter Name="CITY" Type="String" />
<asp:Parameter Name="CTel" Type="String" />
<asp:Parameter Name="CEmail" Type="String" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="CNAME" Type="String" />
<asp:Parameter Name="CID" Type="String" />
<asp:Parameter Name="CITY" Type="String" />
<asp:Parameter Name="CTel" Type="String" />
<asp:Parameter Name="CEmail" Type="String" />
<asp:Parameter Name="SN" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="True" DataKeyNames="SN" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="SN" HeaderText="SN" InsertVisible="False" ReadOnly="True" SortExpression="SN" />
</Columns>
</asp:GridView>
</form>
</body>
</html>
|
Default.aspx.cs
內容不用改 |
Default2.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default2.aspx.cs" Inherits="WebApplication1.Default2" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<asp:Button ID="Button_Insert" runat="server" Text="插入" OnClick="Button_Insert_Click" />
</form>
</body>
</html>
|
Default2.aspx.cs
using System;
using System.Data.SqlClient;
using System.Web.Configuration;
namespace WebApplication1
{
public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button_Insert_Click(object sender, EventArgs e)
{
string queryString = @"INSERT INTO [dbo].[MyTable1] ([CNAME],[CID],[CITY],[CTel],[CEmail])
VALUES (@CNAME,@CID,@CITY,@CTel,@CEmail);";
using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["MyAlwaysEncryptedDBConnectionString"].ConnectionString))
{
SqlCommand command = new SqlCommand(queryString, conn);
command.Parameters.Clear();
command.Parameters.AddWithValue("@CNAME", "褚十一");
command.Parameters.AddWithValue("@CID", "F123456780");
command.Parameters.AddWithValue("@CITY", "嘉義");
command.Parameters.AddWithValue("@CTel", "0904-456-789");
command.Parameters.AddWithValue("@CEmail", "a11@mytest.idv");
conn.Open();
try
{
command.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
}
}
}
}
|
執行結果
********************************************************************************
(以下尚未測試)
SQL Server Always Encrypted 的金鑰期限到達後，要如何重做新的金鑰？如何替換設定？
ChatGPT
在 SQL Server 中，Always Encrypted 功能確保了靜態數據在數據庫中的加密。當金鑰的期限到達後，您需要生成新的金鑰並替換現有的金鑰。以下是具體步驟：
步驟 1: 創建新的金鑰
生成新的 Column Master Key (CMK)：
如果您需要替換 Column Master Key，首先需要創建新的 CMK。在 SQL Server Management Studio (SSMS) 中，可以使用以下命令創建新的 CMK：
CREATE COLUMN MASTER KEY [NewColumnMasterKey]
WITH (
KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
KEY_PATH = N'CurrentUser/My/NewCert'
);
生成新的 Column Encryption Key (CEK)：
接著，創建新的 Column Encryption Key，並使用新的 CMK 進行加密：
CREATE COLUMN ENCRYPTION KEY [NewColumnEncryptionKey]
WITH VALUES (
COLUMN_MASTER_KEY = [NewColumnMasterKey],
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = ENCRYPTBYKEY(KEY_GUID('NewColumnMasterKey'), CONVERT(varbinary, 'YourPlainTextValue'))
);
步驟 2: 更新表中的加密列
定義新金鑰的使用：
您需要更新表中的加密列以使用新的 CEK。假設您有一個名為 MyTable 的表和一個名為 MyColumn 的加密列：
ALTER TABLE MyTable
ALTER COLUMN MyColumn
ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = NewColumnEncryptionKey)
步驟 3: 刷新並驗證
刷新密鑰定義：
刷新金鑰以確保更改生效：
EXEC sp_refresh_parameter_encryption
驗證更改：
驗證新的金鑰已經正確應用到您的列：
SELECT * FROM MyTable WHERE MyColumn = 'YourTestValue'
注意事項
確保備份原有的金鑰和證書，防止數據丟失。
更新金鑰的操作應該在停機窗口內進行，以免影響生產環境中的應用程序。
如果有應用程序直接連接數據庫，請確保這些應用程序也更新其連接設置以使用新的金鑰。
通過上述步驟，您可以有效地替換 SQL Server Always Encrypted 的金鑰，確保數據的持續保護。
********************************************************************************
SQL Server Always Encrypted 的金鑰期限到達後，如何用 SSMS 或 certmgr 重做新的金鑰？如何用 SSMS 或 certmgr 設定 SQL Server Always Encrypted 使用新的金鑰？
ChatGPT
當 SQL Server Always Encrypted 的金鑰期限到達後，您需要生成新的金鑰並配置 SQL Server 使用這些新的金鑰。您可以使用 SQL Server Management Studio (SSMS) 或 Windows 的證書管理工具 (certmgr) 來完成這些任務。以下是具體步驟：
步驟 1: 使用 certmgr 生成新的證書
1.打開 certmgr：
在 Windows 上按 Win + R，輸入 certmgr.msc 並按回車。
2.創建新的證書：
在左側導航欄中選擇 Personal 資料夾，然後右鍵選擇 All Tasks -> Request New Certificate。
按照向導步驟生成新的證書。
步驟 2: 在 SSMS 中配置新的 Column Master Key (CMK)
1.打開 SSMS 並連接到您的 SQL Server 實例。
2.創建新的 CMK：
使用下列 SQL 腳本創建新的 Column Master Key：
CREATE COLUMN MASTER KEY [NewColumnMasterKey]
WITH (
KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
KEY_PATH = N'CurrentUser/My/ThumbprintOfNewCertificate'
);
步驟 3: 創建新的 Column Encryption Key (CEK)
創建新的 CEK：
使用下列 SQL 腳本創建新的 Column Encryption Key：
CREATE COLUMN ENCRYPTION KEY [NewColumnEncryptionKey]
WITH VALUES (
COLUMN_MASTER_KEY = [NewColumnMasterKey],
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = ENCRYPTBYKEY(KEY_GUID('NewColumnMasterKey'), CONVERT(varbinary, 'YourPlainTextValue'))
);
步驟 4: 更新現有的加密列使用新的 CEK
更新表中的加密列：
假設您有一個名為 MyTable 的表和一個名為 MyColumn 的加密列：
ALTER TABLE MyTable
ALTER COLUMN MyColumn
ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = NewColumnEncryptionKey)
步驟 5: 刷新並驗證
刷新密鑰定義：
使用下列 SQL 命令刷新金鑰：
EXEC sp_refresh_parameter_encryption
驗證更改：
驗證新的金鑰已經正確應用到您的列：
SELECT * FROM MyTable WHERE MyColumn = 'YourTestValue'
【完整示範】
以下是一個完整的範例，假設您已經生成了一個新的證書，其指紋為 ABCD1234：
創建新的 CMK：
CREATE COLUMN MASTER KEY [NewColumnMasterKey]
WITH (
KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
KEY_PATH = N'CurrentUser/My/ABCD1234'
);
創建新的 CEK：
CREATE COLUMN ENCRYPTION KEY [NewColumnEncryptionKey]
WITH VALUES (
COLUMN_MASTER_KEY = [NewColumnMasterKey],
ALGORITHM = 'RSA_OAEP'
);
更新加密列：
ALTER TABLE MyTable
ALTER COLUMN MyColumn
ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = NewColumnEncryptionKey)
刷新密鑰定義：
EXEC sp_refresh_parameter_encryption
驗證：
SELECT * FROM MyTable WHERE MyColumn = 'YourTestValue'
這樣您就完成了使用新的金鑰替換 SQL Server Always Encrypted 中過期金鑰的整個過程。
********************************************************************************
(完)
相關
教學課程：開始使用 Always Encrypted - SQL Server | Microsoft Learn
https://learn.microsoft.com/zh-tw/sql/relational-databases/security/encryption/always-encrypted-tutorial-getting-started?view=sql-server-ver16&tabs=ssms
使用SQL Server 2016的Always Encrypt功能防止系統管理員讀取私密性資料https://www.uuu.com.tw/Public/content/article/19/20190805.htm
沒有留言:
張貼留言