SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

同一のリソース名のマネージド ID が存在する場合の SQL DB のユーザー作成について – 暫定版 –

with 2 comments

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

マネージド 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 を有効化すると、アプリケーション (エンタープライズアプリケーション) としては次のように認識されます。

image

この状態でマネージド 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 が存在していても接続することができるかと。

image

上記の画像の例であれば、「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 のグループを介して設定していたのですが、正常にテスト接続ができていますね。

image

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

Written by Masayuki.Ozawa

3月 19th, 2020 at 11:42 pm

Posted in Azure,SQL Database

Tagged with ,

2 Responses to '同一のリソース名のマネージド ID が存在する場合の SQL DB のユーザー作成について – 暫定版 –'

Subscribe to comments with RSS or TrackBack to '同一のリソース名のマネージド ID が存在する場合の SQL DB のユーザー作成について – 暫定版 –'.

  1. 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

    hsakoh

    23 3月 20 at 09:41

  2. なるほど。SID指定でできるのですね!
    AAD Group / User を指定するためのタイプがあることを知りませんでした…。
    https://stackoverflow.com/questions/53001874/cant-create-azure-sql-database-users-mapped-to-azure-ad-identities-using-servic

    Masayuki.Ozawa

    23 3月 20 at 10:11

Leave a Reply