Azure-docs: Is there any way to add managed identity as db user from pipelines?

[PiotrPerak]

I tried executing this from azure pipeline
CREATE USER [] FROM EXTERNAL PROVIDER;
but I get error:

Principal 'xyz' could not be created. Only connections established with Active Directory accounts can create other Active Directory users.

Do I need to add special account for this in my Azure AD? I don't want to use my personal account.

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 31b0d33c-f88e-d50c-2926-7933940f3b92
• Version Independent ID: 4aaef906-f501-6947-5d23-1b704470f09d
• Content: Tutorial: Access data with managed identity - Azure App Service
• Content Source: articles/app-service/app-service-web-tutorial-connect-msi.md
• Service: app-service
• GitHub Login: @cephalin
• Microsoft Alias: cephalin

[Grace-MacJones-MSFT]

Hi @PiotrPerak, thanks for the question. No you don't need a special account. based on this doc, you want to set your AD account as the Active Directory admin under- Provision an Azure Active Directory administrator for your Azure SQL Database server).. you may also reference this SO post for more insights. hope that helps.

[PiotrPerak]

Hi @Grace-MacJones-MSFT.
It works for me when I execute this script manually. I have created group in Azure Ad and made it Azure AD Sql Admin.

But my question is if I really need to use login and password of one of the users in this group? I would prefer to not use my personal login and password in pipeline. I thought about adding user just for this purpose but I don't have access to do it.

[syedhassaanahmed]

+1 We have the same issue. Potentially we'll execute the following from an Azure DevOps pipeline and can't use an Azure AD user for this purpose as the login fails due to MFA policy

sqlcmd -S <server-name>.database.windows.net -d <db-name> -U <aad-user-name> -P "<aad-password>" -G -l 30

Error

AADSTS50079: Due to a configuration change made by your administrator, or because you moved to a new location, you must enroll in multi-factor authentication to access 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'

[syedhassaanahmed]

@PiotrPerak I found a workaround to this problem. You can login to the SQL Server using the SQL Authentication and when creating the user, instead of below

CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;

do the following, specifying SID

CREATE USER [<identity-name>] WITH default_schema=[dbo], SID=<SID>, TYPE=E;

SID can be calculated as specified here.

[Grace-MacJones-MSFT]

Hi @PiotrPerak, any update on this issue? Were you able to test out the workaround Syed provided?

[syedhassaanahmed]

@Grace-MacJones-MSFT Once verified, should we add the workaround in the doc itself?

[Grace-MacJones-MSFT]

@syedhassaanahmed we should be able to add it if it fits the scope of this doc. I will leave it up to the doc author to decide if the workaround should be added. @cephalin what are your thoughts?

[Grace-MacJones-MSFT]

Hi @PiotrPerak, since we have not heard back from you we will now proceed to close this thread. If there are further questions regarding this matter, please tag me in your reply. We will gladly reopen the issue and continue the discussion. thanks!

[Vossekop]

So I stumbled across this issue while trying the same thing as @PiotrPerak. @syedhassaanahmed 's proposed suggestion did not work but it got me in the right direction. Calculation of the SID as linked in his comment works for regular AAD groups and users. However for MSIs, which are basically service principals with an application registration you need to calculate the SID based on the application ID instead of the object ID.

Now when you create the MSI using an ARM template you might have noticed that neither the objectID nor the appID properties are available in the created object. There is some documentation somewhere that specifies that the object ID of the MSI is exposed as the principalId property. I came to the conclusion that the clientID property of the MSI object must therefor be the the actual appID of the service principal.

To make a long story short, use @syedhassaanahmed 's approach for creating the user in the DB, to calculate the SID use the clientID property of the MSI in the SID calculation method described in the stack overflow post @syedhassaanahmed links to.