0

I have the following:

  • Azure App Services in Tenant A
  • Azure SQL Server in Tenant A
  • Azure App/Enterprise Registration in Tenant B

The point of this separation was to restrict specific users to the Azure App Services website. How can I get to the point that I can execute this code on my SQL server to grant access to the app registration which sits in Tenant B?

CREATE USER [tenant_b_app_reg] FROM EXTERNAL PROVIDER;
EXEC sp_addrolemember [db_datareader], [tenant_b_app_reg];

This post seems similar to my issue (Grant service principal access to application in other tenant), but I cannot follow just the code posting with little context. It is also unclear why we post the same value multiple times object-id-of-sp-in-one-tenant in one command and it is also unclear what role-id is.

John Stud
  • 1,506
  • 23
  • 46

1 Answers1

1

• As per your query, the ‘’ is occurring multiple times in the related script because ‘object-id’ and the ‘principal-id’ of the app in one tenant is different as object-id refers to the unique id of the app registered in that tenant and the principal-id refers to the entity that requires access to that application in that tenant or across other tenants. The principal-id refers to the service principal object that defines the access policy and the permissions for that application in the Azure AD tenant.

• Whereas ‘role-id’ is the azure built-in role based on azure role-based access control functionality that is to be assigned to that application or any custom azure role that is created for assigning the scoped access controls and permissions within that concerned role, i.e., may that be ‘contributor’ or ‘user access administrator’.

 ‘New-AzureADServiceAppRoleAssignment `
   -ObjectId <object-id-of-sp-in-one-tenant> `
   -Id <role-id> `
   -PrincipalId <object-id-of-sp-in-one-tenant> `
   -ResourceId <app-id-in-other-tenant>’

• Thus, you can refer to the script in the other thread and replace the object-id and the principal-id with the ones in your environment appropriately. Also, you can try granting database access to a managed identity user in Azure AD for this purpose. You can do this by connecting to a SQL database with a system-assigned managed identity.

• Once, managed identity on app is enabled, grant permissions to that managed identity in SQL Database with the required SQL security role on the SQL prompt. Once done, modify the SQL connection string and publish the changes done. Also, ensure to enter the appropriate app-id and user-id in the powershell commands to assign the managed identity to access SQL database with the enterprise app registration. Please find the below documentation link for more details on creating a managed identity for the SQL DB for an application across the tenants: -

https://learn.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi?tabs=windowsclient%2Cdotnet

https://learn.microsoft.com/en-us/azure/app-service/tutorial-dotnetcore-sqldb-app?pivots=platform-windows

Kartik Bhiwapurkar
  • 4,550
  • 2
  • 4
  • 9