💻

Avanade Beef で SQL Database にマネージド ID で接続するときの注意点

2023/04/05に公開

はじめに

Avanade Beef (以下、Beef) は ASP.NET Core をベースとする Web API の自動生成ツールです。

https://github.com/Avanade/Beef

概要については以下のスライドもご覧ください。

問題点

SQL Database への接続文字列をうっかりとソース管理で公開してしまって情報漏洩になる事例がたびたび発生していますが、それを防ぐための手段としてマネージド ID を使用する方法があります。ユーザー名とパスワードではなく Azure リソースに紐づけられた ID (サービス プリンシパル) を使用することでパスワードレスで SQL Database にアクセスすることができます。

手順は以下にチュートリアルが公開されています。

https://learn.microsoft.com/ja-jp/azure/app-service/tutorial-connect-msi-sql-database?WT.mc_id=M365-MVP-5002941

  • 接続文字列を変更する

    Server=tcp:<server-name>.database.windows.net;Authentication=Active Directory Default; Database=<database-name>;
    
  • App Service でマネージド ID を公開する

  • マネージド ID にアクセス許可を付与する

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

しかしこの手順にしたがったとしても Beef の場合は 500 エラーが発生します。

解決策

というのも Beef は (たとえ構成を Entity Framework で使っていたとしても) いくつかのストアド プロシージャを使用しています。通常は Database プロジェクトで dotnet run database したときに自動的に生成されるので意識をすることはないのですが、以下のストアド プロシージャが存在します。

https://github.com/Avanade/Beef/tree/afad9130ac4981929478693f1bf18c7fcc2c4d06/tools/Beef.Database.SqlServer/Schema/dbo/Stored Procedures

上記のチュートリアルではマネージド ID に対して db_datareaderdb_datawriterdb_ddladmin のロールの割り当てを行っていますが、これらのロールではストアド プロシージャを実行できないため、エラーが発生します。これを防ぐためには、db_owner のロールを割り当てる、またはカスタム ロールを定義する必要があります。

CREATE ROLE db_executor
GRANT EXECUTE TO db_executor
ALTER ROLE db_executor ADD MEMBER [<identity-name>];

おわりに

実のところ、この事象はマネージド ID でなくとも発生するのですが、特にマネージド ID の有効化のチュートリアルにしたがって実施すると発生するので、注意いただければと思います。

Discussion

ログインするとコメントできます