Suchen...
Generic filters
Exact matches only
Search in title
Search in excerpt
Search in content

SSIS, Passwörter und SQL Server-Verschlüsselung

Wie verwendet und speichert man sensible Informationen wie Passwörter in SSIS? Ist eine SQL Server-Datenbank für das Speichern von solchen sensiblen Informationen gut geeignet? Dieser Beitrag gibt die Antworten auf diese Fragen.

Bei vielen Business Intelligence-Projekten kommt es oft vor, dass Quellsysteme, aus denen die Daten extrahiert werden müssen, keine Windows-Authentifizierung unterstützen. In solchen Fällen müssen Benutzernamen und Passwörter für die Systemanmeldungen in SSIS-Paketen verwendet werden, was bestimmte Sicherheitsrisiken bringt, wenn Passwörter direkt in Paketen gespeichert werden. SSIS bringt die folgenden Möglichkeiten für die Verwaltung von sensiblen Daten in Paketen (die Eigenschaft ProtectionLevel auf der Paketebene) mit:

  • DontSaveSensitive: Keine sensiblen Informationen wie Passwörter werden im Paket gespeichert – auch während des Entwicklungsprozesses. Diese Option wirkt aus Sicherheitssicht als gute Option.
  • EncryptSensitiveWithUserKey: Sensible Informationen werden mit dem UserKey des Benutzers, der das Paket entwickelt, verschlüsselt. Der Nachteil von dieser Option ist, dass das Paket später nur mit dem Konto des Entwicklers ausgeführt werden kann, was im Arbeitsalltag normalerweise keine passende Lösung ist.
  • EncryptSensitiveWithPassword: Sensible Informationen werden mit einem Passwort geschützt und dieses Passwort muss während der Paketausführung angegeben werden – es verlagert das Sicherheitsproblem einfach auf eine andere Ebene.
  • EncryptAllWithUserKey: Das komplette Paket wird mit dem UserKey verschlüsselt. Diese Lösung hat dieselben Nachteile wie EncryptSensitiveWithUserKey.
  • EncryptAllWithPassword: Das komplette Paket wird mit einem Passwort geschützt. Diese Lösung hat dieselben Nachteile wie EncryptSensitiveWithPassword.

Nach der Analyse der möglichen Optionen für die Verwaltung von sensiblen Daten in Paketen kann man beschließen, dass die DontSaveSensitive-Option die beste Option aus der Sicherheitsperspektive ist. Für diese Option muss eine sichere Lösung gefunden werden, wie die Passwörter an das Paket während des Entwicklungsprozesses und der Paketausführung übergeben werden können. Das Speichern von sensiblen Informationen als Klartext soll überall vermieden werden – in einem SSIS-Paket, in einer Konfigurationsdatei, in einem SQL Server Agent-Auftrag, in einer SQL Server-Tabelle. Aus diesen Gründen kommt eine XML-Konfigurationsdatei für SSIS nicht in Frage. Und wie sieht es mit den Datenbankkonfigurationen aus, wenn Konfigurationsparameter in einer Tabelle einer SQL Server-Datenbank gespeichert werden und beim Öffnen des Paketes aus der Datenbank gelesen werden? Es klingt im ersten Moment gut, aber die Konfigurationswerte werden in der Konfigurationstabelle standardgemäß als Klartext gespeichert. Glücklicherweise bietet SQL Server gute Möglichkeiten für die Verschlüsselung der Daten. Eine dieser Möglichkeiten ist die Verschlüsselung auf Zellebene einer Tabelle (Cell Level Encryption), wo man entscheiden kann, welche Zellen bzw. Spalten von welchen Tabellen verschlüsselt werden sollen.

Szenario

Es wird gezeigt, wie die Verschlüsselung auf der Zellebene innerhalb von SQL Server funktioniert, um sensible Informationen wie Passwörter bzw. Connection Strings mit Passwörtern verschlüsselt in einer SQL Server-Konfigurationstabelle zu speichern und wie diese verschlüsselten Daten als Datenbankkonfigurationen in einem SSIS-Paket verwendet werden können. Als Deployment-Ziel für das SSIS-Paket wird das Dateisystem verwendet, was bei vielen Systemen eine der Anforderungen ist.
Für die Durchführung dieses Szenarios müssen zwei Datenbanken auf dem SQL Server erstellt werden:

  • SSISConfig: Die Datenbank wird für das Speichern der verschlüsselten Konfigurationen verwendet.
  • SSISConfigTestImport: Die Datenbank wird für das Testen des SSIS-Paketes mit verschlüsselten Datenbankkonfigurationen verwendet.

Vorbereitung der SQL Server Server-Datenbank

Für eine für Endanwender und Applikationen transparente Ver- und Entschlüsselung der Daten wird ein Master Key in der Datenbank SSISConfig, in der später die SSIS-Konfigurationsobjekte gespeichert werden, erstellt:

Danach wird ein Zertifikat erstellt, welches mit dem Master Key verschlüsselt wird:

Die Ver- und Entschlüsselung der Zellen in der Konfigurationstabelle wird mit einem symmetrischen Schlüssel erfolgen, der mit dem früher erstellten Zertifikat verschlüsselt wird:

Die SSIS-Konfigurationstabellen können beliebige Namen haben – die einzelne Voraussetzung ist, dass die Spaltennamen und -Datentypen der SSIS-Spezifikation entsprechen. Für dieses Szenario wird eine Konfigurationstabelle erstellt, die die folgenden Abweichungen von der Standardspezifikation aufweist:

  • Die Spalte ConfiguredValue hat den Datentyp VARBINARY(MAX) anstatt NVARCHAR(255). Die Informationen werden in dieser Spalte verschlüsselt gespeichert, falls IsEncryptedValue = 1 ist.

Die erstellte Tabelle entspricht nicht der SSIS-Spezifikation und kann verschlüsselte Daten in der Spal-te ConfiguredValue enthalten, deswegen wird eine View für die Verwendung mit SSIS erstellt, die die Daten entschlüsselt und der Spezifikation entspricht.

Für eine transparente Verwaltung der Daten in der Konfigurationstabelle (INSERT, UPDATE) ist ein Trigger für die V_SSIS_Configurations-View notwendig, der die Änderungen an die T_SSIS_Configurations-Tabelle weiterleitet.

Jetzt können die Konfigurationsparameter hinzugefügt werden. Der Wert für den Parameter SourceConnectionString enthält ein Passwort und wird verschlüsselt. Der Parameter Destination-ConnectionString wird unverschlüsselt gespeichert. Als Quell- und Zieldatenbank wird die SSISConfig-TestImport-Datenbank später verwendet.

Alle Vorbereitungen für die Verwendung der verschlüsselten Konfigurationen wurden getroffen.

Test der SQL Server Server-Datenbankverschlüsselung

Um die verschlüsselten Konfigurationen zu testen, wird ein Testbenutzer mit den Leseberechtigungen in der Konfigurationsdatenbank erstellt.

Es wird mit diesem Testbenutzer in SSMS eingeloggt und die folgenden Abfragen werden ausgeführt:

Abbildung 1 Inhalt der Konfigurationstabelle ohne entsprechende Berechtigungen

Abbildung 1 Inhalt der Konfigurationstabelle ohne entsprechende Berechtigungen

Man kann sehen, dass der Wert für den Parameter SourceConnectionString in der V_SSIS_Configurations-View für den Testbenutzer als NULL dargestellt wird, da dieser Benutzer noch keine entsprechenden Berechtigungen hat, um die Daten zu entschlüsseln. Für die Entschlüsselung der Daten sind die CONTROL-Berechtigung für das Zertifikat und die VIEW DEFINITION-Berechtigung für den symmetrischen Schlüssel notwendig.

Nachdem die notwendigen Berechtigungen dem Testbenutzer erteilt wurden, wird der Inhalt der ConfiguredValue-Spalte als entschlüsselt gezeigt:

Abbildung 2 Inhalt der Konfigurationstabelle mit entsprechenden Berechtigungen

Abbildung 2 Inhalt der Konfigurationstabelle mit entsprechenden Berechtigungen

Vorbereitung des SSIS-Paketes

Für den Test der verschlüsselten Konfigurationen in SSIS wird ein Testbenutzer mit der SQL Server-Authentifizierung in der SSISConfigTestImport-Datenbank angelegt.

Auch werden die folgenden Tabellen in der SSISConfigTestImport-Datenbank angelegt:

Jetzt kann ein Test-SSIS-Paket erstellt werden. Das Paket hat die folgende Struktur:

Abbildung 3 Struktur des SSIS-Paketes

 

 3.1 Struktur des SSIS-Paketes

Abbildung 3 Struktur des SSIS-Paketes

Die Verbindung SSISConfig zeigt auf die Konfigurationsdatenbank und wird mit Hilfe der SSISConnec-tionString-Variable gesteuert. Falls während der Paketausführung eine andere Konfigurationsdaten-bank verwendet werden soll, kann der Wert dieser Variable mit einem neuen Wert überschrieben werden.

Abbildung 4 Konfiguration der Verbindung mit der Konfigurationsdatenbank

Abbildung 4 Konfiguration der Verbindung mit der Konfigurationsdatenbank

Die Verbindungen Source und Destination werden mit Hilfe von den Datenbankkonfigurationen konfiguriert:

Abbildung 5 Datenbankkonfigurationen in SSIS

Abbildung 5 Datenbankkonfigurationen in SSIS

Test des SSIS-Paketes

Wenn das SSIS-Paket in Data Tools geöffnet wird, werden die Konfigurationswerte aus der Konfigurationsdatenbank gelesen. Wenn der Benutzer die entsprechenden Berechtigungen in der Konfigurati-onsdatenbank hat, werden die verschlüsselten Werte automatisch entschlüsselt.
Für den Test des SSIS-Paketes wird ein SQL Server Agent-Auftrag mit der folgenden Konfiguration erstellt:

Abbildung 6 SQL Server Agent-Auftrag für den Test des SSIS-Paketes

Abbildung 6 SQL Server Agent-Auftrag für den Test des SSIS-Paketes

Die Verbindung zur Konfigurationsdatenbank ist folgendermaßen konfiguriert:

Abbildung 7 Die Verbindung zur Konfigurationsdatenbank

Abbildung 7 Die Verbindung zur Konfigurationsdatenbank

Das SQL Server Protokoll nach einer erfolgreichen Auftragsausführung sieht so aus:

Abbildung 8 SQL Server Protokoll nach einer erfolgreichen Auftragsausführung

Abbildung 8 SQL Server Protokoll nach einer erfolgreichen Auftragsausführung

Was passiert, wenn ein falsches Passwort für die Quelldatenbank angegeben wird? Wird die geänderte Konfiguration während der Paketausführung gelesen?

In diesem Fall wird der Auftrag mit einem Fehler beendet, was den Erwartungen entspricht:

Abbildung 9 SQL Server Protokoll nach einer fehlerhaften Auftragsausführung

Abbildung 9 SQL Server Protokoll nach einer fehlerhaften Auftragsausführung

Am Ende wird die Konfiguration für die Quelldatenbank auf den richtigen Wert zurückgesetzt und der Auftrag wird wieder ohne Fehler ausgeführt.

Der Test des SSIS-Paketes war erfolgreich.

Fazit

Dieser Beitrag hat das Folgende demonstriert:

  • Die sensiblen Informationen müssen nirgendwo als Klartext gespeichert werden.
  • Auch wenn SSIS-Pakete ins Dateisystem bereitgestellt werden, was bei vielen Systemen eine der Deployment-Anforderungen ist, enthalten die Pakete und die SQL Server Agent-Aufträge keine sensiblen Informationen wie Passwörter bzw. Connection Strings mit Passwörtern.
  • Die sensiblen Informationen werden in der Konfigurationsdatenbank sicher verschlüsselt und können von unberechtigten Personen nicht gelesen werden. Die Steuerung der Datenbankbe-rechtigungen für das Lesen der sensiblen Informationen ist einfach.
  • Die verschlüsselten Konfigurationen sind einfach einzurichten, sie sind sicher und sie können in ähnlichen Szenarien verwendet werden.