Azure Functions (Python) から Azure SQL Database をマネージド ID で操作する

はじめに

PaaS での資格情報の取り扱い

PaaS での開発を進めていくと、ローカルにないデータベースやストレージを扱うことが増えてきます。Azure でいうと Azure Storage や Azure SQL Database と連携することがあると思います。

それらのサービスに対して接続する時、接続文字列や管理者の資格情報を利用するのが一番簡単です。ソースコード内に埋め込むのが一番簡単ですが、公開リポジトリなどで公開しないように注意しないといけません。

ソースコード内に埋め込まずに接続情報を扱う方法として、環境変数を設定する選択肢もあります。外部への漏洩については心配が減りますが、それぞれの環境で環境変数を管理する手間が増えます。接続文字列を更新した際などは各環境の変数を更新する必要があります。

どちらも一長一短な感じですが、安全性と管理性を両立したアクセス資格の管理方法としてマネージド ID が提供されています。

マネージド ID とは

マネージド ID は Azure で提供されているアクセス制御のための機能です。Azure リソースを Azure AD に登録することで、他のリソースを安全に操作することができます。

認証自体は Azure AD が行うため、接続元の Azure リソースからリソースの情報と接続トークンを Azure AD に送信することで接続先 Azure リソースに対しての認証が行えます。直接接続情報を扱うこともなく、接続先側でどのリソースからのアクセスを許可するかを指定できるので、不正なアクセスを抑制できます。

マネージド ID には2種類の方式があります。

システム割り当てマネージド ID：リソースに1対1で割り当てられる
ユーザー割り当てマネージド ID：マネージド ID リソースとして作成、共用可能

システム割り当てマネージド ID は Azure リソースで機能を有効にすると自動的に Azure AD に ID が登録されます。リソースがローカルで取得する情報に基づいてトークンを生成できるので、セキュアです。

ユーザー割り当てマネージド ID は個別の Azure リソースとして作成することができます。リソース内で外部のマネージド ID リソースを指定することで、事前にマネージド ID に割り当てたロールに基づく作業ができるようになります。

https://docs.microsoft.com/ja-jp/azure/active-directory/managed-identities-azure-resources/overview

ここまで書いてますが、次の Qiita 記事で非常にわかりやすく解説されています。是非ご一読。

はじめての Azure リソースマネージド ID (概要編)
https://qiita.com/Shinya-Yamaguchi/items/edd75f7ee47471a98670

今回の構成

簡易的にシステム割り当てマネージド ID で Azure Functions から SQL Database を操作してみます。

Azure SQL Database 展開

Azure ポータルにて、Azure SQL Database を作成します。

[基本]タブで次の通りパラメーターを設定していきます。

[サブスクリプション]：（利用するサブスクリプションを選択）
[リソースグループ]：（利用するリソースグループを選択）
[データベース名]：sample-azfunc-mid-db（データベースの名前）
[サーバー]
- [サーバー名]：sample-azfunc-mid-sql（データベースサーバーの名前）
- [サーバー管理者ログイン]：（管理者名を指定）
- [パスワード]：（管理者パスワードを指定）
- [場所]：[東日本]
[SQL エラスティックプールを使用します]：[いいえ]
[コンピューティングとストレージ]：[Basic]

[ネットワーク]タブでは[接続方法]で[アクセスなし]を選択します。

[追加設定]タブで次のようにパラメーターを設定します。

[データソース]
- [既存のデータを使用します]：[なし]
[データベース照合順序]
- [照合順序]：SQL_Latin1_General_CP1_CI_AS
[Advanced Data Security]
- [Advanced Data Security を有効にする]：[後で]

パラメーターを設定したら作成します。

SQL データベースサーバーの設定

Active Directory 管理者の有効化

SQL データベースサーバーリソースを選択し、[Active Directory 管理者] をクリックします。[管理者の設定] をクリックします。Azure AD に登録されているユーザー（ポータルにサインインしているユーザー）を選択し、[選択] をクリックします。[保存] をクリックします。

クライアント IP と Azure サービスからのアクセス許可

[ファイアウォールと仮想ネットワーク] をクリックします。 [Azure サービスおよびリソースにこのサーバーへのアクセスを許可する] を [はい] にします。 [クライアント IP アドレス] に現在ポータルに接続しているクライアント IP アドレスが表示されるので、接続元デバイスの IP を追加します。 [保存] をクリックします。

Azure Function 展開

Function リソースを作成します。

[基本]タブで次の通りパラメーターを入力します。

[サブスクリプション]：（利用するサブスクリプションを選択）
[リソースグループ]：（利用するリソースグループを選択）
[関数アプリ名]：sample-azfunc-mid（Function アプリの名前）
[公開]：[コード]
[ランタイムスタック]：[Python]
[バージョン]：[3.8]
[場所]：[東日本]

[ホスティング]タブで次の通りパラメーターを入力します。

[ストレージアカウント]：（自動作成されるストレージアカウント名）
[オペレーティングシステム]：[Lunix]
[プランの種類]：[消費量（サーバーレス）]

[監視]タブでは[Application Insights を有効にする]を[いいえ]にします。

設定したら関数アプリを作成します。

環境変数の作成

関数アプリリソースを選択し、[構成]をクリックします。[新しいアプリケーション設定]をクリックし、環境変数を追加していきます。

名前	値	備考
RESOURCE_URI_SQL	https://database.windows.net	SQL データベースを操作する共通 URL
SQL_DATABASE	sample-azfunc-mid-db	SQL データベースの名前
SQL_DRIVER	{ODBC Driver 17 for SQL Server}	SQL データベースの接続ドライバー
SQL_SERVER	sample-azfunc-mid-sql.database.windows.net	SQL データベースサーバーの URL

[SQL_DATABASE]と[SQL_SERVER]はそれぞれの環境に合わせて変更します。追加したら上部の[保存]をクリックします。

マネージド ID 有効化

関数アプリリソースで[ID]をクリックします。[状態]を[オン]にして[保存]をクリックします。

コードのアップロード

次のリポジトリに今回使う Python コードをまとめています。これをクローンして Function を作成できます。

https://github.com/sny0421/sample-azure-functions-python-system-managed-id-sql-database

Visual Studio Code で操作します。次の拡張機能があると良いです。

https://marketplace.visualstudio.com/items?itemName=ms-azuretools.vscode-azurefunctions

VS Code で GitHub からコードをクローンしたら、Azure 機能ブレードで上向き矢印のマークをクリックしてコードをアップロードします。

サブスクリプションと展開先の関数アプリを指定します。デプロイ時に上書きの確認が出るので、[Deploy]をクリックします。

SQL データベースの設定

データベースリソースを選択し、[クエリエディター (プレビュー)] をクリックします。

テーブル作成

[Active Directory 認証] で [(現在ログインしているユーザー名) として続行] をクリックします。クエリエディターに次のクエリを入力し、テーブルを作成します。

CREATE TABLE test(id NCHAR(5) PRIMARY KEY, name NVARCHAR(256))

Azure Function へのアクセス権設定

クエリエディターに次のクエリを入力し、Azure Function へのアクセス権を付与します。

CREATE USER [azure-function-app-name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [azure-function-app-name];
ALTER ROLE db_datawriter ADD MEMBER [azure-function-app-name];
ALTER ROLE db_ddladmin ADD MEMBER [azure-function-app-name];

ソースコード

解説

少しだけ解説します。

import azure.functions as func
import logging
import os
import pyodbc
import requests
import struct
import sys
from ..shared_code import sql_database_connect as sdc
def main(req: func.HttpRequest) -> func.HttpResponse:
    logging.info('Start function: CreateRecord')
    try:
        db_id = req.params.get('id')
        name = req.params.get('name')
    except:
        return func.HttpResponse(
            "Please put a valid parameters in the request",
            status_code=400
        )
    try:
        # Get access token
        token_struct = sdc.get_sql_access_token()
        # Load environment variables
        driver = os.environ['SQL_DRIVER']
        sql_server = os.environ['SQL_SERVER']
        sql_database = os.environ['SQL_DATABASE']
        # Connect SQL Database
        conn = pyodbc.connect('DRIVER='+driver+';SERVER='+sql_server+';PORT=1433;DATABASE='+sql_database+';', attrs_before = { 1256:bytearray(token_struct) })
        logging.info('connected to {} on {}'.format(sql_server,sql_database))
        cursor = conn.cursor()
        # Execute SQL query
        cursor.execute("INSERT INTO test VALUES (?, ?)", db_id, name)
        cursor.commit()
        logging.info('finished')
    except BaseException as error:
        logging.info('An exception occurred: {}'.format(error))
        return func.HttpResponse(
            'An exception occurred: {}'.format(error),
            status_code=200
        )
    return func.HttpResponse(
        "UserID {} added.".format(db_id),
        status_code=201
    )

上記は https://<関数アプリの URL>?id=xxxxx&name=hoge という形式でアクセスするとデータベースにレコードを追加しようとする HTTP トリガーアプリです。

次のコードでマネージド ID を利用してアクセスするためのアクセストークンを取得します。

token_struct = sdc.get_sql_access_token()

実体は別ファイルに記載しており、次の通りとなっています。

import os
import requests
import struct
import sys
def get_sql_access_token():
    # 環境変数を読み込み
    resource_uri = os.environ["RESOURCE_URI_SQL"]
    identity_endpoint = os.environ["IDENTITY_ENDPOINT"]
    identity_header = os.environ["IDENTITY_HEADER"]
    # 認証用 URI とヘッダーの作成
    token_auth_uri = f"{identity_endpoint}?resource={resource_uri}&amp;api-version=2017-09-01"
    head_msi = {'secret':identity_header}
    # アクセストークンの取得
    response = requests.get(token_auth_uri, headers=head_msi)
    access_token = bytes(response.json()['access_token'], 'utf-8')
    expire_token = b""
    for i in access_token:
            expire_token += bytes({i})
            expire_token += bytes(1)
    token_struct = struct.pack("=i", len(expire_token)) + expire_token
    return token_struct

コードやパラメーターの詳細はこちらの Docs が参考になります。

https://docs.microsoft.com/ja-jp/azure/app-service/overview-managed-identity?tabs=python#using-the-rest-protocol

ここで取得したアクセストークンを使って SQL サーバーの接続情報を作っています。SQL サーバーやデータベースの名前はその前の行で環境変数を読み込んでいます。

driver = os.environ['SQL_DRIVER']
sql_server = os.environ['SQL_SERVER']
sql_database = os.environ['SQL_DATABASE']
pyodbc.connect('DRIVER='+driver+';SERVER='+sql_server+';PORT=1433;DATABASE='+sql_database+';', attrs_before = { 1256:bytearray(token_struct) })

これらの設定がうまくいっていれば、関数アプリを使って SQL データベースを操作することができます。

レコード追加（CreateRecord）

レコード読み取り（ReadRecord）

おわりに

システムマネージド ID によってコード内に接続資格情報を使わず安全に外部のデータベースを操作できました。

前の記事

P2S VPN で Windows デバイスを接続する 2020.09.08
次の記事

Azure Private Link で Azure Monitor と接続 2020.10.09