Creare chiavi simmetriche identiche su due server

  • Articolo

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di Azure

Questo argomento descrive come creare chiavi simmetriche identiche in due server diversi in SQL Server tramite Transact-SQL. Al fine di decrittografare l'argomento ciphertext, è necessario disporre della chiave usata per crittografarlo. Quando la crittografia e la decrittazione vengono eseguite in un database singolo, la chiave viene archiviata nel database ed è disponibile, a seconda delle autorizzazioni, sia per la crittografia che per la decrittazione. Viceversa, quando la crittografia e la decrittazione vengono eseguite in database separati o in server separati, la chiave archiviata in un database non può essere utilizzata nell'altro database.

Operazioni preliminari

Limitazioni e restrizioni

  • Quando si crea una chiave simmetrica è necessario crittografarla con almeno uno degli elementi seguenti: certificato, password, chiave simmetrica, chiave asimmetrica o PROVIDER. Una chiave può essere crittografata con più elementi di ogni tipo, ovvero una singola chiave simmetrica può essere crittografata contemporaneamente con più certificati, password, chiavi simmetriche e chiavi asimmetriche.

  • Se si crittografa una chiave simmetrica con una password anziché con la chiave pubblica della chiave master del database, viene usato l'algoritmo di crittografia TRIPLE DES. Per questo motivo, le chiavi create con un algoritmo di crittografia avanzato, come AES, vengono a loro volta protette con un algoritmo meno avanzato.

Sicurezza

Autorizzazioni

È richiesta l'autorizzazione ALTER ANY SYMMETRIC KEY per il database. Se si specifica AUTHORIZATION, è richiesta l'autorizzazione IMPERSONATE per l'utente di database o l'autorizzazione ALTER per il ruolo applicazione. Se la crittografia viene applicata con un certificato o una chiave asimmetrica, è richiesta l'autorizzazione VIEW DEFINITION per il certificato o la chiave asimmetrica. Solo gli account di accesso di Windows e di SQL Server e i ruoli applicazione possono avere chiavi simmetriche. I gruppi e i ruoli non possono disporre di chiavi simmetriche.

Utilizzo di Transact-SQL

Per creare chiavi simmetriche identiche su due server diversi

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

  2. Sulla barra Standard fare clic su Nuova query.

  3. Creare una chiave eseguendo le seguenti istruzioni CREATE MASTER KEY, CREATE CERTIFICATE e CREATE SYMMETRIC KEY.

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My p@55w0Rd';  
GO  
CREATE CERTIFICATE [cert_keyProtection] WITH SUBJECT = 'Key Protection';  
GO  
CREATE SYMMETRIC KEY [key_DataShare] WITH  
    KEY_SOURCE = 'My key generation bits. This is a shared secret!',  
    ALGORITHM = AES_256,   
    IDENTITY_VALUE = 'Key Identity generation bits. Also a shared secret'  
    ENCRYPTION BY CERTIFICATE [cert_keyProtection];  
GO

  4. Connettersi a un'istanza del server separata, aprire una finestra Query diversa ed eseguire le istruzioni SQL precedenti per creare la stessa chiave nel secondo server.

  5. Verificare le chiavi eseguendo prima l'istruzione OPEN SYMMETRIC KEY e quindi l'istruzione SELECT seguente nel primo server.

    OPEN SYMMETRIC KEY [key_DataShare]   
    DECRYPTION BY CERTIFICATE cert_keyProtection;  
GO  
SELECT encryptbykey(key_guid('key_DataShare'), 'MyData' )  
GO  
-- For example, the output might look like this: 0x2152F8DA8A500A9EDC2FAE26D15C302DA70D25563DAE7D5D1102E3056CE9EF95CA3E7289F7F4D0523ED0376B155FE9C3

  6. Nell'altro server incollare il risultato dell'istruzione SELECT precedente nel codice indicato di seguito come valore di @blob ed eseguire il codice per verificare che la chiave duplicata sia in grado di decrittografare il testo crittografato.

    OPEN SYMMETRIC KEY [key_DataShare]   
    DECRYPTION BY CERTIFICATE cert_keyProtection;  
GO  
DECLARE @blob varbinary(8000);  
SELECT CONVERT(varchar(8000), decryptbykey(@blob));  
GO

  7. Chiudere la chiave simmetrica in entrambi i server.

    CLOSE SYMMETRIC KEY [key_DataShare];  
GO

Modifiche della crittografia in SQL Server 2017 CU2

SQL Server 2016 usa l'algoritmo hash SHA1 per le operazioni di crittografia. A partire da SQL Server 2017 viene invece usato SHA-2. Ciò significa che possono essere necessari alcuni passaggi aggiuntivi affinché l'installazione di SQL Server 2017 sia in grado di decrittografare gli elementi crittografati da SQL Server 2016. Ecco i passaggi aggiuntivi:

  • Verificare che SQL Server 2017 sia aggiornato ad almeno l'aggiornamento cumulativo 2 (CU2).
  • Dopo aver installato CU2, attivare il flag di traccia 4631 in SQL Server 2017: DBCC TRACEON(4631, -1);
    • Il flag di traccia 4631 è nuovo in SQL Server 2017. Il flag di traccia 4631 richiede un ON globale per creare la chiave master, il certificato o la chiave simmetrica in SQL Server 2017. In questo modo gli elementi creati possono interagire con SQL Server 2016 e versioni precedenti. Questo flag di traccia deve essere abilitato temporaneamente solo per eseguire nuovamente la crittografia dei dati con chiavi derivate da SHA2.

Per altre informazioni, vedi:

Vedi anche