Crittografia di una colonna di datiEncrypt a Column of Data

QUESTO ARGOMENTO SI APPLICA A:sìSQL Server (a partire dalla versione 2008)sìDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

In questo argomento viene illustrato come crittografare una colonna di dati tramite la crittografia simmetrica in SQL Server 2017SQL Server 2017 tramite Transact-SQLTransact-SQL.This topic describes how to encrypt a column of data by using symmetric encryption in SQL Server 2017SQL Server 2017 using Transact-SQLTransact-SQL. È chiamata a volte crittografia a livello di colonna o crittografia a livello di cella.This is sometimes known as column-level encryption, or cell-level encryption.

Contenuto dell'argomentoIn This Topic

Prima di iniziare Before You Begin

Sicurezza Security

Autorizzazioni Permissions

Le autorizzazioni seguenti sono necessarie per eseguire i passaggi riportati di seguito:The following permissions are necessary to perform the steps below:

  • Autorizzazione CONTROL per il database.CONTROL permission on the database.

  • Autorizzazione CREATE CERTIFICATE per il database.CREATE CERTIFICATE permission on the database. Solo gli account di accesso di Windows e di SQL Server e i ruoli applicazione possono disporre di certificati.Only Windows logins, SQL Server logins, and application roles can own certificates. I gruppi e i ruoli non possono disporre di certificati.Groups and roles cannot own certificates.

  • Autorizzazione ALTER per la tabella.ALTER permission on the table.

  • È necessario disporre di un'autorizzazione per la chiave e che non venga negata l'autorizzazione VIEW DEFINITION.Some permission on the key and must not have been denied VIEW DEFINITION permission.

Utilizzo di Transact-SQL Using Transact-SQL

Per usare gli esempi seguenti è necessaria una chiave master di database.To use the following examples you must have a database master key. Se il database in uso non contiene una chiave master, crearne una eseguendo l'istruzione riportata di seguito specificando la propria password:If your database does not already have a database master key, create one by executing the following statement providing your password:

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

Creare sempre una copia di backup della chiave master del database.Always backup your database master key. Per altre informazioni sulla creazione di chiavi master di database, vedere CREATE MASTER KEY (Transact-SQL).For more information on database master keys, see CREATE MASTER KEY (Transact-SQL).

Per crittografare una colonna di dati utilizzando una crittografia simmetrica sempliceTo encrypt a column of data using a simple symmetric encryption

  1. In Esplora oggetticonnettersi a un'istanza del Motore di databaseDatabase Engine.In Object Explorer, connect to an instance of Motore di databaseDatabase Engine.

  2. Sulla barra Standard fare clic su Nuova query.On the Standard bar, click New Query.

  3. Copiare e incollare l'esempio seguente nella finestra Query, quindi fare clic su Esegui.Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;  
    
    GO  
    
    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(128);   
    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('SHA1', 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('SHA1', CONVERT(varbinary, CreditCardID))))  
        AS 'Decrypted card number' FROM Sales.CreditCard;  
    GO  
    

Per crittografare una colonna di dati utilizzando la crittografia simmetrica che include un autenticatoreTo encrypt a column of data using symmetric encryption that includes an authenticator

  1. In Esplora oggetticonnettersi a un'istanza del Motore di databaseDatabase Engine.In Object Explorer, connect to an instance of Motore di databaseDatabase Engine.

  2. Sulla barra Standard fare clic su Nuova query.On the Standard bar, click New Query.

  3. Copiare e incollare l'esempio seguente nella finestra Query, quindi fare clic su Esegui.Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;  
    GO  
    
    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 [AdventureWorks2012];  
    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  
    

    Per altre informazioni, vedere quanto segue:For more information, see the following: