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

user_8742162 0
Viewed 585

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?

azure-sql-server azure-managed-identity azure-keyvault azure-active-directory azure
2 Answers

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

enter image description here

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

enter image description here

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.

user_166938760

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
user_41672000
Related