Go to Qiita Advent Calendar 2024 Top
LoginSignup
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@yoshioterada(Yoshio Terada)inMicrosoft

Azure Spring Apps でシステム・マネージドの Identity を利用して MySQL Flexible Server にパスワードレスで接続する方法

Last updated at Posted at 2023-03-15

こちらは、Azure Spring Apps でシステム・マネージドの Identity を利用して MySQL Flexible Server に接続する方法の自分用メモです

環境変数の設定

# リソースグループ名
# MySQL に作成する DB 名
# インストール・ロケーション
# Azure Spring Apps のインスタンス名
# Azure Spring Apps のアプリケーション名
# 作成するユーザ Managed Identity 名
# Azure CLI のログイン・ユーザ名
# Azure CLI のログイン・ユーザのオブジェクトID

export AZ_RESOURCE_GROUP=Password-Less-Access
export AZ_DATABASE_NAME=yoshiodb
export AZ_LOCATION=japaneast
export AZ_SPRING_APPS_SERVICE_NAME=yoshio-spring-apps
export AZ_SPRING_APPS_APP_NAME=petclinic-service
export AZ_USER_IDENTITY_NAME=user-identity-for-yoshio

export CURRENT_USERNAME=$(az ad signed-in-user show --query userPrincipalName --output tsv)
export CURRENT_USER_OBJECTID=$(az ad signed-in-user show --query id --output tsv)

リソース・グループの作成

az group create \
    --name $AZ_RESOURCE_GROUP \
    --location $AZ_LOCATION

MySQL Flexible サーバの作成

setup_mysql.shを入手

./setup_mysql.sh flexible

----
[INFO] -------------------------------------------------------
[INFO] Azure Database for MySQL Setup Completed SUCCESS
[INFO] -------------------------------------------------------
[INFO] 1. Please copy the following value into your temporal file
[INFO]
[INFO] RESOURCE GROUP is Password-Less-Access
[INFO] MySQL HOSTNAME is mysqlserver-lkmkqtzrik.mysql.database.azure.com
[INFO] MySQL USERNAME is azureuser
[INFO] MySQL PASSWORD is !psUvMYai11789
[INFO]
[INFO]
[INFO] 2. Please execute the following command.
[INFO]
[INFO] mysql -u azureuser -h mysqlserver-lkmkqtzrik.mysql.database.azure.com -p [Enter Key]
[INFO] Enter password: !psUvMYai11789 [COPY&PASTE]
[INFO] 
[INFO] 
[INFO] 3. Clean up Resource (Delete MySQL DB)
[INFO] az group delete -n Password-Less-Access
[INFO] -------------------------------------------------------

MySQL サーバ名の設定

export AZ_DATABASE_SERVER_NAME=mysqlserver-lkmkqtzrik

MySQL 内に新規 DB の作成

az mysql flexible-server db create --resource-group $AZ_RESOURCE_GROUP \
    --server-name $AZ_DATABASE_SERVER_NAME  --database-name $AZ_DATABASE_NAME

MySQL サーバ・インスタンスに接続し文字セットの変更

mysql -u azureuser -h $AZ_DATABASE_SERVER_NAME.mysql.database.azure.com -p

mysql> show variables like "chara%";
+--------------------------+------------------------+
| Variable_name            | Value                  |
+--------------------------+------------------------+
| character_set_client     | latin1                 |
| character_set_connection | latin1                 |
| character_set_database   | latin1                 |
| character_set_filesystem | binary                 |
| character_set_results    | latin1                 |
| character_set_server     | latin1                 |
| character_set_system     | utf8                   |
| character_sets_dir       | /mysql/share/charsets/ |
+--------------------------+------------------------+
8 rows in set (0.19 sec)

mysql> SET character_set_client=utf8mb4 ;
mysql> SET character_set_connection=utf8mb4 ;
mysql> SET character_set_database=utf8mb4 ;
mysql> SET character_set_results=utf8mb4 ;
mysql> SET character_set_server=utf8mb4 ;

ユーザ・Identity を作成

AZ_IDENTITY_RESOURCE_ID=$(az identity create \
    --name $AZ_USER_IDENTITY_NAME \
    --resource-group $AZ_RESOURCE_GROUP \
    --query id \
    --output tsv)

AZ_IDENTITY_PRINCIPAL_ID=$(az identity show \
    --name $AZ_USER_IDENTITY_NAME \
    --resource-group $AZ_RESOURCE_GROUP \
    --query principalId \
    --output tsv)

echo $AZ_IDENTITY_RESOURCE_ID
echo $AZ_IDENTITY_PRINCIPAL_ID

認可の設定(Authorization_Request)

uri=https://graph.microsoft.com/v1.0/servicePrincipals/$AZ_IDENTITY_PRINCIPAL_ID/appRoleAssignments

graphResourceId=$(az ad sp list --display-name "Microsoft Graph" \
                     --query "[0].id" --out tsv)

userReadAll=$(az ad sp list --display-name "Microsoft Graph" \
             --query "[0].appRoles[?value=='User.Read.All' && contains(allowedMemberTypes, 'Application')].id" \
             --output tsv)
groupReadAll=$(az ad sp list --display-name "Microsoft Graph" \
             --query "[0].appRoles[?value=='GroupMember.Read.All' && contains(allowedMemberTypes, 'Application')].id" \
             --output tsv)
appReadAll=$(az ad sp list --display-name "Microsoft Graph" \
             --query "[0].appRoles[?value=='Application.Read.All' && contains(allowedMemberTypes, 'Application')].id" \
             --output tsv)

body1="{'principalId':'$AZ_IDENTITY_PRINCIPAL_ID','resourceId':'$graphResourceId','appRoleId':'$userReadAll'}"
body2="{'principalId':'$AZ_IDENTITY_PRINCIPAL_ID','resourceId':'$graphResourceId','appRoleId':'$groupReadAll'}"
body3="{'principalId':'$AZ_IDENTITY_PRINCIPAL_ID','resourceId':'$graphResourceId','appRoleId':'$appReadAll'}"

az rest --method post --uri $uri --body $body1 --headers "Content-Type=application/json"
az rest --method post --uri $uri --body $body2 --headers "Content-Type=application/json"
az rest --method post --uri $uri --body $body3 --headers "Content-Type=application/json"

Azure Spring Apps の作成

az spring create \
    --resource-group $AZ_RESOURCE_GROUP \
    --name $AZ_SPRING_APPS_SERVICE_NAME \
    --location $AZ_LOCATION

Azure Spring Apps にアプリケーションの作成

az spring app create \
    --deployment-name production \
    --resource-group $AZ_RESOURCE_GROUP \
    --service $AZ_SPRING_APPS_SERVICE_NAME \
    --name $AZ_SPRING_APPS_APP_NAME \
    --assign-endpoint true

System Managed Identity で MySQL との接続用アカウント作成

az spring connection create mysql-flexible \
    --deployment production \
    --resource-group $AZ_RESOURCE_GROUP \
    --service $AZ_SPRING_APPS_SERVICE_NAME \
    --app $AZ_SPRING_APPS_APP_NAME \
    --connection user_for_springapp \
    --tg $AZ_RESOURCE_GROUP \
    --server $AZ_DATABASE_SERVER_NAME \
    --database $AZ_DATABASE_NAME \
    --system-identity mysql-identity-id=$AZ_IDENTITY_RESOURCE_ID \
    --client-type springboot

MySQL に接続

mysql -u azureuser -h $AZ_DATABASE_SERVER_NAME.mysql.database.azure.com -p

MySQL PASSWORD is !psUvMYai11789

MySQL に "ad_user_for_springapp" の管理者が自動生成され
このアカウントを利用しログインが可能となる事を確認

確認方法

mysql> select user,plugin from mysql.user;

アプリケーション、プロパティの編集（DB ユーザの編集）

  1. application.properties の編集
  2. pom.xml の確認
mvn clean package

Azure Spring Apps にアプリケーションのデプロイ

az spring app deploy \
    --resource-group $AZ_RESOURCE_GROUP \
    --service $AZ_SPRING_APPS_SERVICE_NAME \
    --name $AZ_SPRING_APPS_APP_NAME \
    --env "SPRING_DATASOURCE_AZURE_PASSWORDLESSENABLED=true" \
    --artifact-path target/spring-petclinic-2.7.8.jar

Azure Functions でも System Managed Identity を確認 (Java アプリ）

mvn clean package
mvn azure-functions:deploy

Azure Functions (Azure App Service)用システム・マネージドIDの作成

az webapp connection create  mysql-flexible \
    --resource-group $AZ_RESOURCE_GROUP \
    -n Java-Azure-Functions-yoshio \
    --connection user_for_webapp \
    --tg $AZ_RESOURCE_GROUP \
    --server $AZ_DATABASE_SERVER_NAME \
    --database $AZ_DATABASE_NAME \
    --system-identity mysql-identity-id=$AZ_IDENTITY_RESOURCE_ID \
    --client-type java

mysql> select user,plugin from mysql.user;
1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?