Bernd Jungbluth, Horn – www.berndjungbluth.de
Die Verbindung von Access zum SQL Server erfolgt in der Regel über ODBC. Hierzu wird vorab eine ODBC-Datenquelle erstellt und unter einem Data Source Name – kurz DSN – gespeichert. Für den Datenzugriff liefert die ODBC-Datenquelle die Bezeichnung des SQL Servers und den Namen der Datenbank. Der Anmeldename und das Kennwort hingegen kommen direkt aus der Access-Applikation. Dabei sind die in Access gespeicherten Anmeldedaten ein nicht zu unterschätzendes Sicherheitsrisiko. Dieser Beitrag beschreibt die Risiken und zeigt Mittel und Wege, wie Sie diese vermeiden können.
Warnung
Die beschriebenen Aktionen haben Auswirkungen auf Ihre SQL Server-Installation. Führen Sie die Aktionen nur in einer Testumgebung aus. Verwenden Sie unter keinen Umständen Ihren produktiven SQL Server!
ODBC-Datenquellen
Die Definition einer ODBC-Datenquelle für den Zugriff auf eine SQL Server-Datenbank besteht im Wesentlichen aus den Angaben zum SQL Server, zur Datenbank und den Anmeldedaten. Bei Bedarf lassen sich noch weitere Optionen ergänzen, zum Beispiel zur Verschlüsselung der Datenübertragung. Der ODBC-Datenquelle geben Sie im ODBC-Datenquellen-Editor einen Namen, den sogenannten Data Source Name oder kurz DSN. Mit diesem DSN binden Sie die Tabellen der SQL Server-Datenbank in die Access-Applikation ein und definieren die ODBC-Verbindung der Pass Through-Abfragen.
In der Beispielumgebung verwendet die ODBC-Datenquelle den DSN WaWi_SQL. Sind Sie der Installationsanleitung zur Beispielumgebung gefolgt, enthält der DSN die Anmeldung WaWiPersonal. Sollte dies nicht der Fall sein, passen Sie die Anmeldedaten in der ODBC-Datenquelle an. Nur mit dieser Anmeldung haben Sie Zugriff auf alle Tabellen und gespeicherten Prozeduren. Die Installationsanleitung zur Beispielumgebung finden Sie am Ende des Beitrags.
Der Data Source Name
Die ODBC-Datenquelle mit dem DSN WaWi_SQL ermöglicht Ihnen den Zugriff auf die Daten der gleichnamigen SQL Server-Datenbank. Dabei ist es Ihnen überlassen, mit welcher Applikation Sie auf die Daten zugreifen. So können Sie die Daten unter anderem in Excel auswerten oder in einer eigenen Access-Datenbank verarbeiten. Der Vorgang für den Datenzugriff ist immer der gleiche. Sie wählen den DSN der ODBC-Datenquelle und geben das Kennwort zur Anmeldung ein. Beides ist in der Access-Applikation WaWi nicht mehr notwendig. Hier haben Sie bereits beim Einbinden der Tabellen den DSN WaWi_SQL gewählt sowie das Kennwort eingegeben und es in Access gespeichert. Dadurch gehört der DSN mitsamt dem Kennwort zu den Eigenschaften einer eingebundenen Tabelle. Diese können Sie sich in der Entwurfsansicht anschauen.
Starten Sie die Access-Applikation WaWi und öffnen Sie die Tabelle Artikel in der Entwurfsansicht. Nach Bestätigen der Meldung mit einem Klick auf Ja blenden Sie mit der Taste F4 das Eigenschaftenblatt ein. In der Eigenschaft Beschreibung sehen Sie die Informationen zur Datenquelle der eingebundenen Tabelle (siehe Bild 1). Neben dem Verweis zum DSN zeigt die Eigenschaft noch weitere Angaben:
Bild 1: Beschreibung zur Datenquelle
ODBC; DSN=WaWi_SQL; UID=WaWiPersonal; Trusted_Connection=No; APP=Microsoft Office; DATABASE=WaWi_SQL; ; TABLE=dbo.Artikel
Mit dem Namen der Datenbank im Parameter DATABASE, dem Anmeldenamen unter UID sowie der Bezeichnung der Originaltabelle im Parameter TABLE enthält die Eigenschaft drei wesentliche Informationen zur externen Datenquelle. Der Eintrag Trusted_Connection=No zeigt, dass für die Verbindung eine Anmeldung per SQL Server-Authentifizierung verwendet wird. Das Kennwort zur Anmeldung ist an dieser Stelle aus Sicherheitsgründen nicht zu sehen. Als ergänzende Information ist im Parameter APP noch der Name der Applikation aufgeführt.
Neben den eingebundenen Tabellen bieten Ihnen zwei Pass Through-Abfragen den Zugriff auf die Daten der SQL Server-Datenbank. Diese enthalten in ihren Eigenschaften ebenso die Informationen zum DSN der ODBC-Datenquelle. Öffnen Sie die Pass Through-Abfrage ptSelectGeburtstagsliste in der Entwurfsansicht und aktivieren Sie dort das Eigenschaftenblatt.
Hier ist es die Eigenschaft ODBC-Verbindung, die Ihnen die Informationen zur Datenquelle zeigt:
ODBC; DSN=WaWi_SQL; UID=WaWiPersonal; PWD=SicherIn2020!; Trusted_Connection=No; APP=Microsoft Office; DATABASE=WaWi_SQL;
Der Eintrag unterscheidet sich zu dem einer eingebundenen Tabelle in zwei Parametern. Zum einen fehlt logischerweise der Verweis zur Originaltabelle und zum anderen ist hier das Kennwort klar und deutlich zu sehen. Im Gegensatz zur Tabelle sind diese Informationen weitaus mehr als eine Beschreibung, sie gehören vielmehr zur Definition der Pass Through-Abfrage. Über die Schaltfläche am Ende der Eigenschaft ODBC-Verbindung können Sie diese Definition ändern. Klicken Sie auf die Schaltfläche und wählen Sie im folgenden Dialog in der Registerkarte Computerdatenquelle die ODBC-Datenquelle über den DSN WaWi_SQL aus. Beim Anmeldedialog verwenden Sie die Anmelde-ID WaWiMa mit dem zugehörigen Kennwort SicherIn2020!. Es folgt die Frage, ob Sie das Kennwort speichern möchten. Mit einem Klick auf Ja übernehmen Sie das Kennwort sowie den Verweis zum DSN in die Eigenschaft ODBC-Verbindung. Diese zeigt nun folgenden Inhalt:
ODBC; DSN=WaWi_SQL; UID=WaWiMa; PWD=SicherIn2020!; Trusted_Connection=No; APP=Microsoft Office; DATABASE=WaWi_SQL;
Sie sehen zum einen den DSN WaWi_SQL und zum anderen unter UID und PWD die Anmeldedaten zur Anmeldung WaWiMa. Indirekt stehen nun zwei Anmeldungen zur Verfügung, die Anmeldung WaWiPersonal im DSN und der direkte Eintrag zur Anmeldung WaWiMa in den Eigenschaften der Pass Through-Abfrage.
Welche Anmeldung wird denn nun beim Datenzugriff verwendet Ein Klick auf Ausführen beantwortet diese Frage. Anstelle der Geburtstage der Mitarbeiter sehen Sie eine Fehlermeldung. Das ist korrekt und es entspricht der aktuell gültigen Rechtevergabe der Beispielumgebung. Der Anmeldung WaWiMa wird das Ausführen der gespeicherten Prozedur pSelectGeburtstagsliste verweigert. Für den Datenzugriff sind also die Anmeldedaten der Pass Through-Abfrage maßgebend. Die im DSN gespeicherten Anmeldedaten hingegen spielen dabei keine Rolle. Eine Abweichung der Anmeldedaten ist auch bei eingebundenen Tabellen möglich. Dazu geben Sie beim Einbinden der Tabellen nach der Auswahl der ODBC-Datenquelle einfach andere Anmeldedaten an.
Gerade solche abweichenden Konfigurationen führen schnell zu einer falschen Bewertung des Zugriffsschutzes. So könnte ein Blick in den DSN eine Anmeldung mit geringen Zugriffsrechten zeigen, in Access jedoch wird eine andere Anmeldung mit weitaus höheren Rechten verwendet. Also gerade umgekehrt zum aktuellen Beispiel, dafür aber mit schlimmeren Folgen. Der Anwender agiert mit mehr Rechten als der Administrator dies nach einem Blick in den DSN vermutet. Wenn das kein gutes Argument ist, in Zukunft auf den DSN zu verzichten!
Ohne Data Source Name – DSN-less
Aktuell stellt der DSN WaWi_SQL die Bezeichnungen zum verwendeten Treiber, zu Ihrem SQL Server und zur Datenbank bereit. Für einen Datenzugriff werden diese Informationen mit den in Access gespeicherten Anmeldedaten ergänzt und in einer Verbindungszeichenfolge zusammengefasst. Diese ebenso als Connection-String bekannte Verbindungszeichenfolge ist die Basis für die Verbindung zur externen Datenquelle. Sie beinhaltet im Grunde genommen die Wegbeschreibung, in diesem Fall die Route zur Ihrem SQL Server und dort zur Datenbank WaWi_SQL. Dabei enthält sie eigentlich nur Informationen, die Sie ebenso gut direkt in Access speichern können. Sie kennen den verwendeten ODBC-Treiber, Ihren SQL Server und die Datenbank. Es fehlt Ihnen nur noch die Syntax der Verbindungszeichenfolge.
Die Syntax lässt sich recht einfach ermitteln. Dazu erstellen Sie eine weitere ODBC-Datenquelle, dieses Mal jedoch als Datei-DSN. Öffnen Sie den ODBC-Datenquellen-Editor in der bewährten Art und Weise über die Windows-Taste und der Eingabe ODBC. Das Suchergebnis zeigt zwei Einträge. Hier wählen Sie je nach installierter Access-Version den Eintrag ODBC-Datenquellen (32-Bit) oder ODBC-Datenquellen (64-Bit).
Im ODBC-Datenquellen-Administrator wechseln Sie zur Registerkarte Datei-DSN. Dort klicken Sie auf Hinzufügen und wählen im folgenden Dialog den Treiber ODBC Driver 17 for SQL Server aus. Informationen zu diesem Treiber finden Sie in der Installationsanleitung der Beispielumgebung. Nach einem Klick auf Weiter legen Sie den Speicherort und Dateinamen fest. Vielleicht nennen Sie die Datei WaWi_SQL und speichern sie im Ordner der Beispieldateien. Bestätigen Sie die bisherigen Angaben mit einem Klick auf Weiter und anschließend mit der Schaltfläche Fertig stellen.
Jetzt beginnt die eigentliche Definition der ODBC-Datenquelle. Dabei geben Sie im ersten Schritt im Eingabefeld Server den Namen Ihres SQL Servers ein. In Schritt 2 legen Sie mit der dritten Option die Anmeldung per SQL Server-Authentifizierung fest und ergänzen diese Auswahl mit dem Anmeldenamen WaWiPersonal im Eingabefeld Anmelde-ID und dem zugehörigen Kennwort im Eingabefeld Kennwort. Die Datenbank WaWi_SQL wählen Sie in Schritt 3 aus, nachdem Sie dort die Option Die Standarddatenbank ändern auf aktiviert haben. Mit einem Klick auf Weiter und anschließend auf Fertig stellen schließen Sie die Definition der ODBC-Datenquelle ab. Es folgt der Dialog zum Test der Verbindung. Nach einem letzten Klick auf OK beenden Sie das Erstellen der Datei-DSN.
Nun öffnen Sie die Datei-DSN mit einem Texteditor. Sie sehen die einzelnen Parameter zur Verbindungszeichenfolge (siehe Bild 2). Bevor Sie diese Informationen in Access nutzen können, sind noch ein paar Anpassungen notwendig. Als Erstes löschen Sie die eckigen Klammern rund um den Begriff ODBC. Danach entfernen Sie die Einträge der Parameter TrustServerCertificate, WSID und APP. Jetzt fügen Sie an jedes Zeilenende ein Semikolon ein und löschen den Zeilenumbruch. Als Ergebnis sehen Sie die Verbindungszeichenfolge in einer einzelnen Zeile. Diese ergänzen Sie mit dem Parameter PWD und dem Kennwort SicherIn2020!. Mit Ausnahme des Werts im Parameter Server müsste Ihre Verbindungszeichenfolge nun der in Bild 3 entsprechen.
Bild 2: Die Verbindungszeichenfolge
Bild 3: Inhalt einer Datei-DSN
Kopieren Sie die Verbindungszeichenfolge und gehen Sie zurück zur Access-Applikation WaWi. Dort öffnen Sie die Pass Through-Abfrage ptSelectGeburtstagsliste in der Entwurfsansicht und überschreiben den Inhalt der Eigenschaft ODBC-Verbindung mit der kopierten Verbindungszeichenfolge. Speichern Sie die Änderungen und klicken Sie auf Ausführen. Als Ergebnis sehen Sie die Geburtstage der Mitarbeiter – und das ohne DSN.
Wenn Sie schon dabei sind, ändern Sie doch direkt noch die Pass Through-Abfrage ptSelectAnsprechpartnerZuMitarbeiter. Überschreiben Sie hier ebenfalls die Eigenschaft ODBC-Verbindung mit dem Inhalt Ihrer Datei-DSN. Glückwunsch! Ab jetzt erfolgt der Datenzugriff der Pass Through-Abfragen ohne Verweis zu einem DSN. Beide arbeiten nun DSN-less. Alle für den Datenzugriff notwendigen Informationen sind direkt in den Pass Through-Abfragen gespeichert. Leider enthält die Eigenschaft ODBC-Verbindung immer noch das Kennwort im Klartext. Doch dazu später mehr.
Vorher soll das Prinzip DSN-less noch bei den eingebundenen Tabellen angewendet werden. Nur ist das hier nicht so einfach wie bei den Pass Through-Abfragen. Zwar enthält eine eingebundene Tabelle ebenfalls den Verweis zum DSN, dieser lässt sich dort aber nicht ändern.
Die Entwurfsansicht eingebundener Tabellen ist schreibgeschützt. Zudem zeigt die Eigenschaft Beschreibung lediglich eine Dokumentation zur Datenquelle. Der eigentliche Speicherort der Verbindungszeichenfolge ist die Spalte Connect in der Access-Systemtabelle MSysObjects. Dort gibt es zu jeder eingebundenen Tabelle den folgenden Eintrag – und an dieser Stelle sogar mit lesbarem Kennwort.
DSN=WaWi_SQL; UID=WaWiPersonal; PWD=SicherIn2020!; Trusted_Connection=No; APP=Microsoft Office; DATABASE=WaWi_SQL;
Die Daten sind hier ebenfalls schreibgeschützt. Die Verbindungszeichenfolge können Sie nur ändern, indem Sie die Tabellen neu einbinden. Doch da gibt es gleich die nächste Hürde. Die Dialoge über den Menüpunkt Externe Daten bieten keine Möglichkeit, die Tabellen ohne einen DSN einzubinden. In den Access-Versionen 2019 und 365 hat der Tabellenverknüpfungs-Manager jedoch eine Lösung für Sie.
DSN-less per Tabellenverknüpfungs-Manager
Mit dem neuen Tabellenverknüpfungs-Manager lässt sich der DSN recht einfach mit einer eigenen Verbindungszeichenfolge ersetzen. Starten Sie den Tabellenverknüpfungs-Manager über das Ribbon Externe Daten, aktivieren Sie dort den Eintrag ODBC (siehe Bild 4) und klicken Sie auf Bearbeiten.
Bild 4: Der Tabellenverknüpfungs-Manager
Im folgenden Dialog überschreiben Sie das Eingabefeld Verbindungszeichenfolge mit dem Inhalt Ihrer Datei-DSN (siehe Bild 5) und bestätigen dies mit einem Klick auf Speichern. Zurück im Tabellenverknüpfungs-Manager klicken Sie auf Aktualisieren. Das war es schon. Sie können den Tabellenverknüpfungs-Manager wieder schließen.
Bild 5: DSN-less per Tabellenverknüpfungs-Manager
Der Datenzugriff über eine eingebundene Tabelle erfolgt jetzt ebenfalls ohne DSN. Testen Sie es einmal. Öffnen Sie die Tabelle Kunden in der Entwurfsansicht und schauen Sie in die Eigenschaft Beschreibung. Dort sehen Sie Ihre Verbindungszeichenfolge. Das Kennwort wird aus Sicherheitsgründen weiterhin nicht angezeigt.
Der neue Tabellenverknüpfungs-Manager macht vieles einfacher. Sollten Sie noch eine ältere Version von Access verwenden, bleibt Ihnen diese Möglichkeit verwehrt. Alternativ können Sie die Tabellen per VBA einbinden.
DSN-less per VBA
Zum Einbinden der Tabellen mit einer eigenen Verbindungszeichenfolge bietet Ihnen die neue Version der Access-Applikation WaWi die Funktion fTabellenEinbinden. Schauen Sie sich einmal die Funktion an. Sie finden sie im Modul Tabellen.
Die Funktion legt zuerst die Verbindungszeichenfolge fest. Dies erfolgt mit einer weiteren Funktion namens fOdbcPerFunction.
Mit einem Rechtsklick auf den Funktionsnamen und anschließender Auswahl des Eintrags Definition wechseln Sie direkt zur Funktion im Modul ODBC. Hier überschreiben Sie den Eintrag Ihre Verbindungszeichenfolge mit dem Inhalt Ihrer Datei-DSN (siehe Bild 6). Ein erneuter Rechtsklick und der Auswahl des Eintrags Letzte Position bringt Sie wieder zurück zur Funktion fTabellenEinbinden.
Bild 6: Die Funktion fOdbcPerFunction
Dort wird Ihre Verbindungszeichenfolge in der Variablen strODBC gespeichert. Anschließend löschen die folgenden Zeilen alle eingebundenen Tabellen und aktualisieren danach den neuen Stand der Tabellendefinitionen:
For Each tdf In CurrentDb.TableDefs
If Left(tdf.Connect, 5) = "ODBC;" Then
CurrentDb.TableDefs.Delete tdf.Name
End If
Next
CurrentDb.TableDefs.Refresh
Es folgt das Neueinbinden der Tabellen. Die Bezeichnungen der Tabellen liefert eine temporäre Pass Through-Abfrage.
Set ptqry = CurrentDb.CreateQueryDef("") With ptqry .Connect = strODBC .SQL = " SELECT [name] As Tabelle, SCHEMA_NAME( [schema_id]) + ''.'' + [name] As Originaltabelle FROM sys.objects WHERE [type] = ''U'';" .ReturnsRecords = True End With
Die Pass Through-Abfrage verwendet als Verbindungszeichenfolge den Inhalt der Variablen strODBC und führt somit auf Ihrem SQL Server in der Datenbank WaWi_SQL diese SQL-Anweisung aus:
SELECT [name] As Tabelle, SCHEMA_NAME([schema_id]) + ''.'' + [name] As Originaltabelle FROM sys.objects WHERE [type] = ''U'';
Die SELECT-Anweisung ermittelt in der Tabelle sys.objects alle Systemobjekte vom Typ U. U steht an dieser Stelle für User-Table und kennzeichnet die vom Datenbankentwickler erstellten Tabellen. Das Ergebnis umfasst zwei Spalten, den Namen der Tabelle ohne Schema und den Namen der Tabelle mit Schema.
Sie möchten sich diese Daten einmal anschauen Dann öffnen Sie das SQL Server Management Studio und melden Sie sich dort mit der Anmeldung WaWiPersonal an. Eventuell müssen Sie hierzu den Anmeldetyp in SQL Server-Authentifizierung ändern (siehe Bild 7).
Bild 7: Die Anmeldung am SSMS
Nach der Anmeldung erweitern Sie den Objekt-Explorer, markieren die Datenbank WaWi_SQL mit der rechten Maustaste und wählen aus dem Kontextmenü den Eintrag Neue Abfrage. Sie erhalten eine leere Registerkarte, in der Sie die SELECT-Anweisung eingeben und dann mit der Taste F5 ausführen. Das Ergebnis listet alle Tabellen auf, zu denen die Anmeldung WaWiPersonal entsprechende Zugriffsrechte besitzt (siehe Bild 8). Dabei enthält die Spalte Originaltabelle neben dem Tabellennamen auch den Namen des Schemas. Das ist in der aktuellen Beispieldatenbank das Standardschema dbo. Welche Vorteile Sie bei der Verwendung verschiedener Schemata haben, erfahren Sie in einer der nächsten Ausgaben.
Bild 8: Die Tabellen der Anmeldung WaWiPersonal
Die Definition der temporären Pass Through-Abfrage endet mit der Zuweisung ReturnsRecords = True. Diese sorgt für die Rückgabe der ermittelten Daten zur Weiterverarbeitung in VBA. Die nächste Anweisung erstellt das Recordset rsObjekte und füllt es mit den Daten der Pass Through-Abfrage:
Set rsObjekte = ptqry.OpenRecordset
Zu jedem Eintrag des Recordsets wird dann eine neue Tabellendefinitionen erstellt.
Do While Not rsObjekte.EOF ''Tabelle einbinden On Error GoTo 0 Set tdf = CurrentDb.CreateTableDef(rsObjekte!Tabelle, dbAttachSavePWD, rsObjekte!Originaltabelle, strODBC) CurrentDb.TableDefs.Append tdf CurrentDb.TableDefs(rsObjekte!Tabelle).RefreshLink rsObjekte.MoveNext Loop
Die Parameter der Anweisung CurrentDb.CreateTableDef beinhalten alle wichtigen Informationen für den späteren Datenzugriff. Dabei übergibt der Parameter rsObjekte!Tabelle den Namen, unter dem die eingebundene Tabelle später in Access zu sehen ist, während der Parameter dbAttachSavePWD das Kennwort der Anmeldedaten in Access speichert. Die Bezeichnung der Originaltabelle liefert der Parameter rsObjekte!Originaltabelle und den Weg dorthin beschreibt die Verbindungszeichenfolge im Parameter strODBC.
Mit den nächsten beiden Anweisungen wird die Tabellendefinition der Access-Applikation hinzugefügt und die Informationen zur neu eingebundenen Tabelle aktualisiert:
CurrentDb.TableDefs.Append tdf CurrentDb.TableDefs(rsObjekte!Name). RefreshLink
Dieser Vorgang wiederholt sich nun für alle Tabellen, die mit der Pass Through-Abfrage ermittelt wurden. Dabei spielt die Verbindungszeichenfolge der Pass Through-Abfrage eine wichtige Rolle. Mit den Anmeldedaten der Anmeldung WaWiPersonal werden alle Tabellen der Datenbank eingebunden. Bei der Anmeldung WaWiMa hingegen sind es nur acht Tabellen. Im aktuellen Berechtigungskonzept ist dieser Anmeldung der Zugriff auf die Tabellen Bewerber, Mitarbeiter und Stellen verweigert – und ohne Rechte kann die Pass Through-Abfrage diese Tabellen nicht ermitteln. Die Funktion bindet also nur die Tabellen ein, zu denen die dabei verwendete Anmeldung entsprechende Zugriffsrechte besitzt. Ein nicht zu unterschätzender Sicherheitsaspekt.
Sie haben das Ende des frei verfügbaren Textes erreicht. Möchten Sie ...
Oder bist Du bereits Abonnent? Dann logge Dich gleich hier ein. Die Zugangsdaten findest Du entweder in der aktuellen Print-Ausgabe auf Seite U2 oder beim Online-Abo in der E-Mail, die Du als Abonnent regelmäßig erhältst:
