3

Most of the sample ARM templates I find for deploying an Azure App Service + Azure SQL database end up configuring the connection string for the web app using the administrator login credentials provided when creating the Azure SQL Server (this one from Microsoft, for example). I've also seen the App Service configured for Managed Identity and then that user granted administrative access over the Azure SQL instance via AAD.

I'd never want to provision a production application this way, but I can't find examples in a more secure configuration.

The only other info I find about provisioning non-admin users is via SQL after the Azure SQL instance is already up. This means I have to add another step outside of my ARM deployment to get my system fully functional.

As of the time this question was written, running custom Powershell scripts as part of an ARM deploy is in preview and that could be a path forward, but it's not ideal.

Can non-administrative users be provisioned in Azure SQL via an ARM template, without resorting to PowerShell?

Dean Goodman
  • 973
  • 9
  • 22
  • i would say it is not possible unless you write custom scripting (powershell or other) – Thomas Jun 25 '20 at 22:25
  • We cannot create non-administrative users when we create SQL database via arm template. Because at the moment, arm template does not define it and we just can create SQL admin and Azure AD admin : https://learn.microsoft.com/en-us/azure/templates/microsoft.sql/2014-04-01/servers/databases – Jim Xu Jun 26 '20 at 01:35

2 Answers2

2

If you want to create non-administrative users in Azure SQL database via arm template, it is impossible. Because Azure ARM template team just defines Administrators type and does not define user type or user property in server. We just can create SQL Admin or Azure AD Admin via ARM template. For more details, please refer to here and here

So if you want to create non-administrative users, you need to write custom scripting.

Jim Xu
  • 21,610
  • 2
  • 19
  • 39
  • As I suspected. Thanks for the link to the other SO question - I didn't come across that one during a previous search. – Dean Goodman Jun 26 '20 at 13:14
  • @DeanGoodman Do you have any other concerns? If you have no other concerns, could you please accept it as an answer? It may help more people. – Jim Xu Jun 29 '20 at 01:21
2

If you want to create the users as part of a template deployment, you can use the deploymentScripts resource to run TSQL or any arbitrary script:

https://learn.microsoft.com/en-us/azure/azure-resource-manager/templates/deployment-script-template?tabs=CLI

If you want to do it as part of the declaration of the Microsoft.SQL/servers resource, no you can't (as Jim mentioned).

bmoore-msft
  • 8,376
  • 20
  • 22
  • Could you elaborate on this? It seems to me the deployment script template only supports `az cli` and `powershell az`. The regular powershell command `Invoke-SqlCommand` does not exist in this environment? – span Apr 28 '22 at 08:08
  • You just need to install the module (powershell) or your tool of choice (cli) - the deploymentScript is just a container, you can do pretty much whatever you want once you're running... – bmoore-msft Apr 28 '22 at 16:05
  • Thank you for posting back. I cannot get it to work so created a new question for reference: https://stackoverflow.com/questions/72055927/use-powershell-module-in-arm-deployment-script – span Apr 29 '22 at 09:41