Esercitazione: Introduzione a Always Encrypted

1. 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).

2. Apri una nuova finestra di query per il database ContosoHR.

3. 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];
   

4. 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" -MinimumVersion 22.0.50

# Connect to your database
# Set the valid server name, database name and authentication keywords in the connection string
$serverName = "<server name>.database.windows.net"
$databaseName = "ContosoHR"
$connStr = "Server=tcp:$serverName,1433;Database=$databaseName;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Interactive"

try {
	$database = Get-SqlDatabase -ConnectionString $connStr -Encrypt Mandatory -ErrorAction Stop
}
catch {
	Write-Error "Failed to connect. Verify server name, database name, Azure SQL firewall access, and Microsoft Entra permissions."
	throw
}


$query = @'
 IF SCHEMA_ID(N'HR') IS NULL
BEGIN
    EXEC(N'CREATE SCHEMA [HR]');
END;

IF OBJECT_ID(N'HR.Employees', N'U') IS NULL
BEGIN
    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,
        CONSTRAINT [PK_HR_Employees] PRIMARY KEY CLUSTERED ([EmployeeID])
    );
END;
'@

#write-host @query

Invoke-SqlCmd -ConnectionString $connStr -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 $connStr -Query $query

Write-Host 'Completed successfully.'

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.

1. In Esplora oggetti, espandi Database>ContosoHR>Tabelle.

2. Fare clic con il pulsante destro del mouse sulla tabella Dipendenti e selezionare Crittografa colonne per aprire la procedura guidata Always Encrypted.

   

3. Nella pagina Introduzione della procedura guidata, selezionare Avanti.

4. Nella pagina Selezione della 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 consente query, ad esempio ricerche puntuali basate su 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.

   

5. Nella pagina Configurazione della chiave master, configurare una nuova chiave master di 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 Azure Key Vault.
     2. Selezionare Accedi e completare l'accesso ad Azure.
     3. Dopo aver eseguito l'accesso, la pagina visualizzerà l'elenco delle sottoscrizioni e degli insiemi di credenziali delle chiavi a cui puoi accedere. Selezionare una sottoscrizione di Azure che contenga l'insieme di credenziali da utilizzare.
     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. Lascia la selezione predefinita di Utente corrente - in questo modo la procedura guidata genererà un certificato (la nuova chiave master di colonna) nell'archivio Utente corrente.

        

     3. Selezionare Avanti.

6. Nella pagina Impostazioni della crittografia sul posto, non è necessaria alcuna configurazione aggiuntiva perché il database non dispone di un'enclave abilitata. Selezionare Avanti.

7. 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.

8. Nella pagina Riepilogo, la procedura guidata ti informa su quali azioni verranno eseguite. Controllare che tutte le informazioni siano corrette e selezionare Fine.

9. Nella pagina Risultati, è possibile monitorare l'avanzamento delle operazioni della procedura guidata. Attendere il completamento di tutte le operazioni e selezionare Chiudi.

   

10. (Facoltativo) Esplora le modifiche apportate dalla procedura guidata nel tuo database.

    1. Espandi ContosoHR>Sicurezza>Chiavi Always Encrypted per esplorare gli oggetti dei metadati relativi alla chiave master di colonna e alla crittografia di colonna create 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 Employees e selezionare Script Table as>CREATE To>New Editor di query Window. Verrà aperta una nuova finestra di query con l'istruzione CREATE TABLE per la tabella Employees . 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;
       

1. Crea una chiave master di colonna nell'archivio chiavi.

   • Se si utilizza Azure Key Vault, eseguire i comandi seguenti per creare una chiave asimmetrica nel proprio insieme di credenziali. Assicurati di fornire l'ID corretto della sottoscrizione, il nome del gruppo di risorse che contiene l'insieme di credenziali delle chiavi e il nome dell'insieme di credenziali delle chiavi.

     [CmdletBinding()]
       param(
           [Parameter(Mandatory = $true)]
           [ValidateNotNullOrEmpty()]
           [string]$SubscriptionId,
     
           [Parameter(Mandatory = $true)]
           [ValidateNotNullOrEmpty()]
           [string]$ResourceGroupName,
     
           [Parameter(Mandatory = $true)]
           [ValidateNotNullOrEmpty()]
           [string]$KeyVaultName,
     
           [Parameter(Mandatory = $true)]
           [ValidateNotNullOrEmpty()]
           [string]$KeyVaultKeyName,
     
           [Parameter(Mandatory = $false)]
           [ValidateSet('RSA', 'RSA-HSM', 'EC', 'EC-HSM', 'oct')]
           [string]$KeyType = 'RSA',
     
           [Parameter(Mandatory = $false)]
           [ValidateSet('Software', 'HSM')]
           [string]$Destination = 'Software'
       )
     
       Set-StrictMode -Version Latest
       $ErrorActionPreference = 'Stop'
     
       Import-Module Az.Accounts -ErrorAction Stop
       Import-Module Az.KeyVault -ErrorAction Stop
       Import-Module Az.Resources -ErrorAction Stop
     
       Write-Host "[AKV] Signing in to Azure"
       Connect-AzAccount -ErrorAction Stop | Out-Null
     
       Write-Host "[AKV] Setting subscription context to '$SubscriptionId'"
       Set-AzContext -SubscriptionId $SubscriptionId -ErrorAction Stop | Out-Null
     
       Write-Host "[AKV] Validating Key Vault '$KeyVaultName' in resource group '$ResourceGroupName'"
       $vault = Get-AzKeyVault -VaultName $KeyVaultName -ResourceGroupName $ResourceGroupName -ErrorAction Stop
     
       Write-Host "[AKV] Checking if key '$KeyVaultKeyName' already exists"
       $existingKey = Get-AzKeyVaultKey -VaultName $KeyVaultName -Name $KeyVaultKeyName -ErrorAction SilentlyContinue
     
       if ($existingKey) {
           Write-Host "[AKV] Key already exists. Returning existing key metadata."
           $existingKey
           return
       }
     
       Write-Host "[AKV] Creating key '$KeyVaultKeyName' (Type=$KeyType, Destination=$Destination)"
       $keyVaultKey = Add-AzKeyVaultKey `
           -VaultName $KeyVaultName `
           -Name $KeyVaultKeyName `
           -Destination $Destination `
           -KeyType $KeyType `
           -ErrorAction Stop
     
       Write-Host '[AKV] Key created successfully.'
       $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
     

2. Connettersi al database usando il modulo SqlServer PowerShell. Assicurarsi di fornire una stringa di connessione valido per il database. Creare un oggetto metadati della chiave master della colonna che fa riferimento alla chiave master della colonna fisica creata nell'archivio delle chiavi.

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

     Write-Host "[AE] Connecting to Azure SQL and creating metadata"
     $keyVaultAccessToken = (Get-AzAccessToken -ResourceUrl "https://vault.azure.net").Token
     
     $serverName = "<server name>.database.windows.net"
     $databaseName = "ContosoHR"
     $connStr = "Server=tcp:$serverName,1433;Database=$databaseName;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Interactive"
     
     try {
         $database = Get-SqlDatabase -ConnectionString $connStr -Encrypt Mandatory -ErrorAction Stop
     }
     catch {
         Write-Error "Failed to connect. Verify server name, database name, Azure SQL firewall access, and Microsoft Entra permissions."
         throw
     }
     
     $cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyURL $keyVaultKey.Key.Kid
     
     $existingCmk = Get-SqlColumnMasterKey -InputObject $database | Where-Object { $_.Name -eq $CmkName }
     if (-not $existingCmk) {
         New-SqlColumnMasterKey -Name $CmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings | Out-Null
     }
     

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

     Write-Host "[AE] Connecting to Azure SQL and creating metadata"
     $serverName = "<server name>.database.windows.net"
     $databaseName = "ContosoHR"
     $connStr = "Server=tcp:$serverName,1433;Database=$databaseName;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Interactive"
     
     try {
         $database = Get-SqlDatabase -ConnectionString $connStr -Encrypt Mandatory -ErrorAction Stop
     }
     catch {
         Write-Error "Failed to connect. Verify server name, database name, Azure SQL firewall access, and Microsoft Entra permissions."
         throw
     }
     
     $cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation "CurrentUser" -Thumbprint $cert.Thumbprint
     
     $existingCmk = Get-SqlColumnMasterKey -InputObject $database | Where-Object { $_.Name -eq $CmkName }
     if (-not $existingCmk) {
         New-SqlColumnMasterKey -Name $CmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings | Out-Null
     }
     

3. 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.

   $existingCek = Get-SqlColumnEncryptionKey -InputObject $database | Where-Object { $_.Name -eq $CekName }
    if (-not $existingCek) {
    	New-SqlColumnEncryptionKey -Name $CekName -InputObject $database -ColumnMasterKey $CmkName -KeyVaultAccessToken $keyVaultAccessToken | Out-Null
    }
   

4. 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 consente query, ad esempio ricerche puntuali basate su 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 .
   
   

5. (Facoltativo) Controllare le modifiche apportate nel database.

   • Esegui i comandi seguenti per interrogare le viste del catalogo di sistema che contengono metadati sulla chiave master della colonna e sulla chiave di crittografia della colonna che hai creato.

     $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
     

1. Connettiti al database con Always Encrypted disabilitato per questa 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. Assicurarsi che Abilita Always Encrypted (crittografia della colonna) non sia selezionata.
   6. Selezionare Connetti.

   

2. Incolla ed esegui la query seguente. La query deve restituire dati crittografati binari.

   SELECT [SSN], [Salary] FROM [HR].[Employees]
   

   

3. Connettiti al database con Always Encrypted abilitato per la tua 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.

   

4. Esegui 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.

   

5. 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.

   

6. 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
   

7. 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, vedi Assegnare un criterio di accesso a Key Vault.
   2. Poiché il driver client in SSMS memorizza nella cache per 2 ore le chiavi di crittografia delle colonne ottenute da un key vault, chiudere e riaprire SSMS. In questo modo, si garantisce che la cache delle chiavi sia vuota.
   3. Connettiti al database con Always Encrypted abilitato per la tua connessione.
   4. Incolla ed esegui la query seguente. 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]
   

1. 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
   

2. Connetti al tuo database con Always Encrypted abilitato ed esegui 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