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 Blogbeitrag 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:

USE SSISConfig
GO

IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)  
BEGIN
	CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pl24s2 s2l2ct a g33d m4st2r p4ssw3rd h2r2';
END
GO

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

IF NOT EXISTS (SELECT * FROM sys.certificates WHERE [name] = 'SSISConfigCert')  
BEGIN
	CREATE CERTIFICATE SSISConfigCert
		WITH SUBJECT = 'SSISConfig Encryption Certificate',
		EXPIRY_DATE = '20991231';
END

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:

IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE [name] = 'SSISConfigSymmetricKey')  
BEGIN
	CREATE SYMMETRIC KEY SSISConfigSymmetricKey
		WITH ALGORITHM = AES_256
		ENCRYPTION BY CERTIFICATE SSISConfigCert;
END

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.

IF OBJECT_ID('dbo.T_SSIS_Configurations') IS NOT NULL
	DROP TABLE dbo.T_SSIS_Configurations;
GO

CREATE TABLE dbo.T_SSIS_Configurations
(
	ConfigurationFilter NVARCHAR(255) NOT NULL
	,ConfiguredValue VARBINARY(MAX) NULL -- Die geänderte Spalte im Vergleich mit der SSIS-Standardkonfigutationstabelle
	,PackagePath NVARCHAR(255) NOT NULL 
	,ConfiguredValueType NVARCHAR(20) NOT NULL
	,IsEncryptedValue BIT NOT NULL -- Eine neue Spalte

	,CONSTRAINT PK_T_SSIS_Configurations PRIMARY KEY CLUSTERED (ConfigurationFilter) 
);   
GO

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.

IF OBJECT_ID('dbo.V_SSIS_Configurations') IS NOT NULL
	DROP VIEW dbo.V_SSIS_Configurations;
GO
	
CREATE VIEW dbo.V_SSIS_Configurations
AS
SELECT 
	ConfigurationFilter
	,CASE
		WHEN IsEncryptedValue = 1 THEN
			CAST(DecryptByKeyAutoCert(Cert_ID(N'SSISConfigCert'), NULL, Config-uredValue) AS NVARCHAR(255)) 
		ELSE
			CAST(ConfiguredValue AS NVARCHAR(255)) 
		END AS ConfiguredValue
	,PackagePath
	,ConfiguredValueType
	,IsEncryptedValue
FROM 
	dbo.T_SSIS_Configurations;
GO

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.

IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OB-JECT_ID(N'dbo.V_SSIS_Configurations_Trigger'))
	DROP TRIGGER dbo.V_SSIS_Configurations_Trigger;
GO

CREATE TRIGGER dbo.V_SSIS_Configurations_Trigger ON dbo.V_SSIS_Configurations
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
	-- Der Schlüssel muss geöffnet werden, um EncryptByKey zu verwenden.
    OPEN SYMMETRIC KEY SSISConfigSymmetricKey
    DECRYPTION BY CERTIFICATE SSISConfigCert;
    
	-- Bei einem UPDATE sind die alten Daten in der deleted-Tabelle und die neuen in der inserted-Tabelle vorhanden.
    MERGE dbo.T_SSIS_Configurations AS dest
    USING inserted AS src

    ON
    (
		dest.ConfigurationFilter = src.ConfigurationFilter
    )
	WHEN MATCHED THEN
		UPDATE SET 
			dest.ConfiguredValue = 
			CASE
				WHEN src.IsEncryptedValue = 1 THEN
					EncryptByKey(Key_Guid(N'SSISConfigSymmetricKey'), src.ConfiguredValue)
				ELSE
					CAST(src.ConfiguredValue AS VARBINARY(MAX))
			END
			,dest.PackagePath = src.PackagePath
			,dest.ConfiguredValueType = src.ConfiguredValueType
			,dest.IsEncryptedValue = src.IsEncryptedValue
	WHEN NOT MATCHED BY TARGET THEN
		INSERT
		(
			ConfigurationFilter
			,ConfiguredValue
			,PackagePath
			,ConfiguredValueType
			,IsEncryptedValue
		)
		VALUES
		(
			src.ConfigurationFilter
			,CASE
				WHEN src.IsEncryptedValue = 1 THEN
					EncryptByKey(Key_Guid(N'SSISConfigSymmetricKey'), src.ConfiguredValue)
				ELSE
					CAST(src.ConfiguredValue AS VARBINARY(MAX))
			END
			,src.PackagePath
			,src.ConfiguredValueType
			,src.IsEncryptedValue
		);

    CLOSE SYMMETRIC KEY SSISConfigSymmetricKey;
END;
GO

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.

USE SSISConfig
GO

DELETE FROM dbo.T_SSIS_Configurations;

INSERT INTO dbo.V_SSIS_Configurations
(
	ConfigurationFilter
	,ConfiguredValue
	,PackagePath
	,ConfiguredValueType
	,IsEncryptedValue
)
VALUES
(
    'SourceConnectionString'
    ,'Data Source=.\SQL2014;Initial Catalog=SSISConfigTestImport;Provider=SQLNCLI11.1;User ID=SSISConfigExecutePackagesUser;Password=bc#2017;'
    ,'\Package.Connections[Source].Properties[ConnectionString]'
    ,'String'
	,1
);

INSERT INTO dbo.V_SSIS_Configurations
(
	ConfigurationFilter
	,ConfiguredValue
	,PackagePath
	,ConfiguredValueType
	,IsEncryptedValue
)
VALUES
(
    'DestinationConnectionString'
    ,'Data Source=.\SQL2014;Initial Cata-log=SSISConfigTestImport;Provider=SQLNCLI11.1;Integrated Security=SSPI;'
    ,'\Package.Connections[Destination].Properties[ConnectionString]'
    ,'String'
	,0
);
GO

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.

USE SSISConfig
GO

IF EXISTS(SELECT * FROM dbo.sysusers WHERE name = 'SSISConfigTestImportUser1')
	DROP USER SSISConfigTestImportUser1;

IF EXISTS(SELECT * FROM master.dbo.syslogins WHERE loginname = 'SSISConfigTestImportUser1')
	DROP LOGIN SSISConfigTestImportUser1;

CREATE LOGIN [SSISConfigTestImportUser1] WITH PASSWORD=N'bc#2017', DE-FAULT_DATABASE=[SSISConfig], DEFAULT_LANGUAGE=[Deutsch], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE USER [SSISConfigTestImportUser1] FOR LOGIN [SSISConfigTestImportUser1]
ALTER ROLE [db_datareader] ADD MEMBER [SSISConfigTestImportUser1]
GO

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

USE SSISConfig
GO

SELECT * FROM dbo.T_SSIS_Configurations
SELECT * FROM dbo.V_SSIS_Configurations
GO

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.

USE SSISConfig
GO

GRANT CONTROL ON CERTIFICATE::SSISConfigCert TO SSISConfigTestUser1;
GRANT VIEW DEFINITION ON SYMMETRIC KEY::SSISConfigSymmetricKey TO SSISConfigTestUser1;
GO

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

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.

USE SSISConfigTestImport
GO

IF EXISTS(SELECT * FROM dbo.sysusers WHERE name = 'SSISConfigExecutePackagesUser')
	DROP USER SSISConfigExecutePackagesUser;

IF EXISTS(SELECT * FROM master.dbo.syslogins WHERE loginname = 'SSISConfigExecutePackagesUs-er')
	DROP LOGIN SSISConfigExecutePackagesUser;

CREATE LOGIN [SSISConfigExecutePackagesUser] WITH PASSWORD=N'bc#2017', DE-FAULT_DATABASE=[SSISConfig], DEFAULT_LANGUAGE=[Deutsch], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE USER [SSISConfigExecutePackagesUser] FOR LOGIN [SSISConfigExecutePackagesUser]
ALTER ROLE [db_datareader] ADD MEMBER [SSISConfigExecutePackagesUser]
GO

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

USE SSISConfigTestImport
GO

IF OBJECT_ID('dbo.T_STAGING_Table01') IS NOT NULL
	DROP TABLE dbo.T_STAGING_Table01;
GO

CREATE TABLE dbo.T_STAGING_Table01
(
	ID INT NOT NULL
	,[Name] NVARCHAR(50) NOT NULL
);

INSERT INTO dbo.T_STAGING_Table01 (ID, [Name]) VALUES (1, 'Name 1');
INSERT INTO dbo.T_STAGING_Table01 (ID, [Name]) VALUES (2, 'Name 2');
GO

IF OBJECT_ID('dbo.T_IMPORT_Table01') IS NOT NULL
	DROP TABLE dbo.T_IMPORT_Table01;
GO

CREATE TABLE dbo.T_IMPORT_Table01
(
	ID INT NOT NULL
	,[Name] NVARCHAR(50) NOT NULL
);
GO

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

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

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

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

Die Verbindung zur Konfigurationsdatenbank ist folgendermaßen konfiguriert:

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

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

USE SSISConfig
GO

-- Ein falsches Passwort angeben.
UPDATE t SET
	ConfiguredValue = 'Data Source=.\SQL2014;Initial Cata-log=SSISConfigTestImport;Provider=SQLNCLI11.1;User ID=SSISConfigExecutePackagesUser;Password=bc#2017_1;'
FROM
	dbo.V_SSIS_Configurations AS t
WHERE
	ConfigurationFilter = 'SourceConnectionString';
GO

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

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.

USE SSISConfig
GO

-- Das korrekte Passwort angeben.
UPDATE t SET
	ConfiguredValue = 'Data Source=.\SQL2014;Initial Cata-log=SSISConfigTestImport;Provider=SQLNCLI11.1;User ID=SSISConfigExecutePackagesUser;Password=bc#2017;'
FROM
	dbo.V_SSIS_Configurations AS t
WHERE
	ConfigurationFilter = 'SourceConnectionString';
GO

Der Test des SSIS-Paketes war erfolgreich.

Fazit

Dieser Blogbeitrag 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.