Esercitazione: Introduzione a Always Encrypted

Articolo
03/22/2024

Si applica a:SQL Server Database SQL di Azure Istanza gestita di SQL di Azure

Questa esercitazione illustra come iniziare a usare Always Encrypted. L'esercitazione spiega:

Come crittografare le colonne selezionate nel database.
Come eseguire query su colonne crittografate.

Nota

Per informazioni su Always Encrypted con enclave sicure, vedere invece le esercitazioni seguenti:

Prerequisiti

Per eseguire questa esercitazione, è necessario avere:

Un database vuoto in Database SQL di Azure, Istanza gestita di SQL di Azure o SQL Server. Le istruzioni seguenti presuppongono che il nome del database sia ContosoHR. È necessario essere un proprietario del database (membro del ruolo db_owner). Per informazioni su come creare un database, vedere Avvio rapido: Creare un database singolo - Database SQL di Azure o Creare un database in SQL Server.
Facoltativo, ma consigliato, soprattutto se il database si trova in Azure: un insieme di credenziali delle chiavi in Azure Key Vault. Per ulteriori informazioni su come creare un insieme di credenziali delle chiavi, vedere Guida introduttiva: creare un insieme di credenziali delle chiavi usando il portale di Azure.
- Se l'insieme di credenziali delle chiavi usa il modello di autorizzazioni dei criteri di accesso, assicurarsi di disporre delle autorizzazioni di chiave seguenti nell'insieme di credenziali delle chiavi: get, list, create, unwrap key, wrap key, verify, sign. Vedere Assegnare criteri di accesso a Key Vault.
- Se si usa il modello di autorizzazione del controllo degli accessi in base al ruolo (RBAC) di Azure, assicurarsi di essere un membro del ruolo Agente di crittografia di Key Vault per l'insieme di credenziali delle chiavi. Vedere Fornire l'accesso a chiavi, certificati e segreti di Key Vault con un controllo degli accessi in base al ruolo.
L'ultima versione di SQL Server Management Studio (SSMS) o l'ultima versione dei moduli di PowerShell SqlServer e Az. Il modulo Az di PowerShell è necessario solo se si usa Azure Key Vault.

Passaggio 1: Creare e popolare lo schema del database

In questo passaggio, si creeranno lo schema HR e la tabella Dipendenti . Verrà quindi popolata la tabella con alcuni dati.

SSMS
PowerShell

Connettersi al database. Per istruzioni su come connettersi a un database da SSMS, vedere Guida introduttiva: Connettersi ed eseguire query su un database SQL di Azure o su un'istanza gestita di SQL di Azure usando SQL Server Management Studio (SSMS) o Guida introduttiva: Connettersi ed eseguire query su un'istanza di SQL Server usando SQL Server Management Studio (SSMS).
Aprire un nuovo intervallo di query per il database ContosoHR .

Incollare ed eseguire le istruzioni seguenti per creare una nuova tabella, denominata Dipendenti.

CREATE SCHEMA [HR];
GO

CREATE TABLE [HR].[Employees]
(
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL
    , [SSN] [char](11) NOT NULL
    , [FirstName] [nvarchar](50) NOT NULL
    , [LastName] [nvarchar](50) NOT NULL
    , [Salary] [money] NOT NULL
) ON [PRIMARY];

Incollare ed eseguire le istruzioni seguenti per aggiungere alcuni record dei dipendenti nella tabella Dipendenti.

INSERT INTO [HR].[Employees]
(
    [SSN]
    , [FirstName]
    , [LastName]
    , [Salary]
)
VALUES
(
    '795-73-9838'
    , N'Catherine'
    , N'Abel'
    , $31692
);

INSERT INTO [HR].[Employees]
(
    [SSN]
    , [FirstName]
    , [LastName]
    , [Salary]
)
VALUES
(
    '990-00-6818'
    , N'Kim'
    , N'Abercrombie'
    , $55415
);

In una sessione di PowerShell, eseguire i comandi seguenti. Assicurarsi di aggiornare la stringa di connessione con l'indirizzo del server e le impostazioni di autenticazione valide per il database.

Import-Module "SqlServer"

# Set your database connection string
$connectionString = "Server = myServerAddress; Database = ContosoHR; ..."

# Create a new table, named Employees.
$query = @'
    CREATE SCHEMA [HR];
    GO
    
    CREATE TABLE [HR].[Employees]
    (
        [EmployeeID] [int] IDENTITY(1,1) NOT NULL
        , [SSN] [char](11) NOT NULL
        , [FirstName] [nvarchar](50) NOT NULL
        , [LastName] [nvarchar](50) NOT NULL
        , [Salary] [money] NOT NULL
    ) ON [PRIMARY];
'@
Invoke-SqlCmd -ConnectionString $connectionString -Query $query

# Add a few rows to the Employees table.
$query = @'
    INSERT INTO [HR].[Employees]
    (
        [SSN]
        , [FirstName]
        , [LastName]
        , [Salary]
    )
    VALUES
    (
        '795-73-9838'
        , N'Catherine'
        , N'Abel'
        , $31692
    );

    INSERT INTO [HR].[Employees]
    (
        [SSN]
        , [FirstName]
        , [LastName]
        , [Salary]
    )
    VALUES
    (
        '990-00-6818'
        , N'Kim'
        , N'Abercrombie'
        , $55415
    );
'@
Invoke-SqlCmd -ConnectionString $connectionString -Query $query

Passaggio 2: Crittografare le colonne

In questo passaggio, si eseguirà il provisioning di una chiave master della colonna e di una chiave di crittografia della colonna per Always Encrypted. Verranno quindi crittografate le colonne SSN e Stipendio nella tabella Dipendenti .

SSMS
PowerShell

SSMS offre una procedura guidata per configurare facilmente Always Encrypted impostando la chiave master della colonna e la chiave di crittografia della colonna e crittografando le colonne selezionate.

In Esplora oggetti, espandere Databases>ContosoHR>Tabelle.
Fare clic con il pulsante destro del mouse sulla tabella Dipendenti e selezionare Crittografa colonne per aprire la procedura guidata Always Encrypted.
Nella pagina Introduzione della procedura guidata, selezionare Avanti.
Nella pagina Selezione colonna.
1. Selezionare le colonne SSN e Stipendio. Scegliere la crittografia deterministica per la colonna SSN e la crittografia casuale per la colonna Stipendio. La crittografia deterministica supporta le query, ad esempio le ricerche di punti che comportano confronti di uguaglianza sulle colonne crittografate. La crittografia casuale non supporta alcun calcolo sulle colonne crittografate.
2. Lasciare CEK-Auto1 (New) come chiave di crittografia della colonna per entrambe le colonne. Questa chiave non esiste ancora e verrà generata dalla procedura guidata.
3. Selezionare Avanti.
Nella pagina Configurazione chiave master, configurare una nuova chiave master della colonna che verrà generata dalla procedura guidata. Prima di tutto, è necessario selezionare la posizione in cui archiviare la chiave master della colonna. La procedura guidata supporta due tipi di archivio di chiavi:
- Azure Key Vault: consigliato se il database si trova in Azure
- Archivio certificati Windows
In generale, Azure Key Vault è l'opzione consigliata, soprattutto se il database si trova in Azure.
- Per usare Azure Key Vault:
  1. Selezionare Insieme di credenziali delle chiavi di Azure.
  2. Selezionare Accedi e completare l'accesso ad Azure.
  3. Dopo aver eseguito l'accesso, nella pagina verrà visualizzato l'elenco di sottoscrizioni e insiemi di credenziali delle chiavi a cui si ha accesso. Selezionare la sottoscrizione di Azure che contiene l'insieme di credenziali delle chiavi da usare.
  4. Selezionare l'insieme di credenziali delle chiavi.
  5. Selezionare Avanti.
- Per usare l'archivio certificati di Windows:
  1. Selezionare Archivio certificati di Windows.
  2. Lasciare la selezione predefinita di Utente corrente. In questo modo, verrà indicato alla procedura guidata di generare un certificato (la nuova chiave master della colonna) nell'archivio Utente corrente.
  3. Selezionare Avanti.
Nella pagina Impostazioni della crittografia sul posto, non è necessaria alcuna configurazione aggiuntiva perché il database non dispone di un'enclave abilitata. Selezionare Avanti.
Nella pagina Impostazioni esecuzione test, viene chiesto se si vuole procedere con la crittografia o generare uno script di PowerShell da eseguire in un secondo momento. Lasciare le impostazioni predefinite e selezionare Avanti.
Nella pagina Riepilogo, la procedura guidata comunica le azioni che verranno eseguite. Controllare che tutte le informazioni siano corrette e selezionare Fine.
Nella pagina Risultati è possibile monitorare lo stato delle operazioni della procedura guidata. Attendere il completamento di tutte le operazioni e selezionare Chiudi.
(Facoltativo) Esplorare le modifiche apportate dalla procedura guidata nel database.
1. Espandere ContosoHR>Sicurezza>Chiavi Always Encrypted per esplorare gli oggetti metadati per la chiave master della colonna e la crittografia della colonna creata dalla procedura guidata.
2. È anche possibile eseguire le query seguenti sulle viste del catalogo di sistema che contengono metadati chiave.
```
SELECT * FROM sys.column_master_keys;
SELECT * FROM sys.column_encryption_keys
SELECT * FROM sys.column_encryption_key_values
```
3. In Esplora oggetti, fare clic con il pulsante destro del mouse sulla tabella Dipendenti e selezionare Crea script per tabella>Crea in>Nuova finestra editor di query. Verrà aperto un nuovo intervallo di query con l'istruzione CREATE TABLE per la tabella Dipendenti. Si noti la clausola ENCRYPTED WITH visualizzata nelle definizioni delle colonne SSN e Stipendio.
4. È anche possibile eseguire la query seguente su sys.columns per riprendere i metadati di crittografia a livello di colonna per le due colonne crittografate.
```
SELECT
[name]
, [encryption_type]
, [encryption_type_desc]
, [encryption_algorithm_name]
, [column_encryption_key_id]
FROM sys.columns
WHERE [encryption_type] IS NOT NULL;
```

Creare una chiave master della colonna in un archivio di chiavi.

Se si usa Azure Key Vault, eseguire i comandi seguenti per creare una chiave asimmetrica nell'insieme di credenziali delle chiavi. Assicurarsi di specificare l'ID corretto della sottoscrizione, il nome del gruppo di risorse contenente l'insieme di credenziali delle chiavi e il nome dell'insieme di credenziali delle chiavi.

Import-Module "Az"
Connect-AzAccount
$subscriptionId = "<your Azure subscription ID"
$resourceGroup = "your resource group name containing your key vault"
$keyVaultName = "your vault name"
$keyVaultKeyName = "your key name"

# Switch to your subscription.
Set-AzConteXt -SubscriptionId $subscriptionId

# To validate the above key vault settings, get the key vault properties.
Get-AzKeyVault -VaultName $keyVaultName -ResourceGroupName $resourceGroup 

# Create a key in the key vault.
$keyVaultKey = Add-AzKeyVaultKey -VaultName $keyVaultName -Name $keyVaultKeyName -Destination "Software"
$keyVaultKey

Se si usa l'archivio certificati di Windows, eseguire i comandi seguenti per creare un certificato nell'archivio Utente corrente.

$cert = New-SelfSignedCertificate -Subject "HRCMK" -CertStoreLocation Cert:CurrentUser\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage DataEncipherment -KeySpec KeyExchange

Connettersi al database usando il modulo SqlServer di PowerShell. Assicurarsi di fornire una stringa di connessione valido per il database.
```
$database = Get-SqlDatabase -ConnectionString $connectionString
$database
```

Effettuare il provisioning di un oggetto di metadati della chiave master della colonna (che fa riferimento alla chiave master della colonna fisica creata nell'archivio di chiavi) nel database.

Se si usa Azure Key Vault, eseguire i comandi seguenti.

# Sign in to Azure for the SqlServer PowerShell module
Add-SqlAzureAuthenticationContext -Interactive

# Create a SqlColumnMasterKeySettings in-memory object referencing the key you've created in your key vault. 
$cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyURL $keyVaultKey.Key.Kid

# Create column master key metadata object (referencing your certificate), named CMK1, in the database.
$cmkName = "CMK1"
New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings

Se si usa l'archivio certificati di Windows, eseguire i comandi seguenti.

# Create a SqlColumnMasterKeySettings in-memory object referencing your certificate.
$cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation "CurrentUser" -Thumbprint $cert.Thumbprint

# Create column master key metadata object, named CMK1, in the database.
$cmkName = "CMK1"
New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings

Generare una nuova chiave di crittografia della colonna, crittografarla con la chiave master della colonna creata e creare l'oggetto di metadati della chiave di crittografia della colonna nel database.
```
$cekName = "CEK1"
New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName
```

Crittografate le colonne SSN e Stipendio nella tabella Dipendenti. Scegliere la crittografia deterministica per la colonna SSN e la crittografia casuale per la colonna Stipendio. La crittografia deterministica supporta le query, ad esempio le ricerche di punti che comportano confronti di uguaglianza sulle colonne crittografate. La crittografia casuale non supporta alcun calcolo sulle colonne crittografate.

# Encrypt the SSN and Salary columns 
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.SSN" -EncryptionType "Deterministic" -EncryptionKey $cekName
$ces += New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.Salary" -EncryptionType "Randomized" -EncryptionKey $cekName
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory .

(Facoltativo) Controllare le modifiche apportate nel database.

Eseguire i comandi seguenti per eseguire query sulle viste del catalogo di sistema contenenti metadati relativi alla chiave master della colonna e alla chiave di crittografia della colonna creati.

$query = @'
SELECT * FROM sys.column_master_keys;
SELECT * FROM sys.column_encryption_keys
SELECT * FROM sys.column_encryption_key_values
'@

Invoke-SqlCmd -ConnectionString $connectionString -Query $query

Eseguire i comandi seguenti relativi alla query sys.columns per riprendere i metadati di crittografia a livello di colonna per le due colonne crittografate.

$query = @'
SELECT
[name]
, [encryption_type]
, [encryption_type_desc]
, [encryption_algorithm_name]
, [column_encryption_key_id]
FROM sys.columns
WHERE [encryption_type] IS NOT NULL;
'@

Invoke-SqlCmd -ConnectionString $connectionString -Query $query

Passaggio 3: Eseguire query su colonne crittografate

SSMS
PowerShell

Connettersi al database con Always Encrypted disabilitato per la connessione.
1. Aprire una nuova finestra Query.
2. Fare clic con il pulsante destro del mouse nell'intervallo di query e selezionare Connessione>Cambia connessione. Si aprirà la finestra di dialogo Connessione al motore di database.
3. Selezionare Opzioni<<. Appariranno delle schede aggiuntive nella finestra di dialogo Connessione al motore di database.
4. Selezionare la scheda Always Encrypted.
5. Verificare che Abilita Always Encrypted (crittografia della colonna) non sia selezionata.
6. Selezionare Connetti.
Incollare la query seguente ed eseguirla. La query deve restituire dati crittografati binari.
```
SELECT [SSN], [Salary] FROM [HR].[Employees]
```
Connettersi al database con Always Encrypted abilitato per la connessione.
1. Fare clic con il pulsante destro del mouse nell'intervallo di query e selezionare Connessione>Cambia connessione. Si aprirà la finestra di dialogo Connessione al motore di database.
2. Selezionare Opzioni<<. Appariranno delle schede aggiuntive nella finestra di dialogo Connessione al motore di database.
3. Selezionare la scheda Always Encrypted.
4. Selezionare Abilita Always Encrypted (crittografia della colonna).
5. Selezionare Connetti.
Eseguire nuovamente la query. Poiché si è connessi con Always Encrypted abilitato per la connessione al database, il driver client in SSMS tenterà di decriptare i dati archiviati in entrambe le colonne crittografate. Se si usa Azure Key Vault, potrebbe essere richiesto di accedere ad Azure.
Abilitare Parametrizzazione per Always Encrypted. Questa funzionalità consente di eseguire query che filtrano i dati in base alle colonne crittografate (o inseriscono i dati in colonne crittografate).
1. Selezionare Query dal menu principale di SQL Server Management Studio.
2. Selezionare Opzioni query.
3. Passare a Esecuzione>Avanzata.
4. Assicurarsi che Abilita parametrizzazione per Always Encrypted sia abilitata.
5. Seleziona OK.
Incollare ed eseguire la seguente query, che filtra i dati in base alla colonna SSN crittografata. La query deve restituire una riga contenente valori di testo non crittografato.
```
DECLARE @SSN [char](11) = '795-73-9838'
SELECT [SSN], [Salary] FROM [HR].[Employees]
WHERE [SSN] = @SSN
```
Facoltativamente, se si usa Azure Key Vault configurato con il modello di autorizzazioni dei criteri di accesso, seguire questa procedura per vedere cosa accade quando un utente tenta di riprendere dati di testo non crittografato da colonne crittografate senza avere accesso alla chiave master della colonna che protegge i dati.
1. Rimuovere l'autorizzazione della chiave unwrap per se stessi nei criteri di accesso per l'insieme di credenziali delle chiavi. Per ulteriori informazioni, vedere Assegnare un criterio di accesso di Key Vault.
2. Poiché il driver client in SSMS memorizza nella cache le chiavi di crittografia della colonna acquisite da un insieme di credenziali delle chiavi per 2 ore, chiudere SSMS e aprirlo di nuovo. In questo modo, si garantisce che la cache delle chiavi sia vuota.
3. Connettersi al database con Always Encrypted abilitato per la connessione.
4. Incollare la query seguente ed eseguirla. La query dovrebbe non riuscire e mostrare il messaggio di errore che indica che manca l'autorizzazione unwrap richiesta.
```
SELECT [SSN], [Salary] FROM [HR].[Employees]
```

Connettersi al database con Always Encrypted disabilitato ed eseguire una query per leggere i dati dalle colonne crittografate. La query deve restituire dati crittografati come matrici binarie.
```
$query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
Invoke-SqlCmd -ConnectionString $connectionString -Query $query
```
Connettersi al database con Always Encrypted abilitato ed eseguire una query per leggere i dati dalle colonne crittografate. Poiché si ha accesso alla chiave master della colonna che protegge le colonne crittografate, la query deve restituire dati di testo non crittografato.
```
$query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
Invoke-SqlCmd -ConnectionString "$connectionString; Column Encryption Setting = Enabled" -Query $query
```

Nota

Invoke-SqlCmd non supporta query che possono filtrare i dati o inserirli in colonne crittografate. Tali query devono essere parametrizzate e Invoke-SqlCmd non supporta query con parametri.

Passaggi successivi

Sviluppare applicazioni usando Always Encrypted