How to Create Azure AD Users (Contained Accounts) on Azure SQL Database For Passwordless Database Access

[siegfried01]

After several attempts, I got this procedure to work:

https://sqlitybi.com/how-to-create-azure-ad-users-on-azure-sql-database/

I need to translate this manual procedure to bicep so I can put it in a Github CI/CD action because it is part of the Microsoft Best Practices for SQL database access.

1. Anyone know of any bicep examples that implement the above manual process?
2. I have to set something (AD user?) as the admin and in the follow along example (above URL), I set myself (my Microsoft hotmail account) as the admin of the azure SQL. What would I do in bicep executing inside a Github deployment action? Would I create another AD account? How do I do that in bicep?
3. Then I would have to execute some SQL queries that create an SQL AD account for the Azure App Service Web app... How would I do that?
   Thanks

Siegfried

[brwilkinson]

This should cover the first part, however I am not sure we have a way for the second part natively in bicep.

https://docs.microsoft.com/en-us/azure/templates/microsoft.sql/servers/administrators?tabs=bicep

e.g.
https://github.com/brwilkinson/AzureDeploymentFramework/blob/main/ADF/bicep/AZSQL-SQL.bicep#L40

All capabilities are listed under the api page:
https://docs.microsoft.com/en-us/azure/templates/microsoft.sql/allversions

E.g. the local users which uses t-sql, you can do an alternate way.

Azure data studio will get you into view the settings.

I believe if you add an Azure AD group as the Admin, it could resolve your issue without creating local users, however you may need to still create them with different permissions than Admin.

[brwilkinson]

How did you go with the Azure ad group for Admin?

[siegfried01]

Thanks for those links! Thanks for checking on me!

I've been working on other issues while waiting (and hoping) for a response from azure support incidents (paid for by me)... I was complaining to Microsoft management about the slow progress yesterday.

After pondering the official Microsoft documentation https://docs.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database?tabs=windowsclient%2Cef%2Cdotnet
(1) I'm thinking maybe this should be done in the Github actions yaml directly instead of having github yaml call bicep? I just assumed that since bicep is the new language/feature that everything should be done in bicep!
(2) I'm surprised that I did not see any references to your link on capabilities in that Microsoft tutorial. Instead they have those crazy SQL "alter role" commands (that work when I tried out the similar tutorial I mentioned above: https://github.com/Azure/bicep/discussions/url)... Very strange!

[brwilkinson]

I think you are onto the best solution there, to call in from your pipeline.

if you get a solution, feel free to share and we can mark as answered following.

[brwilkinson]

will mark as answered, since using devops pipeline or task other than Bicep is required.