共用方式為

加密資料行

  • 發行項

適用於：Microsoft Fabric 中的 SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System （PDW） SQL Database

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics

本文描述如何使用 Transact-SQL，在 SQL Server 中透過對稱加密來加密資料行。 這有時候稱為資料行層級加密或資料格層級加密。

本文 Transact-SQL 程式碼範例使用 AdventureWorks2022 或 AdventureWorksDW2022 範例資料庫，從 Microsoft SQL Server Samples 和 Community Projects (Microsoft SQL Server 範例和社群專案) 首頁即可下載。

安全性

權限

需要下列權限，才能執行以下步驟：

  • 資料庫的 CONTROL 權限。
  • 資料庫的 CREATE CERTIFICATE 權限。 只有 Windows 登入、SQL Server 登入，以及應用程式角色可以擁有憑證。 群組和角色無法擁有憑證。
  • 資料表的 ALTER 權限。
  • 金鑰的某種權限，而且不得被拒絕 VIEW DEFINITION 權限。

建立資料庫主要金鑰

若要使用下列範例，您必須有資料庫主要金鑰。 如果您的資料庫還沒有資料庫主要金鑰，請建立一個。 若要建立主要金鑰，請連線到您的資料庫，然後執行下列指令碼。 請務必使用複雜密碼。

將下列範例複製並貼入已連線至 AdventureWorks 範例資料庫的查詢視窗中。 選取 [執行] 。

CREATE MASTER KEY ENCRYPTION BY   
PASSWORD = '<complex password>';

一律備份您的資料庫主要金鑰。 如需資料庫主要金鑰的詳細資訊，請參閱 CREATE MASTER KEY (Transact-SQL)。

範例：使用對稱式加密和驗證器進行加密

  1. 在物件總管中，連線到資料庫引擎的執行個體。

  2. 在標準列上，選取 [新增查詢] 。

  3. 將下列範例複製並貼入已連線至 AdventureWorks 範例資料庫的查詢視窗中。 選取 [執行] 。

    CREATE CERTIFICATE Sales09  
   WITH SUBJECT = 'Customer Credit Card Numbers';  
GO  

CREATE SYMMETRIC KEY CreditCards_Key11  
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE Sales09;  
GO  

-- Create a column in which to store the encrypted data.  
ALTER TABLE Sales.CreditCard   
    ADD CardNumber_Encrypted varbinary(160);   
GO  

-- Open the symmetric key with which to encrypt the data.  
OPEN SYMMETRIC KEY CreditCards_Key11  
   DECRYPTION BY CERTIFICATE Sales09;  

-- Encrypt the value in column CardNumber using the  
-- symmetric key CreditCards_Key11.  
-- Save the result in column CardNumber_Encrypted.    
UPDATE Sales.CreditCard  
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')  
    , CardNumber, 1, HASHBYTES('SHA2_256', CONVERT( varbinary  
    , CreditCardID)));  
GO  

-- Verify the encryption.  
-- First, open the symmetric key with which to decrypt the data.  

OPEN SYMMETRIC KEY CreditCards_Key11  
   DECRYPTION BY CERTIFICATE Sales09;  
GO  

-- Now list the original card number, the encrypted card number,  
-- and the decrypted ciphertext. If the decryption worked,  
-- the original number will match the decrypted number.  

SELECT CardNumber, CardNumber_Encrypted   
    AS 'Encrypted card number', CONVERT(nvarchar,  
    DecryptByKey(CardNumber_Encrypted, 1 ,   
    HASHBYTES('SHA2_256', CONVERT(varbinary, CreditCardID))))  
    AS 'Decrypted card number' FROM Sales.CreditCard;  
GO

使用簡單的對稱式加密進行加密

  1. 在物件總管中，連線到資料庫引擎的執行個體。

  2. 在標準列上，選取 [新增查詢] 。

  3. 將下列範例複製並貼入已連線至 AdventureWorks 範例資料庫的查詢視窗中。 選取 [執行] 。

     CREATE CERTIFICATE HumanResources037  
   WITH SUBJECT = 'Employee Social Security Numbers';  
GO  

CREATE SYMMETRIC KEY SSN_Key_01  
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE HumanResources037;  
GO  

USE [AdventureWorks2022];  
GO  

-- Create a column in which to store the encrypted data.  
ALTER TABLE HumanResources.Employee  
    ADD EncryptedNationalIDNumber varbinary(128);   
GO  

-- Open the symmetric key with which to encrypt the data.  
OPEN SYMMETRIC KEY SSN_Key_01  
   DECRYPTION BY CERTIFICATE HumanResources037;  

-- Encrypt the value in column NationalIDNumber with symmetric   
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.  
UPDATE HumanResources.Employee  
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);  
GO  

-- Verify the encryption.  
-- First, open the symmetric key with which to decrypt the data.  
OPEN SYMMETRIC KEY SSN_Key_01  
   DECRYPTION BY CERTIFICATE HumanResources037;  
GO  

-- Now list the original ID, the encrypted ID, and the   
-- decrypted ciphertext. If the decryption worked, the original  
-- and the decrypted ID will match.  
SELECT NationalIDNumber, EncryptedNationalIDNumber   
    AS 'Encrypted ID Number',  
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))   
    AS 'Decrypted ID Number'  
    FROM HumanResources.Employee;  
GO

下一步