SQL Always On quando i dati di ripristino di BitLocker vengono crittografati nel database

2025-06-13

Per SQL Always On, sono necessari passaggi aggiuntivi quando le informazioni di BitLocker vengono crittografate usando le istruzioni riportate in Crittografare i dati di ripristino nel database. I passaggi aggiuntivi garantiscono che tutti i nodi Always On possano aprire automaticamente la chiave master del database quando si verifica un evento di failover. La procedura seguente consente di recuperare facilmente le chiavi BitLocker senza intervento manuale.

Panoramica di SQL Always On quando i dati di ripristino di BitLocker vengono crittografati nel database

SQL Server crittografa i dati usando un'infrastruttura gerarchica ed è descritto in dettaglio in Gerarchia di crittografia.

SMK (Site Master Key): questa chiave è una chiave per istanza univoca per ogni nodo SQL Server Always On e non viene replicata. Viene usato per crittografare la chiave master del database.
Chiave master del database (DMK): questa chiave viene archiviata nel database e viene replicata. Viene usato per crittografare il BitLockerManagement_CERT.
BitLockerManagement_CERT : questo certificato viene archiviato nel database e viene replicato. Viene usato per crittografare alcuni dati correlati a BitLocker, ad esempio le chiavi di ripristino.

SMK crittografa la password DMK. Gli SDK sono specifici del nodo. Quando si verifica un evento di failover, il nuovo nodo primario non può decrittografare la password DMK perché è stata crittografata con una SMK diversa. L'impostazione della password DMK in ogni nodo consente al nodo di decrittografare la password in caso di failover.

Nota

Il BitLockerManagement_CERT esegue la crittografia delle colonne. Se questo certificato viene perso o eliminato o la DMK crittografata viene persa o eliminata, le chiavi di BitLocker devono essere depositate e crittografate di nuovo.

Se la password DMK (Database Master Key) è nota

Eseguire il comando seguente in ogni nodo del gruppo di disponibilità che ospita il database Configuration Manager:

Importante

Nel comando seguente:

Sostituire password ovunque con una password complessa di propria scelta. Assicurarsi di archiviare in modo sicuro la password per informazioni di riferimento future.
Sostituire CM_XXX con il nome del database Configuration Manager (CM).

EXEC sp_control_dbmasterkey_password
    @db_name = N'CM_XXX',
    @password = N'password',
    @action = N'add';

Questo comando registra la password DMK con la chiave master del servizio locale (SMK) consentendo a SQL Server di aprire automaticamente la DMK quando si verifica un evento di failover. Questo processo garantisce che la DMK possa essere decrittografata automaticamente in tale nodo dopo un failover o un riavvio.

Per verificare che tutti i nodi possano aprire automaticamente la chiave master del database (DMK) e decrittografare i dati, vedere la sezione Verificare che tutti i nodi possano aprire automaticamente la chiave master del database (DMK) e decrittografare i dati in questo articolo.

Se la password DMK (Database Master Key) esistente è sconosciuta

Se la password DMK esistente è sconosciuta, è necessario eliminare la DMK esistente e crearne una nuova con una password nota. Questi passaggi illustrano come eseguire questa procedura.

Trovare una DMK valida

Se non è noto quale nodo ha una DMK valida, seguire questa procedura per determinare dove è aperta la DMK esistente:

Importante

Nelle query e nei comandi seguenti:

Sostituire password ovunque con una password complessa di propria scelta. Assicurarsi di archiviare in modo sicuro la password in un percorso noto per informazioni di riferimento future.
Sostituire CM_XXX con il nome del database Configuration Manager (CM).

Eseguire la query seguente nel nodo primario:

SELECT TOP 5 RecoveryAndHardwareCore.DecryptString(RecoveryKey, DEFAULT)
FROM RecoveryAndHardwareCore_Keys
ORDER BY LastUpdateTime DESC

Nella query risultante:
- Se la DMK è aperta, la query restituisce valori di testo non crittografato per tutte le righe in cui è presente una chiave valida. Questo nodo è il nodo da avviare e il passaggio successivo può essere ignorato.
- Se la DMK non è aperta, la query restituisce valori NULL per tutte le righe. Il nodo corrente non è il nodo in cui è aperta la DMK. Seguire il passaggio successivo per trovare il nodo in cui è aperta la DMK.
Se la query restituisce tutti i valori NULL, eseguire il failover in ogni nodo secondario e ripetere i passaggi precedenti fino a quando non viene trovato il nodo in grado di decrittografare correttamente RecoveryAndHardwareCore_Keys . Questo nodo è il nodo da avviare.

Creare una nuova chiave master del database (DMK)

Dopo aver identificato il nodo appropriato con la DMK aperta, seguire questa procedura:

Nel nodo identificato nei passaggi precedenti eseguire la query seguente per esportare il certificato BitLockerManagement_CERT con la relativa chiave privata. Assicurarsi di usare una password complessa:

BACKUP CERTIFICATE BitLockerManagement_CERT
TO FILE = 'C:\Windows\Temp\BitLockerManagement_CERT'
WITH PRIVATE KEY
(
    FILE = 'C:\Windows\Temp\BitLockerManagement_CERT_KEY',
    ENCRYPTION BY PASSWORD = 'password'
);

Eseguire il backup della chiave master del database esistente eseguendo la query seguente per esportare la DMK esistente:
```
BACKUP MASTER KEY
TO FILE = 'C:\Windows\Temp\DMK'
ENCRYPTION BY PASSWORD = 'password';
```
Nota

Questo passaggio è facoltativo ma consigliato. Assicurarsi di mantenere il backup in una posizione nota sicura.
Eseguire la query seguente per eliminare il certificato e la DMK esistenti:
```
DROP CERTIFICATE BitLockerManagement_CERT;
DROP MASTER KEY;
```
Questo passaggio rimuove le chiavi precedenti.
Eseguire la query seguente per creare una nuova DMK. Assicurarsi di usare una password complessa:
```
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'password';
```

Eseguire la query seguente per registrare la nuova password DMK con la SMK locale:

EXEC sp_control_dbmasterkey_password
    @db_name = N'CM_XXX',
    @password = N'password',
    @action = N'add';

Eseguire la query seguente per importare il certificato BitLockerManagement_CERT esportato in precedenza:

CREATE CERTIFICATE BitLockerManagement_CERT AUTHORIZATION RecoveryAndHardwareCore
FROM FILE = 'C:\Windows\Temp\BitLockerManagement_CERT'
WITH PRIVATE KEY
(
    FILE = 'C:\Windows\Temp\BitLockerManagement_CERT_KEY',
    DECRYPTION BY PASSWORD = 'password'
);

Eseguire la query seguente per concedere le autorizzazioni di controllo necessarie per il certificato:

GRANT CONTROL ON CERTIFICATE::BitLockerManagement_CERT TO RecoveryAndHardwareRead;
GRANT CONTROL ON CERTIFICATE::BitLockerManagement_CERT TO RecoveryAndHardwareWrite;

Eseguire il failover al nodo successivo.

Eseguire la query seguente per registrare la password DMK con la SMK locale. Eseguire una volta per replica:

EXEC sp_control_dbmasterkey_password
    @db_name = N'CM_XXX',
    @password = N'password',
    @action = N'add';

Eseguire i due passaggi precedenti su tutti i nodi rimanenti.
Eseguire il failover nel nodo originale.
Per verificare che tutti i nodi possano aprire automaticamente la chiave master del database (DMK) e decrittografare i dati, vedere la sezione successiva Verificare che tutti i nodi possano aprire automaticamente la chiave master del database (DMK) e decrittografare i dati in questo articolo.

Verificare che tutti i nodi possano aprire automaticamente la chiave master del database (DMK) e decrittografare i dati

Per verificare che tutti i nodi possano aprire automaticamente la chiave master del database (DMK) e decrittografare i dati:

Eseguire il failover in un nodo.

Eseguire la query seguente:

SELECT TOP 5 RecoveryAndHardwareCore.DecryptString(RecoveryKey, DEFAULT)
FROM RecoveryAndHardwareCore_Keys
ORDER BY LastUpdateTime DESC

Se la query restituisce valori di testo non crittografato per le righe in cui è presente una chiave valida, il nodo può aprire automaticamente la chiave master del database (DMK) e decrittografare i dati.
Ripetere i tre passaggi precedenti per ogni nodo aggiuntivo.

Consiglio

Per una maggiore sicurezza, archiviare la password DMK complessa in modo sicuro. Ad esempio, in Azure Key Vault o in un altro archivio segreto sicuro. Evitare inoltre di codificare la password DMK in testo normale in script o file di configurazione.

Condividi tramite