Azure-docs: Managed Identity for several web apps connected to 1 Azure SQL Database

[bertrandpons]

Hi,

I have several web apps to connect to the same Azure SQL Database. As far as I have tested, Azure SQL Database seems to support only one Managed Identity from a web app and not several ones in the same time. It causes that if I link one web app with SQL Database then the other one throught the CLI _az sql server ad-admin create_, one of the web app becomes unable to connect to the DB.

Is it a normal behavior and how to make this scenario work ?

Thank you!

---

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: Secure SQL Database connection with managed identity - Azure App Service
• Content Source: articles/app-service/app-service-web-tutorial-connect-msi.md
• Service: app-service-web
• GitHub Login: @cephalin
• Microsoft Alias: cephalin

[Mike-Ubezzi-MSFT]

@bertrandpons Thank you for this detailed question. I believe the restriction is in the creation of the managed identity as it is assigned to an application name. The configuration requires a 1:1 relationship between the managed identity and the application name. Are you creating multiple, separate application names? As a result of this process, you are creating a Tenant ID and a Principle ID, and the Principle ID is used to configure access to the SQL Database. So, I think you need to create multiple managed identities (ManagedID1, ManagedID2, ManagedID3, etc.). Please take a look at the attached tutorial: Tutorial: Secure Azure SQL Database connection from App Service using a managed identity.

[bertrandpons]

@Mike-Ubezzi-MSFT Many thanks for your reply.
I created one managed identity for each web app therefore the Principal Id is different for each app but the Tenant Id remains the same as the apps are in the same tenant. So the behavior is like the SQL Server accepts only one “service account”. There may be several options and tests to perform:

• use a user-assigned identity instead of a system-assigned identity
  
  
  
  • use a system-assigned identity for one web app then create an AD group in which I add this identity, then create managed identity for the other app that I then include in the same AD group (not sure of this option!)
  
  

Do you think it could work ?

Thank you !

[Mike-Ubezzi-MSFT]

@bertrandpons Are you using a single database or do you have multiple databases hosted on a single SQL logical server, one for each web application? Do the multiple Managed Identities work with your SQL deployment but you want to narrow this down to a single Managed Identity? With this information, I can do some research. Thank you.

[bertrandpons]

@Mike-Ubezzi-MSFT I am using one single common database for the 2 web apps. I just want that the managed identity for each web app be approved by the SQL server to allow the web apps to connect to the database in the same time with their own managed identity.
Currently if I link the database server to one web app’s managed identity, the access to the DB from the second web app is dropped

[Mike-Ubezzi-MSFT]

@bertrandpons We figured out a way to make this work with 2 continuous running Azure webjobs. I am going to have @KalyanChanumolu-MSFT reply directly to this issue to provide some guidance.

[bertrandpons]

@Mike-Ubezzi-MSFT I did a couple tests and none of them solve the problem for now:

• user-assigned identity only works for VM apparently
• I create an AD group to which I added the managed identities for the 2 web apps, then attached this AD group as sql server admin but it doesn’t work.

I am continuing investigating. Have you been able to find something ?

Thanks !

[bertrandpons]

@Mike-Ubezzi-MSFT great news! We wrote our comment at the same time. Look forward to see the process

[Mike-Ubezzi-MSFT]

@bertrandpons I believe the solution is to add the individual Managed Identities as Service Principles to an AD Group and then add the group to the SQL server. Please see: Grant minimal privileges to identity

[bertrandpons]

@Mike-Ubezzi-MSFT I tried this solution with the AD group but it did not work. I may have done it the wrong way. I am going to retry.
One question I have: do I really have to set an AD administrator as sql server admin as described in the doc ? In this case, the AD group can not be sql server admin at the same time, right ? I do not clearly understand how it works

[Mike-Ubezzi-MSFT]

@bertrandpons Correct. The AD Admin and the AD Group are separate. That is why the instructions I provided say "Grant minimal privileges to identity". Minimal is the keyword! We got it working with this configuration. Please note where it says log in with AD credentials...not SQL Server authentication. Please pay close attention to this. You need an AD Administrator tied to the SQL Server but the AD Group is added via these steps: Grant permissions to Azure Active Directory group. You might need to go back over these steps Provision an Azure Active Directory administrator for your Azure SQL Database, to remove the AD Group and re-establish the specific AD Administrator.

[KalyanChanumolu-MSFT]

@bertrandpons

1. Add both Service Principals to the AD group.
2. Add the AD Group as an EXTERNAL PROVIDER on your SQL Server database.
3. Grant appropriate role privileges to the AD Group on your SQL Server database.

Neither the Service Principals nor the AD Group need to be added as AD Admin on your SQL Server.
The AD admin can be yourself or a database administrator in your organization.

Please let us know if this still doesn't work for you.
I can upload a code sample to help you sort this out.

[bertrandpons]

@Mike-Ubezzi-MSFT @KalyanChanumolu-MSFT Thank you for your great support and your explanations. It works as expected.

Just one more question: in case of web app autoscaling, is managed identity well handled ?

[KalyanChanumolu-MSFT]

@bertrandpons

MI creates an Identity tied to the web app and persists until MI is disabled or the resource is deleted.
We will now proceed to close this thread. If there are further questions regarding this matter, please comment and we will gladly continue the discussion.