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 のグループを介してではなく、直接ユーザーを追加する方法があるかは確認をしている最中なのですが、とりあえずはこのような方法で回避することができるのではないでしょうか。

追記

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

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 については、公開情報で対応できるグループを介しての設定の方が良さそうではありました。

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