Web Applikationen sind das Mittel der Wahl um eine Data Science-Projekt für eine große Zahl an Nutzern verfügbar zu machen. Eine wesentliche Herausforderung ist dabei die Handhabung umfassender Datensätze. Hierfür kommen oft relationale Datenbanken, wie etwa eine Azure SQL DB, zum Einsatz. Wie kann der sichere und performante Datenaustausch zwischen einer Web Applikation gewährleistet werden? In diesem Blog-Beitrag beschreiben wir das Aufsetzen einer Datenbankverbindung für eine Linux Web App in Microsoft Azure.
Bevor ich beschreibe wie man sicher eine Datenbankverbindung aus einem Azure Linux Web Service aufbaut, lass mich kurz den Aufbau des Web Services erklären.
Wie dem Titel zu entnehmen, läuft unsere Anwendung auf einem Linux basiertem System. Die Anwendung ist eine in Python geschriebene WebApp, als Webframework wurde Flask verwendet. Für die Datenbankverbindung nutzen wir Flask-SQLAlchemy und SQLAlchemy wiederum nutzt den ODBC Driver 17, um eine Datenbankverbindung aufzubauen.
Wir möchten mithilfe von Managed Identities for Azure Resources eine sichere Verbindung zu unserer Azure SQL Datenbank aufbauen.
Konfiguration der Azure Resourcen
Zunächst richten wir eine System Assigned Identitiy im Portal ein:
Die Firewall für den SQL-Server sollte den Zugriff anderer Azure Resourcen auf die Datenbank erlauben:
Active Directory Admin für SQL-Server setzen:
Nun wollen wir unserem WebService mit dem Namen "xzy-dash-lnx" Zugriffsrechte auf die Tabelle geben:
CREATE USER [xyz-dash-lnx] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [xyz-dash-lnx];
ALTER ROLE db_datawriter ADD MEMBER [xyz-dash-lnx];
ALTER ROLE db_ddladmin ADD MEMBER [xyz-dash-lnx];
GO
Code Beispiele:
Nachdem unsere App Service und SQL Datenbank eingerichtet sind, können wir jetzt in Python auf die Datenbank zugreifen.
Zunächst einige Bibliotheken, die verwendet werden:
import osimport requestsimport structimport pyodbcimport sqlalchemyimport urllibDiese Funktion wird später genutzt, um den Access Token zu holen:
def get_bearer_token(resource_uri, token_api_version): token_auth_uri = f"{msi_endpoint}?resource={resource_uri}&api-version={token_api_version}" head_msi = {'Secret':msi_secret} resp = requests.get(token_auth_uri, headers=head_msi) access_token = resp.json()['access_token'] return access_tokenDie Umgebungsvariablen "MSI_ENDPOINT" und "MSI_SECRET" werden vom Azure Web Service erstellt, wenn man Managed Identities einschaltet. Die Serveradresse und die Datenbank müssen natürlich auch angegeben werden:
msi_endpoint = os.environ["MSI_ENDPOINT"]msi_secret = os.environ["MSI_SECRET"]connstr="Driver={ODBC Driver 17 for SQL Server};Server=tcp:xyz-sqlserver-jma.database.windows.net,1433;Database=xyz-titanicdb-jma";Access Token abholen und in struct umwandeln:
token = bytes(get_bearer_token("https://database.windows.net/", "2017-09-01"), "UTF-8")exptoken = b"";for i in token: exptoken += bytes({i}); exptoken += bytes(1);tokenstruct = struct.pack("=i", len(exptoken)) + exptoken;Beispiel einer Datenbankabfrage in ODBC. Die Zahl 1256 entspricht dem Attribut "SQL_COPT_SS_ACCESS_TOKEN":
conn = pyodbc.connect(connstr, attrs_before = { 1256:tokenstruct });cursor = conn.cursor()cursor.execute("SELECT TOP (1000) [id],[Survived],[Pclass],[Name],[Sex],[Age],[sibling_or_spouse],[parents_or_children],[Fare] FROM [dbo].[titanic_passanger]")row = cursor.fetchone()while row: print (str(row[2]) + " " + str(row[3])) row = cursor.fetchone()Beispiel einer Abfrage in SQLAlchemy:
params = urllib.parse.quote(connstr)engine = sqlalchemy.create_engine('mssql+pyodbc:///?odbc_connect={}'.format(params) ,connect_args={'attrs_before': { 1256:tokenstruct}})conn = engine.connect()result = conn.execute("SELECT TOP (10) [id],[Survived],[Pclass],[Name],[Sex],[Age],[sibling_or_spouse],[parents_or_children],[Fare] FROM [dbo].[titanic_passanger]")for row in result: print (str(row[2]) + " " + str(row[3]))conn.close()Für Flask-SQLAlchemy könnte eine Konfigurationsdatei config.py folgende Einstellungen enthalten:
class BaseConfig:
SQLALCHEMY_DATABASE_URI = 'mssql+pyodbc:///?odbc_connect={}'.format(params)
SQLALCHEMY_ENGINE_OPTIONS = {'connect_args': {'attrs_before': {1256:tokenstruct}}}
SQLALCHEMY_TRACK_MODIFICATIONS = False
DEBUG = False
TESTING = False
Wie man sieht werden in unserem Code keine Informationen wie Nutzename und Passwort enthalten. Die Adresse unseres SQL-Servers ist noch im Code enthalten, aber auch diese Information lässt sich außerhalb des Programmcodes speichern. Dieser Code sollte in ähnlicher Form auch funktionieren, um einer Azure Function mittels Managed Identities Datenbankzugriff zu gewähren.
Weiterführende Links:
https://docs.microsoft.com/en-us/sql/connect/odbc/using-azure-active-directory?view=sql-server-2017
https://docs.microsoft.com/en-us/azure/app-service/overview-managed-identity#using-the-azure-portal
https://docs.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi
https://flask-sqlalchemy.palletsprojects.com/en/2.x/config/
https://docs.sqlalchemy.org/en/13/core/engines.html