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

Beliebig viele Tabellenspalten beim SQL Server 2008: Sparse Columns

Ich hoffe, jeder Leser dieses Blogs hat beim Datenbank-Design gut aufgepasst, dann wissen sie oder er ja, dass man keine unendlich breiten Tabellen erzeugen soll. Aber gut, mal unter uns und nicht weitersagen: manchmal geht es eben nicht anders, aber dann auf eigenes Risiko! Mitunter wird der SQL Server nur als so eine Art »Excel auf dem Server« missbraucht, und seit Excel 2007 jetzt auch 16.384 Spalten speichern kann (statt vorher nur 256) ist natürlich die Frage: kann der SQL Server das auch? Nun, bis zur Version 2008 kann er das nicht, da beherrscht er »nur« 1024 Spalten. Jetzt kann er diese Grenze sprengen, aber nur, wenn die zusätzlichen Spalten nur spärlich (»sparse«) gefüllt sind!

„Beliebig viele Tabellenspalten beim SQL Server 2008: Sparse Columns“ weiterlesen