Esercitazione: Creare e usare indici sulle colonne abilitate per l'enclave tramite la crittografia casualeTutorial: Create and use indexes on enclave-enabled columns using randomized encryption

Si applica a:Applies to: sìSQL Server 2019 (15.x)SQL Server 2019 (15.x)yesSQL Server 2019 (15.x)SQL Server 2019 (15.x) - Solo Windows Sìdatabase SQL di AzureAzure SQL DatabaseYesdatabase SQL di AzureAzure SQL DatabaseSi applica a:Applies to: sìSQL Server 2019 (15.x)SQL Server 2019 (15.x)yesSQL Server 2019 (15.x)SQL Server 2019 (15.x) - Windows only Sìdatabase SQL di AzureAzure SQL DatabaseYesdatabase SQL di AzureAzure SQL Database

In questa esercitazione viene illustrato come creare e usare gli indici sulle colonne abilitate per l'enclave tramite la crittografia casuale supportata in Always Encrypted con enclave sicuri.This tutorial teaches you how to create and use indexes on enclave-enabled columns using randomized encryption supported in Always Encrypted with secure enclaves. L'esercitazione spiega:It will show you:

  • Come creare un indice quando si ha accesso alle chiavi (la chiave master della colonna e la chiave di crittografia della colonna) che proteggono la colonna.How to create an index when you have access to the keys (the column master key and the column encryption key) protecting the column.
  • Come creare un indice quando non si ha accesso alle chiavi che proteggono la colonna.How to create an index when you don't have access to the keys protecting the column.

PrerequisitiPrerequisites

Assicurarsi di aver completato una delle esercitazioni riportate di seguito prima di seguire le procedure in questa esercitazione:Make sure you've completed one of the below tutorials before following the below steps in this tutorial:

Passaggio 1: Abilitare il ripristino accelerato del database nel databaseStep 1: Enable Accelerated Database Recovery (ADR) in your database

Nota

Questo passaggio si applica solo a SQL ServerSQL Server.This step applies only to SQL ServerSQL Server. Se si usa database SQL di AzureAzure SQL Database, ignorare questo passaggio.If you're using database SQL di AzureAzure SQL Database, skip this step. Il ripristino accelerato del database viene abilitato automaticamente in database SQL di AzureAzure SQL Database e la disabilitazione non è supportata.ADR is automatically enabled in database SQL di AzureAzure SQL Database and disabling it is not supported.

Microsoft consiglia di abilitare il ripristino accelerato del database nel database prima di creare il primo indice su una colonna abilitata per l'enclave con la crittografia casuale.Microsoft strongly recommends you enable ADR in your database before creating the first index on an enclave-enabled column using randomized encryption. Vedere la sezione Ripristino del database in Always Encrypted con enclave sicuri.See the Database Recovery section in Always Encrypted with secure enclaves.

  1. Chiudere tutte le istanze di SSMS che sono state usate nell'esercitazione precedente.Close any SSMS instances, you used in the previous tutorial. Verranno chiuse le connessioni di database che sono state aperte, come richiesto per abilitare il ripristino accelerato del database.This will close database connections you've opened, which is required to enable ADR.

  2. Aprire una nuova istanza di SSMS e connettersi all'istanza di SQL ServerSQL Server come sysadmin senza Always Encrypted abilitato per la connessione di database.Open a new instance of SSMS and connect to your SQL ServerSQL Server instance as sysadmin without Always Encrypted enabled for the database connection.

    1. Avviare SSMS.Start SSMS.
    2. Nella finestra di dialogo Connetti al server specificare il nome del server, selezionare un metodo di autenticazione e specificare le credenziali.In the Connect to Server dialog, specify your server name, select an authentication method, and specify your credentials.
    3. Fare clic su Opzioni >> e selezionare la scheda Always Encrypted.Click Options >> and select the Always Encrypted tab.
    4. Verificare che la casella di controllo Abilita Always Encrypted (crittografia colonna) non sia selezionata.Make sure the Enable Always Encrypted (column encryption) checkbox is not selected.
    5. Selezionare Connetti.Select Connect.
  3. Aprire una nuova finestra di query ed eseguire l'istruzione seguente per abilitare il ripristino accelerato del database.Open a new query window and execute the below statement to enable ADR.

    ALTER DATABASE ContosoHR SET ACCELERATED_DATABASE_RECOVERY = ON;
    

Passaggio 2: Creare e testare un indice senza la separazione dei ruoliStep 2: Create and test an index without role separation

In questo passaggio verrà creato e testato un indice su una colonna crittografata.In this step, you'll create and test an index on an encrypted column. Si opererà come un singolo utente che ricopre sia il ruolo di amministratore di database, che gestisce il database, che di proprietario dei dati, che ha accesso alle chiavi che proteggono i dati.You'll be acting as a single user who is assuming the roles of both a DBA, who manages the database, and the data owner who has access to the keys, protecting the data.

  1. Aprire una nuova istanza di SSMS e connettersi all'istanza di SQL ServerSQL Server con Always Encrypted abilitato per la connessione di database.Open a new SSMS instance and connect to your SQL ServerSQL Server instance with Always Encrypted enabled for the database connection.

    1. Avviare una nuova istanza di SSMS.Start a new instance of SSMS.
    2. Nella finestra di dialogo Connetti al server specificare il nome del server, selezionare un metodo di autenticazione e specificare le credenziali.In the Connect to Server dialog, specify your server name, select an authentication method, and specify your credentials.
    3. Fare clic su Opzioni >> e selezionare la scheda Always Encrypted.Click Options >> and select the Always Encrypted tab.
    4. Selezionare la casella di controllo Abilita Always Encrypted (crittografia colonna) e specificare l'URL di attestazione dell'enclave, ad esempio http://hgs.bastion.local/Attestation o https://MyAttestationProvider.us.attest.azure.net/attest/SgxEnclave.Select the Enable Always Encrypted (column encryption) checkbox and specify your enclave attestation URL (for example, http://hgs.bastion.local/Attestation or https://MyAttestationProvider.us.attest.azure.net/attest/SgxEnclave).
    5. Selezionare Connetti.Select Connect.
    6. Se viene richiesto di abilitare la parametrizzazione per le query Always Encrypted, fare clic su Abilita.If prompted to enable parameterization for Always Encrypted queries, click Enable.
  2. Se non viene richiesto di abilitare la parametrizzazione per Always Encrypted, verificare che sia abilitata.If you weren't prompted to enable Parameterization for Always Encrypted, verify it's enabled.

    1. Selezionare Strumenti dal menu principale di SSMS.Select Tools from the main menu of SSMS.
    2. Selezionare Opzioni.Select Options....
    3. Passare a Esecuzione query > SQL Server > Avanzata.Navigate to Query Execution > SQL Server > Advanced.
    4. Assicurarsi che l'opzione Abilita parametrizzazione per Always Encrypted sia selezionata.Ensure that Enable Parameterization for Always Encrypted is checked.
    5. Selezionare OK.Select OK.
  3. Aprire una finestra di query ed eseguire le istruzioni seguenti per crittografare la colonna LastName nella tabella Employees.Open a query window and execute the below statements to encrypt the LastName column in the Employees table. Verrà creato e usato un indice su tale colonna nei passaggi successivi.You'll create and use an index on that column in later steps.

    ALTER TABLE [HR].[Employees]
    ALTER COLUMN [LastName] [nvarchar](50) COLLATE Latin1_General_BIN2 
    ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL;
    GO   
    ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
    GO
    
  4. Creare un indice sulla colonna LastName.Create an index on the LastName column. Poiché si è connessi al database con Always Encrypted abilitato, il driver client all'interno di SSMS fornisce in modo trasparente CEK1 (la chiave di crittografia della colonna, che protegge la colonna LastName) per l'enclave, necessaria per creare l'indice.Since you're connected to the database with Always Encrypted enabled, the client driver inside SSMS transparently provides CEK1 (the column encryption key, protecting the LastName column) to the enclave, which is needed to create the index.

    CREATE INDEX IX_LastName ON [HR].[Employees] ([LastName])
    INCLUDE ([EmployeeID], [FirstName], [SSN], [Salary]);
    GO
    
  5. Eseguire una query avanzata sulla colonna LastName e verificare che SQL Server usi l'indice quando si esegue la query.Run a rich query on the LastName column and verify SQL Server uses the index when executing the query.

    1. Nella stessa finestra di query o in una nuova verificare che il pulsante Includi statistiche query dinamiche sulla barra degli strumenti sia attivato.In the same or a new query window, make sure the Include Live Query Statistics button on the toolbar is on.

    2. Eseguire la query seguente.Execute the below query.

      DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%';
      SELECT * FROM [HR].[Employees] WHERE [LastName] LIKE @LastNamePrefix;
      GO
      
    3. Nella scheda Statistiche query dinamiche (nella parte inferiore della finestra di query) è possibile osservare che la query usa l'indice.In the Live Query Statistics tab (in the bottom part of the query window), observe that the query uses the index.

Passaggio 3: Creare un indice con la separazione dei ruoliStep 3: Create an index with role separation

In questo passaggio verrà creato un indice su una colonna crittografata, operando come due utenti diversi.In this step, you'll create an index on an encrypted column, pretending to be two different users. Un utente è un amministratore di database, che deve creare un indice, ma non ha accesso alle chiavi.One user is a DBA, who needs to create an index, but doesn't have access to the keys. L'altro utente è un proprietario dei dati, che può accedere alle chiavi.The other user is a data owner, who has access to the keys.

  1. Usando l'istanza di SSMS senza Always Encrypted abilitato, eseguire l'istruzione seguente per eliminare l'indice della colonna LastName.Using the SSMS instance without Always Encrypted enabled, execute the below statement to drop the index on the LastName column.

    DROP INDEX IX_LastName ON [HR].[Employees]; 
    GO
    
  2. Operando come il proprietario dei dati (o un'applicazione che dispone dell'accesso alle chiavi), popolare la cache all'interno dell'enclave con CEK1.Acting as a data owner (or an application that has access to the keys), populate the cache inside the enclave with CEK1.

    Nota

    A meno che non sia stata riavviata l'istanza di SQL Server dopo il Passaggio 2: Creare e testare un indice senza la separazione dei ruoli, questo passaggio è ridondante dal momento che CEK1 è già presente nella cache.Unless you have restarted your SQL Server instance after Step 2: Create and test an index without role separation, this step is redundant as the CEK1 is already present in the cache. È stato aggiunto per illustrare come un proprietario dei dati può fornire una chiave per l'enclave, se non è già presente nell'enclave.We have added it to demonstrate how a data owner can provide a key to the enclave, if it is not already present in the enclave.

    1. Nell'istanza di SSMS con Always Encrypted abilitato eseguire le istruzioni seguenti in una finestra di query.In the SSMS instance with Always Encrypted enabled, execute the below statements in a query window. L'istruzione invia all'enclave tutte le chiavi di crittografia delle colonne abilitate per l'enclave.The statement sends all enclave-enabled column encryption keys to the enclave. Per informazioni dettagliate, vedere sp_enclave_send_keys.See sp_enclave_send_keys for details.

      EXEC sp_enclave_send_keys;
      GO
      
    2. Come alternativa all'esecuzione della stored procedure precedente, è possibile eseguire una query DML che usa l'enclave sulla colonna LastName.As an alternative to executing the above stored procedure, you can run a DML query that uses the enclave against the LastName column. Questa operazione inserirà nell'enclave solo CEK1.This will populate the enclave only with CEK1.

      DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%';
      SELECT * FROM [HR].[Employees] WHERE [LastName] LIKE @LastNamePrefix;
      GO
      
  3. Operando come amministratore di database, creare l'indice.Acting as a DBA, create the index.

    1. Nell'istanza di SSMS senza Always Encrypted abilitato eseguire le istruzioni seguenti in una finestra di query.In the SSMS instance without Always Encrypted enabled, execute the below statements in a query window.

      CREATE INDEX IX_LastName ON [HR].[Employees] ([LastName])
      INCLUDE ([EmployeeID], [FirstName], [SSN], [Salary]);
      GO
      
  4. Come proprietario dei dati, eseguire una query avanzata sulla colonna LastName e verificare che SQL Server usi l'indice quando si esegue la query.As a data owner, run a rich query on the LastName column and verify SQL Server uses the index when executing the query.

    1. Nell'istanza di SSMS con Always Encrypted abilitato selezionare una finestra di query esistente o aprirne una nuova e verificare che il pulsante Includi statistiche query dinamiche sulla barra degli strumenti sia attivato.In the SSMS instance with Always Encrypted enabled, select an existing query window or open a new query window, and make sure the Include Live Query Statistics button on the toolbar is on.

    2. Eseguire la query seguente.Execute the below query.

      DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%';
      SELECT * FROM [HR].[Employees] WHERE [LastName] LIKE @LastNamePrefix;
      GO
      
    3. Nella scheda Statistiche query dinamiche (nella parte inferiore della finestra di query) è possibile osservare che la query usa l'indice.In the Live Query Statistics (in the bottom part of the query window), observe that the query uses the index.

Passaggi successiviNext steps

Vedere ancheSee also