Condividi tramite

Crittografia di una colonna di dati

  • Articolo

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsAnalytics Platform System (PDW)Database SQL in Microsoft Fabric

Si applica a: SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse Analytics

Questo articolo descrive come crittografare una colonna di dati tramite la crittografia simmetrica in SQL Server con Transact-SQL. È chiamata a volte crittografia a livello di colonna o crittografia a livello di cella.

Gli esempi di codice in questo articolo usano il database di esempio AdventureWorks2022 o AdventureWorksDW2022, che è possibile scaricare dalla home page Microsoft SQL Server Samples and Community Projects.

Sicurezza

Autorizzazioni

Le autorizzazioni seguenti sono necessarie per eseguire i passaggi riportati di seguito:

  • Autorizzazione CONTROL per il database.
  • Autorizzazione CREATE CERTIFICATE per il database. Solo gli account di accesso di Windows e di SQL Server e i ruoli applicazione possono disporre di certificati. I gruppi e i ruoli non possono disporre di certificati.
  • Permesso ALTER sulla tabella.
  • È necessario disporre di un'autorizzazione per la chiave e che non venga negata l'autorizzazione VIEW DEFINITION.

Creare la chiave master del database

Per usare gli esempi seguenti è necessaria una chiave master di database. Se il database non dispone già di una chiave master del database, crearne una. Per crearne uno, collegati al tuo database ed esegui lo script seguente. Assicurarsi di usare una password complessa.

Copiare e incollare il seguente esempio nella finestra di query connessa al database di esempio AdventureWorks. Seleziona Execute.

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

Creare sempre una copia di backup della chiave master del database. Per ulteriori informazioni sulle chiavi master del database, vedere CREATE MASTER KEY (Transact-SQL).

Esempio: Crittografare con la crittografia simmetrica e l'autenticatore

  1. In Esplora oggetti, connettersi a un'istanza del motore di database.

  2. Sulla barra Standard selezionare Nuova query.

  3. Copia e incolla l'esempio riportato di seguito nella finestra delle query connessa al database di esempio AdventureWorks. Seleziona Execute.

    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

Crittografare con crittografia simmetrica semplice

  1. In Esplora oggetti, connettersi a un'istanza del Motore di Database.

  2. Sulla barra Standard selezionare Nuova query.

  3. Copia e incolla il seguente esempio nella finestra di query connessa al database di esempio AdventureWorks. Seleziona Execute.

     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

Passaggi successivi