Verschlüsseln von Daten

Mit dem SQL Server 2005 ist es nun auch möglich Daten direkt auf dem Server zu verschlüsseln. Für das Verschlüsseln und Entschlüsseln der Daten können asymmetrische oder symmetrische Verschlüsselungsverfahren verwendet werden.

Symmetrische Verschlüsselung

Bei der symmetrischen Verschlüsselung wird der gleiche Schlüssel sowohl für die Verschlüsselung als auch für die Entschlüsselung der Informationen verwendet. Symmetrische Verschlüsselungsverfahren sind performanter als asymmetrische Verfahren; daher empfiehlt sich für die Verschlüsselung von Daten in der Datenbank die Verwendung von symmetrischen Schlüsseln.

Asymmetrische Verschlüsselung

Der Nachteil der symmetrischen Verschlüsselung liegt darin, dass für beide Vorgänge, also das Verschlüsseln und das Entschlüsseln der gleiche Schlüssel bekannt sein muss. Bei Einsatzgebieten, bei denen Verschlüsselung und Entschlüsselung bei verschiedenen Partnern stattfinden (also zum Beispiel beim Versenden verschlüsselter Emails) besteht so das Problem, wie der geheime Schlüssel zwischen Sender und Empfänger sicher ausgetauscht werden kann. Diese Problematik lässt sich durch die Verwendung von asymmetrischen Verschlüsselungsverfahren überwinden. Bei der asymmetrischen Verschlüsselung werden zwei unterschiedliche Schlüssel für das Verschlüsseln und Entschlüsseln verwendet. In der Regel wird der Schlüssel zum Verschlüsseln öffentlich zugänglich gemacht („Public Key“). Der zugehörige Schlüssel zum Entschlüsseln der Nachricht verbleibt beim Empfänger („Private Key“). Dadurch ist ein sicherer Austausch von Informationen zwischen Sender und Empfänger möglich, ohne dass das Geheimnis (der Schlüssel) preisgegeben werden muss. Eine andere Variante der asymmetrischen Verschlüsselung wird beim Signieren von Modulen verwendet. Hier wird ein Hash aus dem Modul mit Hilfe des privaten Schlüssels verschlüsselt gespeichert. Beim Ausführen der Prozedur kann der Hash mit Hilfe des öffentlichen Schlüssels wieder entschlüsselt und geprüft werden. Da der private Schlüssel unbekannt ist, ist es nicht möglich, den Hash des Moduls nach einer Änderung erneut zu generieren.

Da bei der jetzigen Implementierung der Verschlüsselung im SQL Server Ver- und Entschlüsselung grundsätzlich auf dem Server stattfinden und damit auch bei asymmetrischer Verschlüsselung beide Schlüssel an der gleichen Stelle gespeichert werden, bietet die asymmetrische Verschlüsselung von Daten keine Vorteile. In der Regel wird die asymmetrische Verschlüsselung nur zum Sichern der symmetrischen Schlüssel verwendet. Eine Ausnahme bildet der Einsatz von Zertifikaten für das Signieren von Modulen.

Der Schlüssel ist der Schlüssel

Grundsätzlich ist die Verschlüsselung von Daten nur so sicher wie der Schlüssel selbst. Das bedeutet, dass jeder der Zugriff auf den Schlüssel hat auch in der Lage ist, die Daten wieder zu entschlüsseln. Der Zugriff auf die Schlüssel wird im SQL Server auf zwei Arten geschützt: Zum einen kann – wie bei allen anderen Datenbankobjekten auch – der Zugriff über Berechtigungen eingeschränkt werden. Dadurch erhalten nur autorisierte Benutzer Zugriff auf die Schlüssel; Datenbankbesitzer oder Systemadministratoren erhalten so aber in jedem Fall Zugriff auf alle Schlüssel der Datenbank. Daher wird der Schlüssel zusätzlich geschützt, indem der Schlüssel selbst wieder verschlüsselt gespeichert wird. Für die verschlüsselte Speicherung eines Schlüssels können andere Schlüssel oder ein Kennwort verwendet werden.

Der Schlüssel unter der Fußmatte

Um Daten zu verschlüsseln oder entschlüsseln muss grundsätzlich zunächst der Schlüssel geöffnet werden. Anschließend kann die Funktion zum Verschlüsseln (z.B. EncryptByKey) oder Entschlüsseln (z.B. DecryptByKey) aufgerufen werden. Um den Vorgang des Öffnens von Schlüsseln zu automatisieren, können die Schlüssel quasi automatisch geöffnet werden. Um das möglich zu machen, verwendet der SQL Server eine Verschlüsselungshierarchie, bei der ein Schlüssel automatisch mit dem übergeordneten Schlüssel geöffnet werden kann.

Datenbank- und Diensthauptschlüssel

Um die Schlüssel in einer Datenbank automatisch öffnen zu können, kann der sogenannte Datenbankhauptschlüssel verwendet werden. Wird ein Schlüssel mit dem Datenbankhauptschlüssel verschlüsselt abgespeichert, so kann er automatisch geöffnet werden, sobald der Datenbankhauptschlüssel geöffnet ist. Dadurch reicht es aus, einen Schlüssel zu öffnen, um Zugriff auf alle mit diesem Schlüssel verschlüsselten Schlüssel in der Datenbank zu erhalten (Voraussetzung dazu ist natürlich zusätzlich die Berechtigung auf die Schlüsselobjekte).

Um auch das Öffnen des Datenbankhauptschlüssels zu automatisieren, kann der Datenbankhauptschlüssel wiederum mit dem sogenannten Diensthauptschlüssel verschlüsselt abgespeichert werden. In diesem Fall muss auch der Datenbankhauptschlüssel nicht mehr explizit geöffnet werden. Der Diensthauptschlüssel wird automatisch mit Hilfe der DPAPI während der Serverinstallation erzeugt.

Diese Automatisierung bedeutet natürlich auch, dass die Daten jetzt eigentlich nur noch durch Berechtigungen geschützt sind, da keine Kennwörter benötigt werden, um einen Schlüssel in der Datenbank zu öffnen. Trotzdem hat natürlich auch dieses Vorgehen seine Berechtigung, denn die Daten können verschlüsselt abgespeichert werden, ohne dass die Anwendung explizit Anweisungen zum Öffnen und Schließen von Schlüsseln benötigt. Werden die Ver- und Entschlüsselungsmethoden in Sichten oder gespeicherten Prozeduren verwendet, so ist die Datenverschlüsselung für die Anwendung transparent. Dieses Vorgehen bietet auch hinreichenden Schutz für die Datenbanksicherungen, da eine Wiederherstellung der Daten auf einem anderen Server keinen Zugriff auf die Daten ermöglicht, denn in der Sicherung selbst ist der Diensthauptschlüssel natürlich nicht enthalten.

Daten ver- und entschlüsseln – Schritt für Schritt

Da es eine große Zahl an Verschlüsselungsmöglichkeiten gibt, hier einige Beispiele, die einige der möglichen Vorgehensweisen demonstrieren sollen.

Im ersten Beispiel werden Daten mit Hilfe eines symmetrischen Schlüssels verschlüsselt. Der symmetrische Schlüssel wird durch ein Kennwort geschützt. Zur Verschlüsselung wird der Triple-DES Algorithmus verwendet.

Als Beispieldaten sollen Bankleitzahl und Kontonummer verschlüsselt in einer Tabelle abgelegt werden:

-- Anlegen der Tabelle kontoinformationen
IF OBJECT_ID('dbo.kontoinformationen', 'U') IS NOT NULL
DROP TABLE dbo.kontoinformationen
CREATE TABLE dbo.kontoinformationen (BLZ varbinary(52), Kontonr varbinary(52))
GO

Listing: Anlegen einer Beispieltabelle zum Speichern verschlüsselter Kontoinformationen

Um die Daten verschlüsselt abspeichern zu können, wird ein symmetrischer Schlüssel erstellt:

-- Erstellen eines symmetrischen Schlüssels
--(durch Kennwort gesichert)
CREATE SYMMETRIC KEY KontoSchluessel
WITH ALGORITHM=TRIPLE_DES
ENCRYPTION BY PASSWORD = '!AmE3Zqp@NeP4$'
GO
-- Abrufen von Informationen zum erstellten Schlüssel:
SELECT * FROM sys.symmetric_keys

Listing: Erstellen eines symmetrischen Schlüssels

Abbildung>: Informationen zum erstellten Schlüssel aus der Sicht „sys.symmetric_keys“

Nachdem der Schlüssel erstellt ist, können Daten mit diesem Schlüssel verschlüsselt werden. Dazu muss der Schlüssel zunächst geöffnet werden, anschließend kann der Schlüssel in der Funktion EncryptByKey für die Verschlüsselung verwendet werden; nach dem Verwenden des Schlüssels sollte dieser wieder geschlossen werden:

-- Einfügen von Datensätzen mit Verschlüsselung
-- Öffnen des Schlüssels
OPEN SYMMETRIC KEY KontoSchluessel
DECRYPTION BY PASSWORD = '!AmE3Zqp@NeP4$'
-- Zur Info: Prüfen, ob der Schlüssel geöffnet ist:
SELECT * FROM sys.openkeys
-- Einfügen von Datensätzen
INSERT INTO dbo.kontoinformationen (BLZ, Kontonr)
VALUES (EncryptByKey(KEY_GUID('Kontoschluessel'), '70080000'),
EncryptByKey(KEY_GUID('Kontoschluessel'), '0123456789'))
INSERT INTO dbo.kontoinformationen (BLZ, Kontonr)
VALUES (EncryptByKey(KEY_GUID('Kontoschluessel'), '70080000' ),
EncryptByKey(KEY_GUID('Kontoschluessel'), '987654321' ))
-- Schließen des Symmetrischen Schlüssels
CLOSE SYMMETRIC KEY Kontoschluessel

Listing: Verschlüsseln von Datensätzen mit der Funktion „EncryptByKey“

Werden nun Daten aus der Tabelle abgerufen, so werden nur die verschlüsselten Werte angezeigt. Beim Einsatz der DES- und AES-Verschlüsselungsverfahren wird außerdem automatisch ein „Salt“-Wert verwendet, dadurch ist sichergestellt, das anhand der verschlüsselten Daten nicht erkannt werden kann, ob zwei Werte den gleichen Inhalt besitzen.

-- Abrufen von Daten ohne Entschlüsselung
SELECT * FROM dbo.kontoinformationen

Listing: Abrufen von Daten ohne Entschlüsselung

Um die verschlüsselten Daten wieder lesen zu können, muss der Schlüssel erneut geöffnet werden. Anschließend können die Daten mit Hilfe der Funktion DecryptByKey wieder entschlüsselt werden.

-- Lesen der Daten mit Entschlüsselung
-- Schlüssel öffnen
OPEN SYMMETRIC KEY Kontoschluessel
DECRYPTION BY PASSWORD = '!AmE3Zqp@NeP4$'
SELECT CAST(DecryptByKey(BLZ) AS varchar(8)) AS BLZ,
CAST(DecryptByKey(Kontonr) AS varchar(10)) AS Kontonr
FROM dbo.kontoinformationen
CLOSE SYMMETRIC KEY Kontoschluessel

Listing: Lesen von Daten mit Entschlüsselung (DecryptByKey)

Symmetrische Schlüssel können nicht nur durch Kennwörter, sondern auch durch asymmetrische Schlüssel oder Zertifikate geschützt werden. Das folgende Beispiel zeigt, wie ein symmetrischer Schlüssel mit Hilfe eines Zertifikats geschützt werden kann:

-- Erstellen eines symmetrischen Schlüssels
-- mit Schutz durch ein Zertifikat
CREATE CERTIFICATE KontoZertifikat
ENCRYPTION BY PASSWORD = '#Et4Wrzucp*'
WITH SUBJECT = 'Zugriff auf Kontoschluessel2',
START_DATE = '20060101',
EXPIRY_DATE = '20080101'
-- Erstellen eines symmetrischen Schlüssel,
-- der durch das Zertifikat verschlüsselt wird
CREATE SYMMETRIC KEY Kontoschluessel2
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE Kontozertifikat

Listing: Erstellen eines Zertifikats zum Verschlüsseln eines symmetrischen Schlüssels

Im Gegensatz zum vorangegangenen Beispiel wird in diesem Fall der Schlüssel mit Hilfe des Zertifikats geöffnet. Da der private Schlüssel des Zertifikats nicht durch den Datenbankhauptschlüssel, sondern durch ein Kennwort geschützt ist, muss dieses Kennwort beim Öffnen des Zertifikats mit angegeben werden:

-- Einfügen von Datensätzen, die mit dem Schlüssel Kontoschluessel2
-- verschlüsselt werden
OPEN SYMMETRIC KEY Kontoschluessel2
DECRYPTION BY CERTIFICATE Kontozertifikat
WITH PASSWORD = '#Et4Wrzucp*'
-- Einfügen von Datensätzen, die mit Kontoschluessel2
-- verschlüsselt werden
INSERT INTO dbo.kontoinformationen (BLZ, Kontonr)
VALUES (EncryptByKey(KEY_GUID('Kontoschluessel2'), '70040000'),
EncryptByKey(KEY_GUID('Kontoschluessel2'), '1122334455')
INSERT INTO dbo.kontoinformationen (BLZ, Kontonr)
VALUES (EncryptByKey(KEY_GUID('Kontoschluessel2'), '20020020'),
EncryptByKey(KEY_GUID('Kontoschluessel2'), '66778899'))
-- Schließen des symmetrischen Schlüssels nach der Verwendung
CLOSE SYMMETRIC KEY Kontoschluessel2

Listing: Verschlüsseln von Daten mit einem Zertifikat-geschütztem symmetrischen Schlüssel

Um die Syntax für den Zugriff auf die Daten zu vereinfachen, können die Entschlüsselungsfunktionen natürlich auch in einer Sicht verwendet werden.

-- Erstellen einer Sicht für das Entschlüsseln der Daten
IF OBJECT_ID('dbo.kontoinfosView', 'V') IS NOT NULL
DROP VIEW dbo.kontoinfosView
GO
CREATE VIEW dbo.kontoinfosView
AS
SELECT CAST(DecryptByKey(BLZ) AS varchar(8)) AS BLZ,
CAST(DecryptByKey(Kontonr) AS varchar(10)) AS Kontonr
FROM dbo.kontoinformationen
GO

Listing: Erstellen einer Sicht zum Entschlüsseln der Daten

Werden nun Daten über diese Sicht abgerufen, ohne die für die Verschlüsselung verwendeten Schlüssel zu öffnen, werden lediglich NULL-Werte angezeigt.

-- Abrufen von Daten ohne Schlüssel
SELECT * FROM dbo.kontoinfosView

Listing: Abrufen von Daten ohne geöffneten Schlüssel

Abbildung: Ohne passenden Schlüssel werden nur NULL-Werte zurückgegeben

Wird ein Schlüssel geöffnet, so können alle mit diesem Schlüssel verschlüsselten Daten wieder gelesen werden.

-- Öffnen des Schlüssels Kontoschluessel2
OPEN SYMMETRIC KEY Kontoschluessel2
DECRYPTION BY CERTIFICATE Kontozertifikat
WITH PASSWORD = '#Et4Wrzucp*'
-- Abrufen von Daten
SELECT * FROM dbo.kontoinfosView

Abbildung: Da nur Kontoschluessel2 geöffnet ist, können nur die mit diesem Schlüssel verschlüsselten Daten wieder ermittelt werden.

Natürlich können weitere Schlüssel – soweit benötigt – geöffnet werden, um wieder Zugriff auf alle Daten zu erhalten.

-- Zusätzliches Öffnen des zweiten Schlüssels
OPEN SYMMETRIC KEY KontoSchluessel
DECRYPTION BY PASSWORD = '!AmE3Zqp@NeP4$'
-- Abrufen von Daten
SELECT * FROM dbo.kontoinfosView

Listing: Öffnen eines weiteren symmetrischen Schlüssel mit Angabe eines Kennworts

Abbildung: Nur wenn alle für die Verschlüsselung verwendeten Schlüssel geöffnet sind, können alle Daten auch wieder entschlüsselt werden

Nach der Verwendung der Schlüssel sollten alle Schlüssel wieder geschlossen werden:

-- Schließen aller geöffneten Schlüssel
CLOSE ALL SYMMETRIC KEYS

Listing: Schließen aller offenen symmetrischen Schlüssel

Beim Entschlüsseln der Daten wird automatisch der richtige Schlüssel verwendet. Dies ist möglich, da in den ersten 16-Bytes der verschlüsselten Daten hinterlegt ist, mit welchem Schlüssel die Daten verschlüsselt wurden. Die folgende Abfrage zeigt für jede verschlüsselte Bankleitzahl den Namen des verwendeten Schlüssels an:

SELECT name, BLZ
FROM sys.symmetric_keys
INNER JOIN dbo.kontoinformationen
ON CAST(BLZ AS varbinary(16)) = CAST(key_guid AS varbinary(16))

Abbildung: Ermitteln des verwendeten Schlüssels für jeden einzelnen Datensatz

Die Hauptschlüssel

Bei allen bisherigen Beispielen mussten die Schlüssel explizit durch Angabe eines Kennworts geöffnet werden. Mit Hilfe des Datenbankhauptschlüssels können asymmetrische Schlüssel und Zertifikate ohne Angabe eines Kennworts geöffnet werden. Um diese Möglichkeit nutzen zu können, muss in der Datenbank zunächst ein Datenbankhauptschlüssel angelegt werden. Dieser Schlüssel wird zum einen mit dem angegebenen Kennwort verschlüsselt gespeichert; zusätzlich wird automatisch eine durch den Diensthauptschlüssel der Instanz verschlüsselte Kopie gespeichert.

Nachdem der Datenbankhauptschlüssel erstellt ist, kann der Kennwortschutz vom Zertifikat entfernt werden. Das Zertifikat ist anschließend durch den Datenbankhauptschlüssel geschützt.

-- Erstellen des Datenbankhauptschlüssels
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '§Bzm7/Uc.W'
-- Schützen des Zertifikats durch den Datenbankhauptschluessel
ALTER CERTIFICATE KontoZertifikat
WITH PRIVATE KEY (DECRYPTION BY PASSWORD = '#Et4Wrzucp*')

Listing: Erstellen eines Datenbankhauptschlüssels und Verschlüsseln eines bestehenden Zertifikats mit dem Datenbankhauptschlüssel

Schutz von asymmetrischen Schlüsseln

Während symmetrische Schlüssel mehrfach verschlüsselt gespeichert werden können (d.h. es sind mehrere Kopien des Schlüssel vorhanden), wird bei asymmetrischen Schlüsseln (also auch bei Zertifikaten) jeweils der private Schlüssel durch eine einzige Verschlüsselungsart gesichert. Da es nicht möglich ist, einen privaten Schlüssel unverschlüsselt zu Speichern, muss beim Entfernen der Verschlüsselung eine neue Verschlüsselungsart angegeben werden. Ist ein Datenbankhauptschlüssel vorhanden, so wird dieser für die Verschlüsselung verwendet, falls keine Verschlüsselung angegeben wird.

Wie der private Schlüssel verschlüsselt wurde, kann über die Sichten sys.asymmetric_keys bzw. sys.certificates in der Spalte „pvt_key_encryption_type“ ermittelt werden.

Da eine Version der Datenbankhauptschlüssels durch den Diensthauptschlüssel entschlüsselt werden kann, kann das Zertifikat jetzt ohne Angabe eines Kennworts geöffnet werden. Dadurch ist auch für das Öffnen des symmetrischen Schlüssels „Kontoschluessel2“ kein Kennwort mehr erforderlich:

-- Öffnen des symmetrischen Schlüssels Kontoschluessel2
OPEN SYMMETRIC KEY Kontoschluessel2
DECRYPTION BY CERTIFICATE KontoZertifikat
-- Abrufen von Daten
SELECT * FROM dbo.kontoinfosView
-- Schließen des symmetrischen Schlüssels nach der Verwendung
CLOSE SYMMETRIC KEY Kontoschluessel2

Listing: Öffnen eines symmetrischen Schlüssels ohne Kennwortangabe

Wie die symmetrischen Schlüssel in der Datenbank jeweils geschützt sind kann natürlich auch über die Systemsichten ermittelt werden. Im bisherigen Beispiel wurde der symmetrische Datenbankhauptschlüssel zum einen per Kennwortschutz abgelegt, zum anderen auch durch eine Verschlüsselung mit dem Diensthauptschlüssel. Der symmetrische Schlüssel „Kontoschluessel2“ wird durch das Zertifikat „Kontozertifikat“ geschützt und der Schlüssel „Kontoschluessel“ ist durch ein Kennwort geschützt.

-- Wodurch sind die symmetrischen Schlüssel geschützt?
SELECT name, algorithm_desc, key_length, crypt_type_desc
   FROM sys.key_encryptions ken
   INNER JOIN sys.symmetric_keys syk
   ON syk.symmetric_key_id = ken.key_id

Listing: Ermitteln der Verschlüsselung von symmetrischen Schlüsseln

Abbildung: Übersicht der Verschlüsselungstypen für alle symmetrischen Schlüssel in der Datenbank

Soll die Möglichkeit den Datenbankhauptschlüssel mit Hilfe des Diensthauptschlüssels zu entschlüsseln unterbunden werden, so kann diese Verschlüsselung entfernt werden; in diesem Fall muss der Datenbankhauptschlüssel vor jeder weiteren Verwendung explizit mit Angabe des Kennworts geöffnet werden:

-- Entfernen der Diensthauptschlüssel-Verschlüsselung vom -- DatenbankhauptschlüsselALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY -- Der Datenbankhauptschlüssel muss jetzt explizit geöffnet werdenOPEN MASTER KEY DECRYPTION BY PASSWORD = '§Bzm7/Uc.W'

-- Anschließend kann das Zertifikat ohne Kennwort geöffnet werden
OPEN SYMMETRIC KEY Kontoschluessel2
DECRYPTION BY CERTIFICATE KontoZertifikat
-- Prüfen, welche Schlüssel geöffnet sind
SELECT * FROM sys.openkeys
-- Abrufen von Daten
SELECT * FROM dbo.kontoinfosView
-- Schließen von Kontoschluessel2 und Datenbankhauptschlüssel
CLOSE ALL SYMMETRIC KEYS

Listing: Verwenden eines Datenbankhauptschlüssels, der nicht durch den Diensthauptschlüssel gesichert wird

Dieses erste umfangreiche Beispiel hat die wichtigsten Vorgehensweisen bei der Verschlüsselung gezeigt, trotzdem bleiben natürlich noch eine Reihe wichtiger Fragen offen.

Sicherung von Schlüsseln und Zertifikaten

Der Verlust eines Schlüssels macht das Entschlüsseln der Daten unmöglich, daher ist es unbedingt notwendig ein Konzept für die Sicherung und Wiederherstellung der verschiedenen Schlüssel parat zu haben. Je nach Art des Schlüssels sind unterschiedliche Möglichkeiten der Sicherung und Wiederherstellung gegeben

Der Diensthauptschlüssel

Der Diensthauptschlüssel wird auch verwendet, um die Kennwörter von SQL Server Anmeldungen, Informationen über Verbindungsserver und Anmeldenamen verwendet. Daher sollte in jedem Fall ein Backup des Diensthauptschlüssel erstellt und sicher aufbewahrt werden:

-- Sichern des Diensthauptschluessels
BACKUP SERVICE MASTER KEY
TO FILE = 'C:\SAFE\inst05.smk'
ENCRYPTION BY PASSWORD = '2P%tsKDnT2gTmotS&sPd'

Listing: Erstellen einer Sicherungsdatei für den Diensthauptschlüssel

Da die Datenbankhauptschlüssel auch mit dem zugehörigen Kennwort wieder geöffnet werden können, ist der Diensthauptschlüssel für den Zugriff auf den Datenbankschlüssel und alle mit diesem verschlüsselten Objekte nicht zwingend erforderlich.

Der Datenbankhauptschlüssel

Auch der Datenbankhauptschlüssel sollte in jedem Fall separat gesichert werden, falls der Schlüssel dazu verwendet wird asymmetrische Schlüssel und Zertifikate in der Datenbank abzusichern.

-- Sichern des Datenbankhauptschlüssels
OPEN MASTER KEY
DECRYPTION BY PASSWORD = '§Bzm7/Uc.W'
BACKUP MASTER KEY TO FILE = 'C:\SAFE\dbname.key'
ENCRYPTION BY PASSWORD = 'a@wtfDuW23!X#';

Listing: Sichern des Datenbankhauptschlüssels in einer Datei

Zertifikate

Wurde das Zertifikat von einer externen Quelle importiert, so sollten bereits Sicherungen des öffentlichen und privaten Schlüssels vorliegen. Handelt es sich um ein vom SQL Server selbst signiertes Zertifikat, wie im vorangegangen Beispiel, so kann das Zertifikat mit der Anweisung BACKUP CERTIFICATE gesichert werden. Dabei werden der öffentliche und der private Schlüssel in zwei getrennten Dateien abgelegt. Der private Schlüssel wird durch ein Kennwort geschützt:

-- Sichern eines Zertifikats
BACKUP CERTIFICATE KontoZertifikat
   TO FILE = 'C:\SAFE\Kontozertifikat.cer'
   WITH PRIVATE KEY
   ( FILE = 'C:\SAFE\Kontozertifikat.pvk',
   ENCRYPTION BY PASSWORD = '*2W@3UgmLs8Db',
   DECRYPTION BY PASSWORD = '#Et4Wrzucp*' )

Listing: Sichern des öffentlichen und privaten Schlüssels eines Zertifikats in Dateien

Ein so gesichertes Zertifikat kann mit Hilfe der CREATE CERTIFICATE-Anweisung wieder erstellt werden. Das Vorgehen dabei ist das gleiche wie beim Importieren bereits bestehender Zertifikate:

CREATE CERTIFICATE KontoZertifikat
   FROM FILE = 'C:\SAFE\Kontozertifikat.cer'
   WITH PRIVATE KEY
   (FILE = 'C:\SAFE\Kontozertifikat.pvk',
DECRYPTION BY PASSWORD = '*2W@3UgmLs8Db',
ENCRYPTION BY PASSWORD='!n§pr1Ras6Ha5!');
GO

Listing: Erstellen eines Zertifikats aus Zertifikatsdatei und privatem Schlüssel

Asymmetrische Schlüssel

Asymmetrische Schlüssel, die direkt im SQL Server 2005 erstellt wurden, können nicht explizit gesichert werden. Um diese Schlüssel wieder herzustellen, muss eine Datenbanksicherung verwendet werden. Wurde der Schlüssel jedoch aus einer Datei oder einer Assembly extrahiert, so kann der Schlüssel natürlich erneut geladen werden:

-- Erstellen eins asymmetrischen Schlüssels
-- aus einer „strong-name“ Datei
CREATE ASYMMETRIC KEY AsymSchluessel
FROM FILE = 'C:\asymkey.snk'
ENCRYPTION BY PASSWORD = 'sK4)[@ww2kTs'

Listing: Erstellen eines asymmetrischen Schlüssels aus einer „Strong-Name“ Schlüsseldatei

Symmetrische Schlüssel

Da für die eigentliche Verschlüsselung der Daten in der Regel symmetrische Schlüssel verwendet werden, dürfen diese Schlüssel natürlich auch nicht verloren gehen. Eine explizite Sicherung der Schlüssel ist jedoch nicht möglich. Um einen symmetrischen Schlüssel wiederherzustellen muss auch hier in der Regel die Datenbank wiederhergestellt werden.

Leider ist im SQL Server kein Schutz vor dem Löschen eines symmetrischen Schlüssels möglich (Keine Panik! Das bedeutet natürlich nicht, dass ein symmetrischer Schlüssel einfach „von jedem“ gelöscht werden kann. Für die DROP SYMMETRIC KEY-Anweisung wird die CONTROL-Berechtigung für den Schlüssel benötigt).

Die Anweisung DROP SYMMETRIC KEY kann übrigens auch nicht durch DDL-Triggern abgefangen bzw. verhindert werden.

Falls man eine exakte Kopie eines symmetrischen Schlüssels erzeugen möchte, muss der Schlüssel mit einer „Passphrase“ (also einem Kennsatz – im Gegensatz zu einem Kennwort) und einem Identitätsausdruck erzeugt werden. Der Identitätsausdruck wird für das Generieren der GUID des Schlüssels verwendet und muss daher in der Datenbank eindeutig sein. Wird der Schlüssel gelöscht, so kann ein identischer Schlüssel erzeugt werden, indem die gleiche Passphrase und der gleiche Identitätsausdruck wie beim Originalschlüssel angegeben werden; natürlich muss bei der Erzeugung auch der gleiche Verschlüsselungsalgorithmus gewählt werden, der Name des neuen Schlüssel und die Methode zum Verschlüsseln des Schlüssels selbst müssen hingegen nicht mit dem Originalschlüssel übereinstimmen.

Im folgenden Beispiel wird ein symmetrischer Schlüssel („LostIt“) mit Passphrase und Identitätswert erzeugt. Nach dem Verschlüsseln von Daten wird der Schlüssel gelöscht. Der neue Schlüssel „FoundIt“ verwendet die gleichen Werte für die Passphrase und den Identitätswert. Dadurch kann der neue Schlüssel für das Entschlüsseln der Daten verwendet werden.

-- Erstellen eines neuen symmetrischen Schlüssels
CREATE SYMMETRIC KEY LostIt
   WITH ALGORITHM=TRIPLE_DES,
   KEY_SOURCE = 'Leichter zu merken als zu erraten',
IDENTITY_VALUE = 'Das gibt es nur einmal, das kommt nie wieder.'
   ENCRYPTION BY PASSWORD = '!AmE3Zqp@NeP4$'
-- Öffnen des Schlüssels
OPEN SYMMETRIC KEY LostIt
DECRYPTION BY PASSWORD = '!AmE3Zqp@NeP4$'
-- Verschlüsseln eines Datensatzes mit dem neuen Schlüssel
INSERT INTO dbo.kontoinformationen (BLZ, Kontonr)
VALUES (EncryptByKey(KEY_GUID('LostIt'), '70080000' ), EncryptByKey(KEY_GUID('LostIt'), '0123456789'))
-- Abrufen der Daten mit Entschlüsselung
SELECT CAST(DecryptByKey(BLZ) AS varchar(8)) BLZ, CAST(DecryptByKey(Kontonr) AS varchar(10)) Kontonr
FROM dbo.kontoinformationen
-- Schließen des Schlüssels und anschließendes Löschen
CLOSE SYMMETRIC KEY LostIt
DROP SYMMETRIC KEY LostIt
-- Erstellen eines neuen symmetrischen Schlüssels mit identischen
-- Schlüsseloptionen:
CREATE SYMMETRIC KEY FoundIt
   WITH ALGORITHM = TRIPLE_DES,
   KEY_SOURCE = 'Leichter zu merken als zu erraten',
   IDENTITY_VALUE = 'Das gibt es nur einmal, das kommt nie wieder.'
   ENCRYPTION BY PASSWORD = 'wAlIdN32R@'
-- Der neue Schlüssel kann verwendet werden, um die mit “LostIt” verschlüsselten Daten zu lesen:
OPEN SYMMETRIC KEY FoundIt
   DECRYPTION BY PASSWORD = 'wAlIdN32R@'
SELECT CAST(DecryptByKey(BLZ) AS varchar(8)) BLZ,
CAST(DecryptByKey(Kontonr) AS varchar(10)) Kontonr
FROM dbo.kontoinformationen
CLOSE ALL SYMMETRIC KEYS

Listing: Beispiel für das Erstellen eines identischen symmetrischen Schlüssel nach Verlust des Originals

Autor: svenh
erstellt: juni 2006
online: oktober 2006
letzte änderung:

Link-Tipp zum Thema "SQL": www.sqlteam.com/

Sehr umfangreich Site rund um den SQL Server