SE の雑記

SQL Database の認証には、Azure のリソースのマネージド ID (MSI) を使用することができるようになっています。

• チュートリアル:マネージド ID を使用した App Service からの Azure SQL Database 接続のセキュリティ保護

• チュートリアル:Windows VM のシステム割り当てマネージド ID を使用して Azure SQL にアクセスする

• Azure Logic Apps でマネージド ID を使用して Azure リソースへのアクセスを認証する

• Data Factory のマネージド ID

• Azure Container Instances でマネージド ID を使用する方法

• Azure リソースの Azure AD マネージド ID のドキュメント

マネージド ID をサポートしているサービスについては  Azure リソースのマネージド ID をサポートするサービス に記載されていますが、これらのサービスが接続をする際にはマネージド ID による接続ができる可能性があります。

SQL Database に対して、各リソースのマネージド ID で接続をしたい場合は、AAD アカウントで SQL Database に接続した後に次のようなクエリで DB にユーザーを作成する必要があります。

CREATE USER [<リソース名>] FROM EXTERNAL PROVIDER

CREATE USER を実行する際に、ユーザー名として「Azure のリソース名」を設定することになります。

Azure のリソース名ですが簡単に重複するのですよね…。

例としては次のようなパターンです。

• 同一リソース名の VM
• 異なるリソースで同一の名称を設定

「engineer-memo」という名称の Azure のリソースが同一のテナント内に複数存在している場合、各リソースでマネージド ID を有効化すると、アプリケーション (エンタープライズアプリケーション) としては次のように認識されます。

この状態でマネージド ID のユーザーを SQL Database で作成する場合は次のクエリを実行することになります。

CREATE USER [engineer-memo] FROM EXTERNAL PROVIDER;

「engineer-memo」がどちらのマネージド ID を示しているかわからないですよね…。

ということで、このクエリを実行すると次のようなエラーとなります。

メッセージ 33131、レベル 16、状態 1、行 1

Principal ‘engineer-memo’ has a duplicate display name. Make the display name unique in Azure Active Directory and execute this statement again.

現象としては Principal X has a duplicate display name. と同様なのですが、マネージド ID の表示名を変更することがうまくできなかったのですよね。

ということで暫定の対応としては、

• Azure AD にグループを作成
• グループにマネージド ID を追加
• CREATE USER には Azure AD のグループ名を設定

というような形で、AAD のグループを介すことで、同一のリソース名のマネージド ID が存在していても接続することができるかと。

上記の画像の例であれば、「Wordress-ADF」という AAD のグループに「enigneer-memo」というマネージド ID のサービスプリンシパルを追加しています。

その後、SQL DB に Azure Active Directory 認証で接続して、次のような形式のクエリでユーザーの作成と適切なロールへのアサインを行います。

CREATE USER [WordPress-ADF] FROM EXTERNAL PROVIDER
EXEC sp_addrolemember'frontdoor_db_role', 'WordPress-ADF'

今回は Azure Data Factory v2 のマネージド ID を AAD のグループを介して設定していたのですが、正常にテスト接続ができていますね。

同一のリソース名のマネージド ID が存在する場合に、AAD のグループを介してではなく、直接ユーザーを追加する方法があるかは確認をしている最中なのですが、とりあえずはこのような方法で回避することができるのではないでしょうか。

追記

コメントで次のアドバイスをいただきました。

CREATE USERのType=EとSIDを指定することで同一のリソース名でも作成できます。

DECLARE @USERNAME AS NVARCHAR(36) = N’任意の名前’

DECLARE @ApplicationId AS NVARCHAR(36) = ‘<サービスプリンシパルのID’

DECLARE @SID AS NVARCHAR(max) = CONVERT(NVARCHAR(max), CONVERT(VARBINARY(16),CONVERT(uniqueidentifier,@ApplicationId)), 1)

EXECUTE(‘CREATE USER [‘ + @USERNAME + ‘] WITH SID = ‘+@SID+’, Type=E;’)

サービスプリンシパルのIDは、↓でとれるId

Get-AzAdServicePrincipal -ObjectId

ユーザー作成のステートメントについては CREATE USER (Transact-SQL) に記載されています。

本投稿のような状態については stack overflow の Can’t Create Azure SQL Database Users Mapped to Azure AD Identities using Service Principal でスレッドが立っています。

公式のドキュメントには記載されていないのですが、CREATE USER を実行する際には SID / Type オプションとして指定することができるようになっているようです。

create user [myAADGroupName] with sid = <sid>, type = X; -- type X for AAD Group
create user [myAADUserName] with sid = <sid>, type = E; -- type E for AAD User

マネージド ID のアプリケーション ID が分かれば、次のような PowerShell のコマンドで ID を SID 化した情報が作れますので、これを指定して、CREATE USER をすることで、EXTERNAL_USER を SID 指定で作成することができます。

$appId = [guid]"<Application Id>"
$sid = "";$appid.ToByteArray() | %{$sid += $_.ToSTring("X2")};"0x{0}" -f $sid

本件について、確認をしてみたところ type を指定した CREATE USER については、ドキュメントに記載されていないものとなるため、現時点では、同一のマネージド ID が存在する場合の CREATE USER については、公開情報で対応できるグループを介しての設定の方が良さそうではありました。