Hatten Sie auch schon einmal das Problem beim Datenimport: Während der Entwicklung der SSIS-Pakete im BI-Development-Studio liefen alle Tests wunderbar durch. Der Flatfile-Import von einem Netzlaufwerk oder der Datenimport aus den Tabellen eines anderen SQL-Servers. Jetzt nur noch schnell einen Import-Job im SQL-Server-Agent anlegen und einen dicken Haken hinter das Thema “Datenimport” machen.
…Aber was ist das? Sobald man den Job über den SQL-Server-Agent anstößt, ist nichts mehr wie vorher – die Fehlermeldungen überschlagen sich, obwohl doch gar nichts anders ist als vorher…
Doch weit gefehlt – hier kann es gleich mehrere Ursachen geben, warum der Import-Job trotz vorherigen erfolgreichen Tests in der Entwicklungsumgebung fehlschlägt.
Problem 1: Verschlüsselung der Anmeldedaten im SSIS-Paket mit den Anmeldenamen des Users
Hat man z.B. eine Verbindung zu einem anderen SQL-Server eingerichtet, bei dem die Authentifizierung über SQL-Server-Authentifizierung erfolgt, dann ist in den Verbindungseigenschaften natürlich der Benutzername und das dazugehörige Passwort hinterlegt. Solange das SSIS-Paket unter dem Benutzer-Account ausgeführt wird, mit dem es erstellt wurde, ist das alles auch kein Problem. Erst wenn das SSIS-Paket unter einem anderen Benutzer-Account ausgeführt wird, kann plötzlich die Verbindung nicht mehr hergestellt werden.
Die Lösung dieses Problems liegt in der Verschlüsselung der sensitiven Daten im SSIS-Paket. Standard-Einstellung ist die Verschlüsselung der Passwörter mit dem Anmeldenamen des Benutzers. Solange der Entwickler eines SSIS-Pakets dieses auch testet, ist alles wunderbar, denn die verschlüsselt abgelegten Passwörter werden mit dem Benutzernamen des angemeldeten Benutzers wieder entschlüsselt und alles geht seinen Gang. Sobald jedoch das SSIS-Paket unter einem anderen Benutzer-Account ausgeführt wird, schlägt die Entschlüsselung der Passwörter fehl und die Paketausführung wird mit einem Fehler beendet.
Wenn man ein SSIS-Paket als Auftrag im SQL-Server-Agent ablegt, tritt genau diese Situation ein: Wird der Auftrag ausgeführt, dann läuft das SSIS-Paket unter dem Account, mit dem der SQL-Server-Agent-Dienst ausgeführt wird. Das ist standardmäßig das lokale Systemkonto.
Jetzt gibt es zwei Möglichkeiten:
- Den SQL-Server-Agent Dienst umstellen, so dass dieser unter dem Benutzeraccount des Entwicklers läuft
- Die Einstellung des SSIS-Pakets für die Verschlüsselung der sensitiven Daten umstellen
Nun – da der erste Vorschlag nicht wirklich ernst gemeint ist, hier die Beschreibung, wie man die SSIS-Paket Einstellung für die Verschlüsselung der sensitiven Daten ändern kann.
Lösung zu Problem 1: Umstellung der Verschlüsselungsart für sensitive Daten im SSIS-Paket
In den Eigenschaftseinstellungen des SSIS-Pakets (im BI-Development-Studio auf den Hintergrund der Ablaufsteuerung klicken) findet sich ein Bereich “Sicherheit”. Hier kann eingestellt werden, wie die sensitiven Daten verschlüsselt werden. Sinnvoll ist hier die Einstellung “EncryptSensitiveWithPassword” oder “EncryptAllWithPassword”. In diesen beiden Fällen muss dann in der Eigenschaft “PackagePassword” natürlich auch ein Passwort angegeben werden, das dann für die Datenverschlüsselung verwendet werden soll – dieses Passwort wird immer dann abgefragt, wenn man das Paket in einem Auftrag einfügt oder das Paket im BI-Development-Studio öffnet! Mit dieser Einstellungsänderung ist aber dann sichergestellt, dass es keine Probleme mehr bei der Paketausführung gibt.
Problem 2: Verbindungen, die auf Windows-Authentifizierung basieren
Komplizierter wird es, wenn im SSIS-Paket die Windows-Authentifizierung eines bestimmten Benutzers benötigt wird. Das ist zum Beispiel der Fall, wenn man Daten mit einem anderen SQL-Server austauschen möchte, auf dem keine SQL-Server-Authentifizierung eingestellt ist oder wenn man Daten aus Dateien importieren möchte, die auf einem Netzlaufwerk abgelegt sind. Wenn man in diesen Fällen die Paketausführung unter dem Standard-Benutzerkonto des SQL-Server-Agents ausführen würde, dann würde das zwangsläufig zu Fehlern führen, weil das lokale Systemkonto keinen Zugriff auf andere Rechner oder Laufwerke hat. Man kann die Windows-Anmeldeinformation eines Benutzers auch nicht im Paket abspeichern. Hier hilft es nur, wenn man explizit einen Benutzer angeben kann, der für die Ausführung des SSIS-Pakets innerhalb eines SQL-Server-Agent Auftrags genutzt werden kann.
Lösung zu Problem 2: Anlegen eines Proxy-Accounts auf dem SQL-Server
Im SQL-Server-Agent gibt es die Möglichkeit einen Proxy (Stellvertreter) anzugeben, unter dem dann ein Auftragsschritt ausgeführt werden kann. Der einzelne Auftragsschritt wird dann nicht mehr im Kontext des lokalen Systemkontos ausgeführt, sondern im Kontext des Proxys. Hierfür muss man ein paar Dinge im SQL-Server einstellen.
Im SQL Server Management Studio unter Sicherheit/Anmeldeinformationen eine neue Anmeldeinformation angeben (Rechtsklick auf den Ordner “Anmeldeinformation”). Hier zuerst die Identität auswählen (im Auswahldialog darauf achten, dass der richtige Suchpfad eingestellt ist) und dann einen Anzeige-Namen angeben und das Passwort dieses Users hinterlegen:
Unter SQL-Server-Agent/Proxys einen neuen Proxy anlegen (Rechtsklick auf den Ordner Proxy). Als Anmeldeinformationsname die bereits angelegte Anmeldeinformation auswählen, einen Namen für den Proxy vergeben und die “Subsysteme” auswählen, für die der Proxy berechtigt sein soll (minimal muss “SQL Server Integration Services-Paket” ausgewählt sein):
Jetzt kann der neu angelegte Proxy bei der Definition von Auftragsschritten verwendet werden. Das lokale Systemkonto wird quasi “maskiert” und kann sich gegenüber den anderen Rechnern als der im Proxy angegebene User authentifizieren.
Hat man diese Einstellungen vorgenommen, ist für die Zukunft sichergestellt, dass immer der User hinter dem Proxy für die Schrittausführung verwendet wird. Hoffentlich ändert sich jetzt nicht monatlich das Passwort.
