Thanks for sharing! I can confirm that what you describe in the article still works.
A couple of details would have been nice to mention though, as the MS documentation is still quite confusing as a whole:
1) It is not needed to check the Allow Azure services and resources to access this server checkbox when creating the Azure SQL Server resource. At least I managed to get this working with having both App Service and Azure SQL behind private endpoints. Some of the MS documentation on this topic instructs to enable it.
3) The Service Principal executing this IaC needs to have some permissions to MS Graph API, for instance Application.Read.All so that it can read the App Service's Managed Identity details from Azure AD to get it's application id which is then set as value to the beforementioned object_id.
It is not 100% complete but shows the basic idea. Identity (Azure AD user principal or service principal) executing that IaC needs to have that above mentioned permission on AAD side (or well, nowadays Microsoft Entra ID). Azure AD group is expected to be created beforehand and another service principal (only used in db automation) added to that group as member.
There's one more gotcha in regards to this approach. The Terraform provider (betr-io/mssql) writes the mssql_user resource to Terraform state and leads to issues if/when the client secret is changed/updated. This is a bug in the provider itself. Therefore it would be better to use a managed identity as it would be a more production-ready solution.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
Thanks for sharing! I can confirm that what you describe in the article still works.
A couple of details would have been nice to mention though, as the MS documentation is still quite confusing as a whole:
1) It is not needed to check the Allow Azure services and resources to access this server checkbox when creating the Azure SQL Server resource. At least I managed to get this working with having both App Service and Azure SQL behind private endpoints. Some of the MS documentation on this topic instructs to enable it.
2) The object_id in mssql_user Terraform resource is mandatory in this use case.
3) The Service Principal executing this IaC needs to have some permissions to MS Graph API, for instance Application.Read.All so that it can read the App Service's Managed Identity details from Azure AD to get it's application id which is then set as value to the beforementioned object_id.
Hey Masi! Could you share your repository if you have one? Thank you!
Hi Clara,
I put together a small gist which builds upon the example from this blog post: gist.github.com/Masahigo/0263ffdda...
It is not 100% complete but shows the basic idea. Identity (Azure AD user principal or service principal) executing that IaC needs to have that above mentioned permission on AAD side (or well, nowadays Microsoft Entra ID). Azure AD group is expected to be created beforehand and another service principal (only used in db automation) added to that group as member.
There's one more gotcha in regards to this approach. The Terraform provider (betr-io/mssql) writes the mssql_user resource to Terraform state and leads to issues if/when the client secret is changed/updated. This is a bug in the provider itself. Therefore it would be better to use a managed identity as it would be a more production-ready solution.