はじめに

前編では、BoxとLogic Appsの連携、Functionsでのデータ加工までを説明しました。後編では、加工したデータを SQL Databaseに保存するために、マネージドIDを使ったDatabase接続方法について記載いたします。

マネージドIDを使用したDatabase接続方法

Functions内でマネージドIDを使用してSQL Databaseに接続します。
以下の公式のドキュメントの手順をそのまま試したのですが、手元の環境ではうまく接続できませんでした。試行錯誤の末、接続することが出来ましたので、手順を共有いたします。

Databaseへのアクセス権をマネージド ID に付与

SQLツールを使用し、以下のコマンドを実行します。は、マネージドIDを指定してください。このコマンドを実行することで、マネージドIDを使用してSQL Databaseにアクセスすることが出来るようになります。

CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
GO

pyodbcを使用したDatabase接続 (接続できなかったパターン1）

pythonのライブラリpyodbcを使用してSQL Databaseに接続します。
前述の公式ドキュメントに記載のあった接続文字列¹を使用して、接続しに行くのですが、正常に接続できませんでした。

# 接続文字列（これでは接続できなかった）
Server=demo.database.windows.net; Authentication=Active Directory Managed Identity; Database=testdb

接続時に以下の様なエラーが出力され接続できない。

('08001', "[08001] [Microsoft][ODBC Driver 18 for SQL Server]Invalid value specified for connection string attribute 'Authentication' (0) (SQLDriverConnect)")

いろいろ調べたところアクセストークンを取得し、それを接続時にわたしてやる必要がありそうだということが分かりました。²³

pyodbcを使用したDatabase接続 (接続できなかったパターン2）

アクセストークンが必要そうとわかった段階で以下のドキュメントを見つけ、実装し試してみたのですが、これでも動作しませんでした。

# 接続文字列の作成
DATABASE_CONNECTION_STRING = (
    'Driver=ODBC Driver 18 for SQL Server;'
    'Server=tcp:xxxxxxxx.database.windows.net,1433;'
    'Database=xxxxx-db;'
    'Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;charset=UTF-8'    
)

import os
import pyodbc, struct
from azure.identity import DefaultAzureCredential

connection_string = os.environ["DATABASE_CONNECTION_STRING"]

def get_all():
    with get_conn() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM Persons")
        # Do something with the data
    return

def get_conn():
    credential = DefaultAzureCredential(exclude_interactive_browser_credential=False)
    token_bytes = credential.get_token("https://database.windows.net/.default").token.encode("UTF-16-LE")
    token_struct = struct.pack(f'<I{len(token_bytes)}s', len(token_bytes), token_bytes)
    SQL_COPT_SS_ACCESS_TOKEN = 1256  # This connection option is defined by microsoft in msodbcsql.h
    conn = pyodbc.connect(connection_string, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct})
    return conn

pyodbcを使用したDatabase接続 (接続できたパターン）

最終的に接続できたコードが以下となります。
変更点は、DefaultAzureCredentialではなく、ManagedIdentityCredentialを使用した部分です。
ManagedIdentityCredentialの引数に指定する、client_idは、AzureポータルのマネージドIDの概要ページで表示される「クライアントID」の文字列を入力します。

import pyodbc
import struct

from azure.identity import ManagedIdentityCredential

# 接続文字列、データ取得方法は同じ

def get_conn():
    credential = ManagedIdentityCredential(client_id="xxxxxxxxxxxxxx")
    token_bytes = credential.get_token("https://database.windows.net/.default").token.encode("UTF-16-LE")
    token_struct = struct.pack(f'<I{len(token_bytes)}s', len(token_bytes), token_bytes)
    SQL_COPT_SS_ACCESS_TOKEN = 1256  # This connection option is defined by microsoft in msodbcsql.h
    conn = pyodbc.connect(connection_string, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct})
    return conn

Functionsへのデプロイ時の注意

ManagedIdentityCredentialを使用するため、以下のコマンドでazure-identityライブラリをインストールします。

pip install azure-identity

インストール後、以下のコマンドでデプロイ時に必要なライブラリのリストを出力し、Functionsにデプロイします。しかし、このままデプロイした場合、正常にFunctionsにデプロイできません。(関数自体がポータルから見えなくなってしまいます。)

pip freeze > requirements.txt

これは、azure-identityをインストールした際に、同時にインストールされたpywin32が原因です。pywin32はWindows用のライブラリで、これをrequirements.txtに含めLinux環境でデプロイしようとすると、正常にデプロイできなくなります。

この問題を回避するために、requirements.txt作成後、pywin32をリストから削除してやる必要があります。
pywin32をrequirements.txtから削除することで正常にデプロイできるようになります。

なぜDefaultAzureCredentialでうまく行かなかったか

DefaultAzureCredentialは、事前に構成された複数の認証を順番に試す構成の様です。
マネージドIDによる認証も資格情報チェーンの中に含まれているので、おそらく設定が悪かったのだと考えられます。

DefaultAzureCredentialはを使用する場合もマネージドIDのクライアントIDも指定が必要で、以下の様に指定していれば、接続できたと考えられます。(未検証)

credential = DefaultAzureCredential(exclude_interactive_browser_credential=False, managed_identity_client_id="xxxxxxxxxxxxxx")

Power BI

一旦、SQL Databaseにデータ取り込めば、あとは取り込んだデータを組み合わせ、PowerBIを使用して様々な形で可視化するだけです！

まとめ

今回の記事(前編・後編)では、エクセルで管理していたデータをAzureの各種サービス（Logic Apps, Functions, SQL Database）を使用して、データ活用基盤を構築する際のポイントを紹介いたしました。