File backup bị mang ra ngoài: mã hóa file trên đĩa bằng TDE

1. Vấn đề: ai cầm file backup là đọc được dữ liệu khách #

Full backup 22:00 ngày 2026-10-01 của BanHang nằm trên ổ B: của một máy khác, đúng như chương sao lưu khuyên. Ngày 2026-10-03, một bản sao của file đó được chép ra ổ USB để dựng môi trường thử cho đối tác phát triển, và ổ USB thất lạc.

Câu hỏi đầu tiên của bộ phận pháp chế: người nhặt được ổ đọc được gì? File .bak chứa nguyên các page dữ liệu. Không có mã hóa, bất kỳ ai có một bản SQL Server, kể cả bản Developer miễn phí, đều restore được và SELECT thoải mái. Người đó không cần tài khoản BanHang, vì quyền đăng nhập nằm trên máy chủ cũ, còn họ là sysadmin trên máy của mình.

Dựng lại trên LocalDB với database thử có 200.000 khách:

Rủi ro không chỉ nằm ở file backup. File dữ liệu trên D:, E:, G: và file log trên L: cũng chứa cùng dữ liệu, dạng chưa mã hóa.

2. Mục đích: file rời khỏi máy chủ thì vô dụng #

• Người cầm file .bak, .mdf hoặc .ldf không đọc được dữ liệu: dò chuỗi không thấy số điện thoại nào, restore trên máy khác không có certificate thì thất bại.
• Máy dự phòng có certificate thì restore được: diễn tập restore thành công.
• Certificate có bản sao lưu: pvt_key_last_backup_date khác NULL, health check của ứng dụng báo xanh.
• Ngoài phạm vi: người đã đăng nhập và có quyền SELECT, vì TDE không chặn họ (xem phân quyền bảng lương); mã hóa đường truyền bằng TLS.

3. Cơ sở lý thuyết: mã hóa từng page khi ghi xuống đĩa #

Page trên đĩa là dữ liệu đọc thẳng được #

SQL Server ghi dữ liệu theo page 8 KB. Không nén, không mã hóa, một cột varchar nằm trong page đúng bằng các byte ASCII của nó. Backup chép các page đó vào file .bak. Vì thế một vòng lặp đơn giản tìm chuỗi "09" theo sau là 8 chữ số đã thấy đủ 200.000 số điện thoại.

TDE mã hóa ở tầng I/O #

TDE mã hóa page trước khi ghi xuống đĩa và giải mã khi đọc vào bộ nhớ. Nó áp cho file dữ liệu và file log, và vì backup chép page đã mã hóa nên file backup cũng mã hóa theo. Ứng dụng không phải đổi gì; dữ liệu trong bộ nhớ và trên đường truyền vẫn là dữ liệu rõ.

Vài hệ quả theo tài liệu:

• tempdb tự được mã hóa khi bất kỳ database nào trên instance bật TDE.
• Dữ liệu FILESTREAM không được mã hóa.
• Instant file initialization không dùng được cho file của database đã bật TDE.
• Bật TDE trên database có sẵn chạy một lượt quét đọc rồi ghi lại mọi page. Từ SQL Server 2019, lượt quét này tạm dừng và tiếp tục được bằng SET ENCRYPTION SUSPEND và RESUME.

Chuỗi khóa và chỗ nằm của từng khóa #

Mỗi tầng khóa bảo vệ tầng dưới. Điểm mấu chốt: certificate nằm trong master, không nằm trong file backup của BanHang.

flowchart TD
  DP["DPAPI của Windows"] --> SMK["Service master key của instance"]
  SMK --> DMK["Database master key trong master"]
  DMK --> CERT["Certificate TdeCert_BanHang trong master"]
  CERT --> DEK["Database encryption key trong BanHang"]
  DEK --> F["File dữ liệu, file log, bản backup"]
  CERT -.->|"BACKUP CERTIFICATE"| B["Ổ B: file .cer và .pvk"]

Restore một bản backup đã mã hóa lên máy khác cần certificate cùng khóa riêng có mặt trong master của máy đó. Không có, lệnh restore dừng với lỗi 33111, "Cannot find server certificate with thumbprint". Đó là điều bảo vệ dữ liệu khi file bị mang ra ngoài, và cũng là điều làm mất dữ liệu khi chính mình mất certificate: file .bak còn nguyên cũng không restore được.

Edition #

Trên SQL Server 2019, TDE có ở Enterprise và Standard; Web và Express không có. LocalDB là Express, nên lệnh CREATE DATABASE ENCRYPTION KEY trả lỗi 33117, "Transparent Data Encryption is not available in the edition of this SQL Server instance".

4. Cách giải quyết: TDE cho cả database, certificate sao lưu ra ngoài #

BanHang chạy SQL Server 2019 Standard, nên chọn TDE: một lần bật bảo vệ mọi file của database, không đổi ứng dụng. Các bước:

1. Tạo database master key và certificate trong master.
2. Sao lưu certificate kèm khóa riêng ra B:\Keys trên máy khác ngay lúc tạo, mật khẩu file khóa giữ ở kho mật khẩu.
3. Tạo database encryption key trong BanHang, bật ENCRYPTION ON, theo dõi lượt quét tới khi encryption_state = 3.
4. Trên máy dự phòng, nạp certificate từ file và diễn tập restore.
5. Ứng dụng có health check báo khi database chưa mã hóa hoặc certificate chưa được sao lưu.

5. Cách cài đặt: ba lệnh ở master, hai lệnh ở BanHang #

Đoạn SQL dưới chưa chạy thử, vì LocalDB không có TDE. Mật khẩu là chỗ trống để điền từ kho mật khẩu, không ghi vào script trong git.

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'<mat-khau-master-key>';
CREATE CERTIFICATE TdeCert_BanHang WITH SUBJECT = N'TDE BanHang';
BACKUP CERTIFICATE TdeCert_BanHang
    TO FILE = N'B:\Keys\TdeCert_BanHang.cer'
    WITH PRIVATE KEY (FILE = N'B:\Keys\TdeCert_BanHang.pvk',
                      ENCRYPTION BY PASSWORD = N'<mat-khau-file-khoa>');
GO
USE BanHang;
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE TdeCert_BanHang;
ALTER DATABASE BanHang SET ENCRYPTION ON;

Lệnh SET ENCRYPTION ON trả về ngay; lượt quét chạy nền. Theo dõi tới khi encryption_state lên 3. Câu dưới đã chạy trên LocalDB để kiểm cột; ở đó nó trả 0 dòng vì không database nào có khóa TDE.

SELECT DB_NAME(database_id) AS db, encryption_state, percent_complete,
       encryption_scan_state_desc, encryption_scan_modify_date
FROM sys.dm_database_encryption_keys;

Bật TDE không mã hóa ngược những gì đã nằm ngoài máy. Tài liệu ghi engine tạo log mới được mã hóa từ lúc bật, còn log sinh trước đó thì không. Các bản full, differential và log backup lấy trước khi bật vẫn là dữ liệu rõ, như file trong mục 1. Lấy một bản full mới sau khi encryption_state = 3, rồi xử lý các bản cũ theo chính sách lưu: hủy, hoặc giữ ở nơi được bảo vệ như chính dữ liệu gốc.

Trên máy dự phòng, nạp certificate từ hai file đó trước khi restore:

CREATE CERTIFICATE TdeCert_BanHang
    FROM FILE = N'B:\Keys\TdeCert_BanHang.cer'
    WITH PRIVATE KEY (FILE = N'B:\Keys\TdeCert_BanHang.pvk',
                      DECRYPTION BY PASSWORD = N'<mat-khau-file-khoa>');

Phía .NET là một kiểm tra đặt trong health check của API. Câu SQL lấy từ tài liệu TDE: nối sys.dm_database_encryption_keys với sys.certificates để đọc ngày sao lưu khóa riêng gần nhất.

static async Task<string> TrangThaiTde(string cs)
{
    await using var cn = new SqlConnection(cs);
    await cn.OpenAsync();
    await using var cmd = new SqlCommand("""
        SELECT DB_NAME(k.database_id), k.encryption_state, c.name, c.pvt_key_last_backup_date
        FROM sys.dm_database_encryption_keys AS k
        LEFT JOIN master.sys.certificates AS c ON c.thumbprint = k.encryptor_thumbprint
        WHERE k.database_id = DB_ID();
        """, cn);
    await using var r = await cmd.ExecuteReaderAsync();
    if (!await r.ReadAsync()) return $"{cn.Database}: chưa có database encryption key, file dữ liệu và backup không được mã hóa";
    var daSaoLuu = !r.IsDBNull(3);
    return $"{r.GetString(0)}: encryption_state = {r.GetInt32(1)} (3 là đã mã hóa), certificate {r.GetString(2)}, "
        + (daSaoLuu ? $"sao lưu khóa riêng lần cuối {r.GetDateTime(3):yyyy-MM-dd HH:mm}" : "CHƯA sao lưu khóa riêng");
}

Bản đầy đủ dưới chạy trên .NET 10.0.401, Microsoft.Data.SqlClient 7.1.1. Nó đọc trạng thái TDE của Kumeo_C trên MSSQLLocalDB, backup database đó, dò số điện thoại trong file .bak, restore file lên một instance LocalDB riêng tên KumeoC như máy của người nhặt được file, rồi thử tạo database encryption key trên instance riêng để ghi lại lỗi. Mật khẩu master key của instance thử sinh ngẫu nhiên lúc chạy và không được lưu.

#:package Microsoft.Data.SqlClient@7.1.1
// File backup của Kumeo_C có lộ số điện thoại khách không, và TDE đã bật kèm certificate đã sao lưu chưa.
// Chạy: dotnet run KiemTraTde.cs   (LocalDB SQL Server 2019; phần restore cần instance riêng: sqllocaldb create KumeoC 15.0)
using System.Diagnostics;
using System.Globalization;
using System.Security.Cryptography;
using System.Text;
using Microsoft.Data.SqlClient;

CultureInfo.DefaultThreadCurrentCulture = CultureInfo.CurrentCulture = new CultureInfo("vi-VN");
const string Cs = @"Server=(localdb)\MSSQLLocalDB;Database=Kumeo_C;Integrated Security=true;TrustServerCertificate=true;Command Timeout=600";
const string MayKhac = @"Server=(localdb)\KumeoC;Database=master;Integrated Security=true;TrustServerCertificate=true;Command Timeout=600";
var thuMuc = Path.Combine(Path.GetTempPath(), "kumeo-c-tde");
Directory.CreateDirectory(thuMuc);

// 1. Trạng thái TDE và bản sao lưu certificate, đúng câu kiểm tra của tài liệu TDE.
Console.WriteLine(await TrangThaiTde(Cs));

// 2. Backup như job 22:00, rồi dò byte trong file: số điện thoại varchar nằm nguyên dạng ASCII.
var bak = Path.Combine(thuMuc, "Kumeo_C_full.bak");
await Exec(Cs, $"BACKUP DATABASE Kumeo_C TO DISK = N'{bak}' WITH INIT, CHECKSUM;");
var soDienThoai = new HashSet<string>();
await using (var cn = new SqlConnection(Cs))
{
    await cn.OpenAsync();
    await using var r = await new SqlCommand("SELECT SoDienThoai FROM dbo.KhachHang WHERE SoDienThoai IS NOT NULL;", cn).ExecuteReaderAsync();
    while (await r.ReadAsync()) soDienThoai.Add(r.GetString(0));
}
var dho = Stopwatch.StartNew();
var thay = DoSoDienThoai(await File.ReadAllBytesAsync(bak), soDienThoai);
Console.WriteLine($"File {Path.GetFileName(bak)}: {new FileInfo(bak).Length / 1048576.0:N0} MB, dò trong {dho.Elapsed.TotalSeconds:N1} s, thấy {thay:N0}/{soDienThoai.Count:N0} số điện thoại hiện tại dạng chữ thường");

// 3. Người cầm file restore trên một instance khác, không cần khóa nào.
var sw = Stopwatch.StartNew();
await Exec(MayKhac, $"""
    IF DB_ID(N'Kumeo_C_Lay') IS NOT NULL BEGIN ALTER DATABASE Kumeo_C_Lay SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE Kumeo_C_Lay; END;
    RESTORE DATABASE Kumeo_C_Lay FROM DISK = N'{bak}'
    WITH MOVE N'Kumeo_C' TO N'{thuMuc}\Kumeo_C_Lay.mdf', MOVE N'Kumeo_C_log' TO N'{thuMuc}\Kumeo_C_Lay_log.ldf';
    """);
await using (var cn = new SqlConnection(MayKhac.Replace("Database=master", "Database=Kumeo_C_Lay")))
{
    await cn.OpenAsync();
    var dem = await new SqlCommand("SELECT COUNT(*) FROM dbo.KhachHang;", cn).ExecuteScalarAsync();
    var khach42 = await new SqlCommand("SELECT Ten + N', ' + SoDienThoai FROM dbo.KhachHang WHERE KhachHangId = 42;", cn).ExecuteScalarAsync();
    Console.WriteLine($"Restore trên instance khác: {sw.Elapsed.TotalSeconds:N1} s, đọc được {dem:N0} khách; khách 42: {khach42}");
}
SqlConnection.ClearAllPools();
await Exec(MayKhac, "DROP DATABASE Kumeo_C_Lay;");
File.Delete(bak);

// 4. Thử tạo database encryption key trên instance riêng (Express) để xem nó báo gì.
try
{
    var matKhau = Convert.ToBase64String(RandomNumberGenerator.GetBytes(24)); // chỉ dùng cho instance thử, không lưu lại
    await Exec(MayKhac, $"""
        IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
            CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'{matKhau}';
        IF CERT_ID(N'TdeCert_Thu') IS NULL CREATE CERTIFICATE TdeCert_Thu WITH SUBJECT = N'TDE thử';
        IF DB_ID(N'Kumeo_C_Tde') IS NULL CREATE DATABASE Kumeo_C_Tde;
        """);
    await Exec(MayKhac.Replace("Database=master", "Database=Kumeo_C_Tde"),
        "CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TdeCert_Thu;");
    Console.WriteLine("CREATE DATABASE ENCRYPTION KEY: chạy được");
}
catch (SqlException ex) { Console.WriteLine($"CREATE DATABASE ENCRYPTION KEY: lỗi {ex.Number}, {ex.Message}"); }
finally
{
    SqlConnection.ClearAllPools();
    await Exec(MayKhac, "IF DB_ID(N'Kumeo_C_Tde') IS NOT NULL DROP DATABASE Kumeo_C_Tde; IF CERT_ID(N'TdeCert_Thu') IS NOT NULL DROP CERTIFICATE TdeCert_Thu; DROP MASTER KEY;");
}

// Đếm số điện thoại xuất hiện nguyên văn trong mảng byte: dò mọi chuỗi "09" + 8 chữ số.
static int DoSoDienThoai(byte[] b, HashSet<string> can)
{
    var thay = new HashSet<string>();
    for (var i = 0; i + 10 <= b.Length; i++)
    {
        if (b[i] != (byte)'0' || b[i + 1] != (byte)'9') continue;
        var ok = true;
        for (var j = 2; j < 10 && ok; j++) ok = b[i + j] >= (byte)'0' && b[i + j] <= (byte)'9';
        if (!ok) continue;
        var s = Encoding.ASCII.GetString(b, i, 10);
        if (can.Contains(s)) thay.Add(s);
    }
    return thay.Count;
}

static async Task<string> TrangThaiTde(string cs)
{
    await using var cn = new SqlConnection(cs);
    await cn.OpenAsync();
    await using var cmd = new SqlCommand("""
        SELECT DB_NAME(k.database_id), k.encryption_state, c.name, c.pvt_key_last_backup_date
        FROM sys.dm_database_encryption_keys AS k
        LEFT JOIN master.sys.certificates AS c ON c.thumbprint = k.encryptor_thumbprint
        WHERE k.database_id = DB_ID();
        """, cn);
    await using var r = await cmd.ExecuteReaderAsync();
    if (!await r.ReadAsync()) return $"{cn.Database}: chưa có database encryption key, file dữ liệu và backup không được mã hóa";
    var daSaoLuu = !r.IsDBNull(3);
    return $"{r.GetString(0)}: encryption_state = {r.GetInt32(1)} (3 là đã mã hóa), certificate {r.GetString(2)}, "
        + (daSaoLuu ? $"sao lưu khóa riêng lần cuối {r.GetDateTime(3):yyyy-MM-dd HH:mm}" : "CHƯA sao lưu khóa riêng");
}

static async Task Exec(string cs, string sql)
{
    await using var cn = new SqlConnection(cs);
    await cn.OpenAsync();
    await new SqlCommand(sql, cn).ExecuteNonQueryAsync();
}

Kumeo_C: chưa có database encryption key, file dữ liệu và backup không được mã hóa
File Kumeo_C_full.bak: 286 MB, dò trong 4,5 s, thấy 200.000/200.000 số điện thoại hiện tại dạng chữ thường
Restore trên instance khác: 3,5 s, đọc được 200.000 khách; khách 42: Đại lý A - Chi nhánh 3, 0900000298
CREATE DATABASE ENCRYPTION KEY: lỗi 33117, Transparent Data Encryption is not available in the edition of this SQL Server instance. See books online for more details on feature support in different SQL Server editions.

6. Chứng minh: đo được rủi ro trước khi bật, chưa đo được sau khi bật #

Môi trường: LocalDB SQL Server 2019 Express 15.0.4382 cho database nguồn Kumeo_C (2.000.000 đơn, 200.000 khách, bảng lịch sử 1.000.000 dòng), và một instance LocalDB riêng cùng phiên bản đóng vai máy của người nhặt được file.

Cột "Có TDE" để trống vì LocalDB từ chối tạo database encryption key với lỗi 33117. Muốn hoàn tất bảng, chạy lại KiemTraTde.cs trên SQL Server 2019 Developer hoặc Standard sau khi bật TDE cho database nguồn. Kết quả cần thấy: dòng dò chuỗi báo 0 số, lệnh restore trên instance không có certificate ném lỗi 33111, và dòng trạng thái báo encryption_state = 3 kèm ngày sao lưu khóa riêng.

Bài cũng không đo chi phí CPU của TDE. Tài liệu không đưa con số chung; đo trên tải thật của BanHang trước khi bật trên production.

7. Kết luận #

TDE làm file rời khỏi máy chủ trở nên vô dụng với người khác, và cũng vô dụng với chính mình nếu mất certificate. Bật TDE và sao lưu certificate là một thao tác, không phải hai.

Trong dự án .NET của bạn:

• Thêm health check đọc sys.dm_database_encryption_keys và pvt_key_last_backup_date, báo đỏ khi encryption_state khác 3 hoặc khóa riêng chưa từng được sao lưu.
• Không đưa mật khẩu master key hay file khóa vào cấu hình của ứng dụng; ứng dụng không cần chúng để chạy.
• Khi dựng môi trường thử cho đối tác từ backup production, restore trên máy có certificate rồi xóa hoặc che dữ liệu khách trước khi giao.
• Đặt lịch diễn tập: nạp certificate vào máy dự phòng và restore bản backup mới nhất.

Những chỗ hay hiểu sai #

• "TDE chặn người đọc trộm dữ liệu." TDE chặn người cầm file; người có quyền SELECT vẫn đọc bình thường.
• "Certificate nằm trong file backup." Certificate nằm trong master; backup của BanHang không mang theo nó.
• "Certificate hết hạn thì TDE ngừng." Tài liệu ghi certificate quá hạn vẫn mã hóa và giải mã được cho TDE.
• "Trên SQL Server 2019, TDE chỉ có ở Enterprise." Standard 2019 cũng có; Web và Express thì không.

Đọc tiếp #

• Bài trước: Temporal table: khách 42 tên gì lúc lập hóa đơn.
• Bài sau: Máy chủ chính chết giữa giờ cao điểm.
• Người đã đăng nhập thì chặn bằng gì: Phân quyền bảng lương.
• Chuỗi backup và lịch 22:00: Xóa nhầm lúc 12:07, khôi phục về đúng 12:06.
• Page 8 KB và byte của một dòng: SELECT * kéo cả file PDF 2 MB.

Nguồn #

• Transparent data encryption: chuỗi khóa, TDE và backup, tempdb, FILESTREAM, lượt quét, câu kiểm tra pvt_key_last_backup_date, certificate quá hạn.
• Editions and supported features of SQL Server 2019: TDE và mã hóa backup ở Enterprise, Standard.
• Error after you perform TDE certificate or key rotation: lỗi 33111 khi thiếu certificate.
• BACKUP CERTIFICATE và CREATE CERTIFICATE