Azure FunctionsでマネージドIDを使ってSQL Serverにつないだメモ

# 概要
[接続文字列を使ってnode-mssqlでDBに接続](https://qiita.com/hibohiboo/items/6bdd035b91cbb38fde61)していたところを、[マネージドIDを使ってDBに接続](https://learn.microsoft.com/ja-jp/azure/azure-sql/database/azure-sql-javascript-mssql-quickstart?view=azuresql&tabs=passwordless%2Cservice-connector%2Cportal#configure-the-mssql-connection-object)に変更する。

[変更点](https://github.com/hibohiboo/async-ttrpg/pull/36)

なお、PrismaでもマネージドIDを使ってみたかったが、[Issue](https://github.com/prisma/prisma/issues/13853)にもあるように2024.09.21時点では使用できない模様。([authentication](https://github.com/tediousjs/node-mssql?tab=readme-ov-file#drivers-1)の設定が[setupMSSQL.ts](https://github.com/prisma/prisma/blob/main/packages/client/src/utils/setupMSSQL.ts)にない)

# 接続までの手順

SQL データベースで Microsoft Entra 認証を有効にする
Azure Function マネージド ID を有効にする
SQL Database へのアクセスをマネージド ID に付与する
node-mssqlの接続設定を作成する


# SQL データベースで Microsoft Entra 認証を有効にする
[チュートリアル: マネージ ID と SQL バインドを使用して Azure SQL に関数アプリを接続する](https://learn.microsoft.com/ja-jp/azure/azure-functions/functions-identity-access-azure-sql-with-managed-identity)の手順にまずは従う。

`testuser@hogeoutlook.onmicrosoft.com` というプリンシパル名を持つユーザを作成する

hogeoutlookの部分は、「Microsoft Entra ID」を開いた時の下記画面を参照。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/99876/103c446f-30d5-3e0a-f2ca-d3dd7c9d9f99.png)

```
infra/bin/add-d-b-user.sh
```


```sh:infra/bin/add-d-b-user.sh
#!/bin/bash

BIN_DIR=$(cd $(dirname $0) && pwd)
source $BIN_DIR/.env
azureaduser=$(az ad user list --filter "userPrincipalName eq '$DB_ACCESS_USER_MAIL'" --query [].id --output tsv)
if [ -z "$azureaduser" ]; then
  az ad user create --display-name $DB_ACCESS_USER_NAME --password $DB_ACCESS_USER_PW --user-principal-name $DB_ACCESS_USER_MAIL
fi

# Microsoft Entra ユーザーへのデータベース アクセスの許可
az sql server ad-admin create --resource-group $DB_RESOURCE_GROUP_NAME --server-name $DB_SERVER_NAME --display-name ADMIN --object-id $azureaduser
```

環境変数|設定値例
--|--
DB_ACCESS_USER_NAME|mogamoga
DB_ACCESS_USER_PW|mogamogaPassw0rd
DB_ACCESS_USER_MAIL|`mogauser@contosooutlook.onmicrosoft.com`
DB_RESOURCE_GROUP_NAME|myDbResourceGroup
DB_SERVER_NAME|my-server-name

# Azure Function マネージド ID を有効にする
[以前の手順の通り](https://qiita.com/hibohiboo/items/e3050de912c9e608cba0)、bicepで設定。

# SQL データベースへのアクセスをマネージド ID に付与する

作成したユーザでログインする。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/99876/bff6520b-1142-99ab-ab48-e8d81bb33277.png)

下記SQLを流す

```sql
CREATE USER functionsの名前 FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER functionsの名前;
ALTER ROLE db_datawriter ADD MEMBER functionsの名前;
```





# node-mssqlの接続設定を作成する
[Node.js と mssql npm パッケージを使用して Azure SQL Database に接続し、クエリを実行する](https://learn.microsoft.com/ja-jp/azure/azure-sql/database/azure-sql-javascript-mssql-quickstart?view=azuresql&tabs=passwordless%2Cservice-connector%2Cportal)を参考に設定する。

```ts:apps/api/src/routes/transactionTest.ts
const config = {
  server: processEnv.SQLSERVER_NAME,
  port: 1433,
  database: processEnv.SQLSERVER_DB_NAME,
  authentication: {
    type: 'azure-active-directory-default',
    options: {},
  },
  options: {
    encrypt: true,
  },
} as const;

const db = new Database(config);
```

# 参考
[Node.js と mssql npm パッケージを使用して Azure SQL Database に接続し、クエリを実行する](https://learn.microsoft.com/ja-jp/azure/azure-sql/database/azure-sql-javascript-mssql-quickstart?view=azuresql&tabs=passwordless%2Cservice-connector%2Cportal)
[チュートリアル: マネージ ID と SQL バインドを使用して Azure SQL に関数アプリを接続する](https://learn.microsoft.com/ja-jp/azure/azure-functions/functions-identity-access-azure-sql-with-managed-identity)
[マネージド ID を使用して Azure Functions から Azure SQL Database へ接続する](https://qiita.com/nassy20/items/8b316726b4fbf1bf55b8)
[[Managed Identity を使った SQL Database の認証がとても簡単になった話](https://blog.shibayan.jp/entry/20210113/1610529026)]
[Azure SQL Database との間でパスワードレス接続を使うように Node.js アプリケーションを移行する](https://learn.microsoft.com/ja-jp/azure/azure-sql/database/azure-sql-passwordless-migration-nodejs?view=azuresql&tabs=sign-in-azure-cli%2Cazure-portal-create%2Cazure-portal-assign%2Capp-service-identity)
[node-mssql](https://github.com/tediousjs/node-mssql?tab=readme-ov-file#formats)
[tedious](https://tediousjs.github.io/tedious/api-connection.html#function_newConnection)
[prisma sql ad](https://github.com/prisma/prisma/issues/13853)
[sqlにマネージドIDで接続する ](https://github.com/hibohiboo/async-ttrpg/issues/33)