-- Übersicht der Hierarchie (konzeptionell)
-- Service Master Key (automatisch, Instanzebene)
-- └─ Database Master Key (optional, Datenbank master)
-- └─ Certificate (in master)
-- └─ Database Encryption Key (in Benutzerdatenbank)

-- Schritt 1: Database Master Key in der master-Datenbank erstellen (falls nicht vorhanden)
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Starkes$Passwort123!';
GO

-- Schritt 2: Zertifikat in master erstellen
CREATE CERTIFICATE TDECert_MeineDB
WITH SUBJECT = 'Zertifikat für TDE der Datenbank MeineDB';
GO

-- Schritt 3: Database Encryption Key (DEK) in der Zieldatenbank erstellen
USE [MeineDB];
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert_MeineDB;
GO

-- Schritt 4: TDE für die Datenbank aktivieren
ALTER DATABASE [MeineDB] SET ENCRYPTION ON;
GO

-- Fortschritt der Verschlüsselung überwachen
SELECT DB_NAME(database_id) AS DatabaseName, encryption_state, percent_complete
FROM sys.dm_database_encryption_keys;

-- Zertifikat inklusive privatem Schlüssel sichern
USE master;
GO

BACKUP CERTIFICATE TDECert_MeineDB
TO FILE = 'D:\Backups\Certificates\TDECert_MeineDB.cer'
WITH PRIVATE KEY (
  FILE = 'D:\Backups\Certificates\TDECert_MeineDB_PrivateKey.pvk',
  ENCRYPTION BY PASSWORD = 'WeiteresStarkesPasswort!'
);
GO

-- Aktuellen Verschlüsselungsstatus aller Datenbanken
SELECT 
  DB_NAME(database_id) AS DatabaseName,
  CASE encryption_state
    WHEN 0 THEN 'Keine Verschlüsselung'
    WHEN 1 THEN 'Nicht verschlüsselt'
    WHEN 2 THEN 'Verschlüsselung läuft'
    WHEN 3 THEN 'Verschlüsselt'
    WHEN 4 THEN 'Schlüsseländerung läuft'
    WHEN 5 THEN 'Entschlüsselung läuft'
  END AS EncryptionState,
  percent_complete
FROM sys.dm_database_encryption_keys;

-- Zertifikate in der master-Datenbank anzeigen
SELECT name, pvt_key_encryption_type_desc, issuer_name
FROM sys.certificates;

-- Prüfen, ob TDE aktiv ist (Version vor 2016: sys.dm_database_encryption_keys)
SELECT name, is_encrypted
FROM sys.databases;

-- TDE für eine Datenbank ausschalten
ALTER DATABASE [MeineDB] SET ENCRYPTION OFF;
GO

-- Warten, bis der Status 1 (Nicht verschlüsselt) erreicht ist
SELECT encryption_state FROM sys.dm_database_encryption_keys
WHERE database_id = DB_ID('MeineDB');

-- Optional: Database Encryption Key löschen (wenn nicht mehr benötigt)
USE [MeineDB];
DROP DATABASE ENCRYPTION KEY;
GO

-- Zertifikat kann in master verbleiben oder ebenfalls gelöscht werden (Vorsicht!)
USE master;
DROP CERTIFICATE TDECert_MeineDB;

-- Auf dem Zielserver (sofern noch kein Database Master Key existiert)
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'AnderesStarkesPasswort';

-- Zertifikat aus der Sicherung wiederherstellen
CREATE CERTIFICATE TDECert_MeineDB
FROM FILE = 'D:\Backups\Certificates\TDECert_MeineDB.cer'
WITH PRIVATE KEY (
  FILE = 'D:\Backups\Certificates\TDECert_MeineDB_PrivateKey.pvk',
  DECRYPTION BY PASSWORD = 'WeiteresStarkesPasswort!'
);
GO

-- Jetzt kann die Datenbank wie gewohnt wiederhergestellt werden
RESTORE DATABASE [MeineDB] FROM DISK = N'D:\Backups\MeineDB.bak' ...;

-- TDE für eine Azure SQL-Datenbank deaktivieren (nicht empfohlen)
ALTER DATABASE [MeineAzureDB] SET ENCRYPTION OFF;

-- Status prüfen
SELECT name, is_encrypted FROM sys.databases;