What is the best practice for creating Azure SQL with ARM template and Key vault

Question

When we create Azure SQL using ARM templates, we have to specify admin username and password using parameters. In the same ARM template, we can create Key Vaults and use key vault values as variables and use them in the server username and password. But in order to create key vault values, again we have to specify the password in the parameter.json file. So there, we lose the point of having key vault for secure database credentials. We can not create the SQL server without admin username and password either as per my knowledge. How can we overcome this?

How are you deploying the ARM? If its PowerShell, you can supply additional parameters from a PowerShell hashtable that don't have to be saved in the parameters file. If it's DevOps you can store the values as secret variables and pass into the pipeline. In all instances, ensure they are parameters of the type "securestring" — joelforsyth, Sep 13 '21 at 17:53
@joelforsyth I was thinking about deploying it through the pipeline. So really appreciate your suggestion on DevOps. So with this approach, if I understood it correctly, we can create key vault values using the pipeline variables and then use them as dependencies for SQL server, azure app configuration etc. Isn't it? — Avilon, Sep 14 '21 at 03:35
if you disable sql auth, you can relay on aad auth and no need of username/password while creating the db, is it what you re looking for ? — Thomas, Sep 14 '21 at 09:20
@Thomas I agree on utilizing AAD and managed identities for this purpose. But can we really create SQL server without giving admin username and password in the initial SQL server creation? (From the azure portal, these values are required) — Avilon, Sep 14 '21 at 11:43

score 3 · Accepted Answer · answered Sep 14 '21 at 06:45

I have tested in my environment.

If you are creating the Key Vault secrets using ARM Template through a pipeline, the best practice is to use variable groups.

Go to your project in the DevOps Organization --> Expand Pipelines --> Click on Library --> Click on + Variable Group --> Give the Variable Group Name and Add the Variable --> Click on Save

Go to Pipelines --> Select your Pipeline --> Click on Variables --> Click on Variable Groups --> Click on Link Variable Group --> Select your variable Group --> Click on Link

Now you can pass these variables to your Pipeline for the creation of Key Vault Secrets.

You can use the Key Vault secrets for the creation of Azure SQL.

@Avilon this is what I was suggesting. Make sure you accept the answer if it solved your issue! — joelforsyth, Sep 14 '21 at 12:43

Thomas · Answer 2 · 2021-09-14T13:23:58.343

You can create an Azure Sql Server without a username and password by configuring AAD Auth.
To make the below template works, I've created an AAD group. Members of this group will be sql administrators.

main.bicep file:

// Azure SQL Server name
param sqlServerName string
// Name of the AAD group that will be sql server administrator
param sqlServerAadAdminGroupName string
// Objectid of the ADD group that will be sql server administrator
param sqlServerAadAdminGroupObjectId string

resource sqlServer 'Microsoft.Sql/servers@2020-11-01-preview' = {
  name: sqlServerName
  location: resourceGroup().location
  properties: {
    version: '12.0'
    minimalTlsVersion: '1.2'
    publicNetworkAccess: 'Enabled'
    administrators: {
      administratorType: 'ActiveDirectory'
      principalType: 'Group'
      login: sqlServerAadAdminGroupName
      sid: sqlServerAadAdminGroupObjectId
      tenantId: subscription().tenantId
      azureADOnlyAuthentication: true
    }
  }
}

You can convert this bicep file to an arm template by using this AZ CLI command:

az bicep build --file main.bicep

What is the best practice for creating Azure SQL with ARM template and Key vault

2 Answers2