Alternative Architecture DOJO

オルターブースのクラウドネイティブ特化型ブログです。

Azure SQL DatabaseにマネージドIDを使って安全に接続する

こんにちは。MLBお兄さんこと松村です。プロ野球のキャンプが始まりましたね!球春到来です。


アプリケーションに必要なシークレットは適切な方法で管理する必要があります。
ここでいうシークレットとは、パスワード、アクセストークン、接続文字列、APIキーなど、取り扱いに注意が必要な値のことです。
Azure 上でこのようなシークレットを扱う場合、Azure Key Vault にシークレットを保管して、適切な資格情報でシークレットにアクセスするという方法がよく紹介されています。

今回は Azure SQL Database の接続文字列を安全に扱う方法を試したのでまとめます。

Azure SQL Database の接続文字列

Azure SQL Database のリソースを作成するとポータルやコマンドラインで接続文字列を取得することができます。

$ az sql db show-connection-string --name sql-managedid --server sqlsrv-managedid --client ado.net
"Server=tcp:sqlsrv-managedid.database.windows.net,1433;Database=sql-managedid;User ID=<username>;Password=<password>;Encrypt=true;Connection Timeout=30;"

見て分かるように接続文字列には、サーバーの FQDN 、ユーザー ID 、パスワードなどが含まれています。
そのためこの接続文字列は安全に取り扱う必要がありますし、もし悪意のある人に知られてしまうと悪用される可能性はあります。
(当然ですが接続元の IP アドレスをサーバーのファイアーウォールに設定するようにしましょう)

Azure SQL Database にマネージド ID で接続する

上記のようにユーザー情報を含む接続文字列を使ってデータベースに接続するのではなく、マネージド ID という仕組みを用いて Azure SQL Database に接続することで安全性を確保することができます。
つまり、Azure SQL Database に接続可能なリソースを限定しちゃおうぜというものです。

docs.microsoft.com

マネージド ID で接続する準備

ポータルで Azure SQL Database を作成し、ファイアーウォール設定にて自身のクライアント IP を追加し、「Azure サービスおよびリソースにこのサーバーへのアクセスを許可する」を有効にしておきます。

f:id:tech-tsubaki:20210223173405p:plain

次に Azure にログインしている自身の Azure AD アカウントで SQL Database に接続できるようユーザーアカウントを整理します。

# 自身の Azure AD ユーザーのプリンシパル名を調べる
$ az ad user list --query [].userPrincipalName

# 自身のオブジェクト ID を取得する
$ azureaduser=$(az ad user list --filter "userPrincipalName eq '<put your principal name>'" --query [].objectId --output tsv)

# Azure AD ユーザーを Active Directory 管理者に追加する
$ az sql server ad-admin create --resource-group <put your resource group name> --server-name <put your sql server name> --display-name ADMIN --object-id $azureaduser

ASP.NET Core でマネージド ID での DB 接続を実装する

ASP.NET Core でマネージド ID を扱う場合はこちらのパッケージをインストールします。(Azure SQL Database に限らず)

www.nuget.org

Entity Framework Core を使った DB 操作を行う場合、アプリケーションで管理している DbContext クラスのコンストラクタでマネージド ID での認証を指定します。

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Azure.Services.AppAuthentication;

public class MyDatabaseContext : DbContext
{
    public MyDatabaseContext (DbContextOptions<MyDatabaseContext> options)
        : base(options)
    {
        var connection = (Microsoft.Data.SqlClient.SqlConnection)Database.GetDbConnection();
        var provider = new Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider();
        var accessToken = provider.GetAccessTokenAsync("https://database.windows.net/", "<put-your-tenant-id>").Result;
        connection.AccessToken = accessToken;
    }

複数のテナントにアクセスできるユーザーの場合、目的のテナントを明確にしておく必要があります。
アクセストークンを取得するときに対象のテナント ID を指定することをオススメします。 (<put-your-tenant-id> の部分)
※テナント ID の指定は Azure のアカウントが複数のテナントに属している場合のみ必須ですが、常に指定しておくと良いと思います。

また、マネージド ID を使う場合は SQL Database の接続文字列からユーザー情報を削除することができます。

{
  "ConnectionStrings": {
    "MyDbConnection": "Server=tcp:sqlsrv-managedid.database.windows.net,1433;Database=sql-managedid;User ID=<username>;Password=<password>;Encrypt=true;Connection Timeout=30;"
  }
}

{
  "ConnectionStrings": {
    "MyDbConnection": "Server=tcp:sqlsrv-managedid.database.windows.net,1433;Database=sql-managedid;Encrypt=true;Connection Timeout=30;"
  }
}

Visual Studio を使っている場合、マネージド ID で使用するアカウントを指定しておく必要があります。
VS > ツール > オプション > Azure サービス認証 > 自身のアカウントを選択

f:id:tech-tsubaki:20210223173458p:plain

これでローカル環境から Azure のアカウントを使って Azure SQL Database に接続することができました。

Web App から SQL Database にマネージド ID で接続する

この状態で Web App にデプロイしても SQL Database に接続できないためアプリケーションでエラーが起きます。

f:id:tech-tsubaki:20210223173514p:plain

そのため Web App から SQL Database に接続できるように設定を行います。
ポータルから Web App のマネージド ID を有効にします。

f:id:tech-tsubaki:20210223173537p:plain

その Web App に SQL Database へのアクセス許可を付与します。
ドキュメントでは sqlcmd で実行するよう記載されていますが、私が試した際は sqlcmd で接続することができなかったので、ポータルで下記のコマンドを実行しました。

CREATE USER [app-sqlsrv-managedid] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [app-sqlsrv-managedid];
ALTER ROLE db_datawriter ADD MEMBER [app-sqlsrv-managedid];
ALTER ROLE db_ddladmin ADD MEMBER [app-sqlsrv-managedid];
GO

f:id:tech-tsubaki:20210223173802p:plain

これで Web App のマネージド ID を使って SQL Database に接続することができました。
アプリケーションからもきちんとデータを取得できています。

f:id:tech-tsubaki:20210223173816p:plain

ソースコードや環境変数に SQL Database の認証情報を保管しておく必要がなくなり、かつ接続元を Web App リソースに限定できるようになったため、セキュアな構成にすることができました。積極的に使っていきたい機能ですね。