Verbinden eines Azure Linux Web Services mit einer SQL Datenbank mittels Managed Identities

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 os
import requests
import struct
import pyodbc
import sqlalchemy
import urllib

Diese 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_token

Die 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://github.com/AzureAD/azure-activedirectory-library-for-python/wiki/Connect-to-Azure-SQL-Database

https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/services-support-managed-identities#azure-services-that-support-azure-ad-authentication

https://flask-sqlalchemy.palletsprojects.com/en/2.x/config/

https://docs.sqlalchemy.org/en/13/core/engines.html

Azure ML hat den Betastatus verlassen

Wie Microsoft am 18.02.2015 bekanntgegeben hat, ist Azure ML nun für alle Nutzer verfügbar. Azure ML ist ein Machine Learning Service in der Cloud, um zum Beispiel Voraussagen Aufgrund von Datenbeständen treffen zu können. Interessant ist bei Azure ML, dass neben bereits implementieren Modellen, wie z.B. neuronale Netze und Entscheidungsbäumen, auch selbst geschrieben Komponenten genutzt werden können. Diese können in R geschrieben werden oder seit neuesten auch in Python. Damit fällt auch die Umstellung auf Azure ML einfacher, da bisherige Skripte weiterverwendet werden können. Der Service ist rein webbasiert, es wird also nur ein Browser und eine Internetverbindung benötigt. Wenn ein Modell erstellt wurde, kann dieses auch als Webservice genutzt werden, um es in andere Produkte einzubinden. Obwohl Microsoft eine große Anzahl an Rechenzentren weltweit hat, wird Azure ML zurzeit nur in einem Rechenzentrum in den USA angeboten.

021915_1533_AzureMLhatd1.png

Beispielhaft habe ich in einem Projekt zwei verschiedene Modelle daraufhin verglichen, welches besser das Einkommen von Personen voraussagen kann.