Spostare un database protetto da TDE in un'altra istanza di SQL Server

  • Articolo

Si applica a:SQL Server

Questo articolo illustra come proteggere un database tramite TDE (Transparent Data Encryption) e spostare il database in un'altra istanza di SQL Server usando SQL Server Management Studio o Transact-SQL. TDE esegue la crittografia e la decrittografia delle operazioni di I/O di file di dati e log in tempo reale. La crittografia usa una chiave di crittografia del database (DEK) che viene archiviata nel record di avvio del database per la disponibilità durante il ripristino. La chiave di decrittografia è una chiave simmetrica protetta tramite un certificato archiviato nel database master del server o una chiave asimmetrica protetta da un modulo EKM.

Limiti

  • Quando si sposta il database protetto con TDE, è necessario spostare anche la chiave asimmetrica o il certificato usato per aprire la chiave di decrittografia. La chiave asimmetrica o il certificato deve essere installato nel database master del server di destinazione in modo che SQL Server possa accedere ai file del database. Per altre informazioni, vedere Transparent Data Encryption (TDE).

  • Ai fini del recupero del certificato, è necessario mantenere copie sia del file del certificato sia del file della chiave privata. La password per la chiave primaria non deve essere uguale a quella della chiave master del database.

  • SQL Server archivia i file creati qui in C:\Program Files\Microsoft SQL Server\MSSQL<xx>.MSSQLSERVER\MSSQL\DATA per impostazione predefinita, dove <xx> è il numero di versione.

Autorizzazioni

  • È necessaria CONTROL DATABASEl'autorizzazione per il database master per creare la chiave master del database.

  • È necessaria CREATE CERTIFICATEl'autorizzazione per il database master per creare il certificato che consente di proteggere la chiave di decrittografia.

  • Sono richieste l'autorizzazione CONTROL DATABASE per il database crittografato e l'autorizzazione VIEW DEFINITION per la chiave asimmetrica o il certificato usato per crittografare la chiave di crittografia del database.

Creare un database protetto con Transparent Data Encryption

Le procedure riportate di seguito mostrano come creare un database protetto da TDE tramite SQL Server Management Studio e mediante l'uso di Transact-SQL.

Usare SQL Server Management Studio

  1. Creare una chiave master del database e il certificato nel database master. Per altre informazioni, vedere la sezione Utilizzo di Transact-SQL più avanti, in questo articolo.

  2. Creare un backup del certificato del server nel database master. Per altre informazioni, vedere la sezione Utilizzo di Transact-SQL più avanti, in questo articolo.

  3. In Esplora oggetti fare clic con il pulsante destro del mouse sulla cartella Database e selezionare Nuovo database.

  4. Nella finestra di dialogo Nuovo database digitare il nome del nuovo database nella casella Nome database .

  5. Nella casella Proprietario digitare il nome del proprietario del nuovo database. In alternativa, selezionare i puntini di sospensione (...) per aprire la finestra di dialogo Seleziona proprietario database. Per altre informazioni sulla creazione di un nuovo database, vedere Creare un database.

  6. In Esplora oggetti selezionare il segno più per espandere la cartella Database .

  7. Fare clic con il pulsante destro del mouse sul database creato, scegliere Attivitàe quindi fare clic su Gestione crittografia del database.

    Nella finestra di dialogo Gestione crittografia del database sono disponibili le opzioni indicate di seguito.

    Algoritmo di crittografia
    Visualizza o imposta l'algoritmo da usare per la crittografia del database. L'algoritmo predefinito èAES128 . Questo campo è obbligatorio. Per altre informazioni sugli algoritmi di crittografia, vedere Scegliere un algoritmo di crittografia.

    Usa certificato server
    Imposta la sicurezza della crittografia mediante un certificato. Selezionarne uno dall'elenco. Se non si dispone dell'autorizzazione VIEW DEFINITION per i certificati del server, l'elenco sarà vuoto. Se viene selezionato un metodo certificato di crittografia, il valore non può essere vuoto. Per altre informazioni sui certificati, vedere SQL Server Certificates and Asymmetric Keys.

    Usa chiave asimmetrica server
    Imposta la sicurezza della crittografia mediante una chiave asimmetrica. Vengono visualizzate solo le chiavi asimmetriche disponibili. Solo una chiave asimmetrica protetta da un modulo EKM può crittografare un database tramite Transparent Data Encryption.

    Attiva crittografia del database
    Modifica il database per abilitare (se selezionata) o disabilitare la funzionalità TDE (se deselezionata).

  8. Al termine selezionare OK.

Usare Transact-SQL

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

  2. Sulla barra Standard selezionare Nuova query.

  3. Copiare e incollare l'esempio seguente nella finestra di query e selezionare Esegui.

    -- Create a database master key and a certificate in the master database.
USE master;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';
GO

CREATE CERTIFICATE TestSQLServerCert
    WITH SUBJECT = 'Certificate to protect TDE key'
GO

-- Create a backup of the server certificate in the master database.
-- The following code stores the backup of the certificate and the private key file in the default data location for this instance of SQL Server
-- (C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA).
BACKUP CERTIFICATE TestSQLServerCert TO FILE = 'TestSQLServerCert'
WITH PRIVATE KEY (
    FILE = 'SQLPrivateKeyFile',
    ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1'
);
GO

-- Create a database to be protected by TDE.
CREATE DATABASE CustRecords;
GO

-- Switch to the new database.
-- Create a database encryption key, that is protected by the server certificate in the master database.
-- Alter the new database to encrypt the database using TDE.
USE CustRecords;
GO

CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE TestSQLServerCert;
GO

ALTER DATABASE CustRecords
SET ENCRYPTION ON;
GO

Per altre informazioni, vedi:

Spostare un database protetto con Transparent Data Encryption

Le procedure riportate di seguito mostrano come spostare un database protetto da TDE tramite SQL Server Management Studio e mediante l'uso di Transact-SQL.

Usare SQL Server Management Studio

  1. In Esplora oggetti fare clic con il pulsante destro del mouse sul database crittografato in precedenza, scegliere Attività e fare clic su Scollega....

    Nella finestra di dialogo Scollega database sono disponibili le opzioni seguenti.

    Database da scollegare
    Consente di visualizzare i database da scollegare.

    Nome database
    Consente di visualizzare il nome del database da scollegare.

    Interrompi connessioni
    Consente di interrompere le connessioni al database specificato.

Nota

Non è possibile scollegare un database con connessioni attive.

Aggiorna statistiche
Per impostazione predefinita, con l'operazione di scollegamento è possibile mantenere eventuali statistiche di ottimizzazione non aggiornate prima di scollegare il database. Per aggiornare le statistiche di ottimizzazione esistenti, selezionare questa casella di controllo.

Mantieni cataloghi full-text
Per impostazione predefinita, con l'operazione di scollegamento è possibile mantenere eventuali cataloghi full-text associati al database. Per rimuoverli, deselezionare la casella di controllo Mantieni cataloghi full-text . Questa opzione viene visualizzata solo quando si aggiorna un database da SQL Server 2005 (9.x).

Stato
Consente di visualizzare uno degli stati seguenti: Pronto o Non pronto.

Message
Nella colonna Messaggio possono essere visualizzate informazioni sul database simili alle seguenti:

  • Quando un database è coinvolto nella replica, lo Stato è Non pronto e nella colonna Messaggio viene visualizzato Database replicato.

  • Quando un database ha una o più connessioni attive, il valore di Stato è Non pronto e la colonna Messaggio visualizza <Connessioni attive:> numero_di_connessioni_attive, ad esempio Connessioni attive: 1. Prima di poter scollegare il database è necessario disconnettere tutte le connessioni attive selezionando Interrompi connessioni.

Per ottenere ulteriori informazioni su un messaggio, selezionare il testo del collegamento ipertestuale per aprire Monitoraggio attività.

  1. Seleziona OK.

  2. Usando Esplora risorse spostare o copiare i file del database dal server di origine nello stesso percorso nel server di destinazione.

  3. Usando Esplora risorse spostare o copiare il backup dei file del certificato del server e della chiave privata dal server di origine nello stesso percorso nel server di destinazione.

  4. Creare una chiave master del database nell'istanza di destinazione di SQL Server. Per altre informazioni, vedere la sezione Utilizzo di Transact-SQL più avanti, in questo articolo.

  5. Ricreare il certificato del server usando il file di backup del certificato del server originale. Per altre informazioni, vedere la sezione Utilizzo di Transact-SQL più avanti, in questo articolo.

  6. In SQL Server Management Studio, in Esplora oggetti fare clic con il pulsante destro del mouse su Database e scegliere Allegare….

  7. Nella finestra di dialogo Collega database , in Database da collegareselezionare Aggiungi.

  8. Nella finestra di dialogo Individua file di database -server_name selezionare il file di database da collegare al nuovo server e selezionare OK.

    Nella finestra di dialogo Collega database sono disponibili le opzioni seguenti.

    Database da collegare
    Consente di visualizzare informazioni sui database selezionati.

    <nessuna intestazione di colonna>
    Consente di visualizzare un'icona che indica lo stato dell'operazione di collegamento. Le icone possibili sono illustrate nella descrizione di Stato .

    Percorso file MDF
    Consente di visualizzare il percorso e il nome del file MDF selezionato.

    Nome database
    Consente di visualizzare il nome del database.

    Collega come
    Facoltativamente, è possibile specificare un nome diverso per il database da collegare.

    Proprietario
    Consente di visualizzare un elenco a discesa di possibili proprietari del database in cui è possibile selezionare un proprietario diverso.

    Stato
    Consente di visualizzare lo stato del base in base alla tabella seguente.

Icon Testo Stato Descrizione
(Nessuna icona) (Nessun testo) L'operazione di collegamento non è stata avviata o può essere sospesa per questo oggetto. È il valore predefinito all'apertura della finestra di dialogo.
Triangolo verde che punta a destra In corso L'operazione di collegamento è stata avviata ma non ancora completata.
Segno di spunta verde Riuscita L'oggetto è stato collegato.
Cerchio rosso con croce bianca Error Si è verificato un errore durante l'operazione. Il collegamento non è stato completato.
Cerchio con due quadranti neri a destra e a sinistra e due quadranti bianchi in alto e in basso Arrestato L'operazione di collegamento non è stata completata perché l'utente ne ha arrestato l'esecuzione.
Cerchio con freccia curva che punta in senso antiorario. È stato eseguito il rollback L'operazione di collegamento è stata completata ma ne è stato eseguito il rollback a causa di un errore durante il collegamento di un altro oggetto.

Message
Non viene visualizzato alcun messaggio oppure viene visualizzato il collegamento ipertestualeImpossibile trovare il file.

Aggiunta
Consente di individuare i file principali del database necessari. Se l'utente seleziona un file con estensione mdf, le informazioni appropriate vengono inserite automaticamente nei rispettivi campi della griglia Database da collegare .

Rimuovi
Consente di rimuovere il file selezionato dalla griglia Database da collegare .

"<database_name>" dettagli database
Consente di visualizzare i nomi dei file da collegare. Per verificare o modificare il percorso di un file, selezionare il pulsante Sfoglia (...).

Nota

Se un file non esiste, nella colonna Messaggio viene visualizzato "Non trovato". Se non viene trovato un file di log, vuol dire che si trova in un'altra directory o è stato eliminato. È necessario aggiornare il percorso del file nella griglia Dettagli database in modo che indichi la posizione corretta oppure rimuovere il file di log dalla griglia. Se non viene rilevato un file di dati con estensione ndf, è necessario aggiornare il percorso nella griglia in modo che indichi la posizione corretta.

Nome file originale
Consente di visualizzare il nome del file collegato appartenente al database.

Tipo di file
Indica il tipo di file, ovvero Dati o Log.

Percorso file corrente
Consente di visualizzare il percorso del file di database selezionato. Il percorso può essere modificato manualmente.

Message
Non viene visualizzato alcun messaggio oppure viene visualizzato il collegamento ipertestualeImpossibile trovare il file.

Usare Transact-SQL

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

  2. Sulla barra Standard selezionare Nuova query.

  3. Copiare e incollare l'esempio seguente nella finestra di query e selezionare Esegui.

    -- Detach the TDE protected database from the source server.
USE master;
GO

EXEC master.dbo.sp_detach_db @dbname = N'CustRecords';
GO

-- Move or copy the database files from the source server to the same location on the destination server.
-- Move or copy the backup of the server certificate and the private key file from the source server to the same location on the destination server.
-- Create a database master key on the destination instance of SQL Server.
USE master;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';
GO

-- Recreate the server certificate by using the original server certificate backup file.
-- The password must be the same as the password that was used when the backup was created.
CREATE CERTIFICATE TestSQLServerCert
FROM FILE = 'TestSQLServerCert'
WITH PRIVATE KEY (
    FILE = 'SQLPrivateKeyFile',
    DECRYPTION BY PASSWORD = '*rt@40(FL&dasl1'
);
GO

-- Attach the database that is being moved.
-- The path of the database files must be the location where you have stored the database files.
CREATE DATABASE [CustRecords] ON (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\CustRecords.mdf'),
    (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\CustRecords_log.LDF')
FOR ATTACH;
GO

Per altre informazioni, vedi:

Contenuto correlato