By: Ron L'Esteve | Updated: 2020-08-24 | Comments | Related: > DevOps
Problem
In my previous article, Getting Started with Azure Blueprints, I discussed the benefits of Infrastructure-as-code (IaC) and demonstrated how to provision Azure resources using Azure Blueprints. Now that I have a few basic resources provisioned in my base Blueprint, including Azure Data Factory V2, Azure Data Lake Storage Gen2, and Azure Key Vault, I am interested in creating a second blueprint to manage my SQL Server objects and begin deploying an Azure SQL Server and SQL Database by using Azure Blueprints with Azure Key Vault secrets for my login credentials. Based on the existing pre-requisites, what is the most effective method of using Azure Blueprints to deploy Azure SQL Server & Database with Key Vault Secrets?
Solution
There are a few different ARM templates available for deploying an Azure SQL Server and Database by using Azure Blueprints. Additionally, since these ARM templates are written in JSON format, this offers the ability to customize the template for a particular use case. In this demo, we will leverage the Blueprint resources that have been provisioned, specifically Azure Key Vault by manually creating a Key Vault Secret and demonstrating a few ways of provisioning a logical Azure SQL Server along with Azure SQL Database with Key Vault Secrets.
Pre-requisites
Remember to read and complete to steps outlined in the demo, Getting Started with Azure Blueprints which deploys the following Azure Resources through a blueprint:
1) Azure Data Factory V2: ADF2 is a cloud based ETL/ELT orchestration application that is widely used in the modern data and analytics platform.
2) Azure Data Lake Storage Gen2: ADLSg2 is a hierarchal namespace enabled storage layer that allows for storing raw data, and then staging and curating the data in to various zones through e-l-t orchestration patterns and processes.
3) Azure Key Vault: This resource will be used to store the Key Vault secrets, specifically the adminLoginUser and adminPassword for the SQL Server.
Once the above resources have been created in a pre-requisite Blueprint, Azure Key Vault Secrets will need to be created manually.
Navigate to Secrets in Azure Key Vault and then generate a new adminLoginUser and adminPassword containing the secret values.
After the secrets have been created, navigate to Access policies and enable access to ‘Azure Resource Manager for template deployment’. This allows the ARM template to retrieve secrets from the Key Vault.
Lastly, we may need to work with the ARM Template JSON file to either validate, customize, build, or create a parameters file. For an easy way of working with the JSON code, I’d recommend downloading Visual Studio Code and installing Azure Resource Manager (ARM) Tools for Visual Studio Code.
Note that the Visual Studio Code logo/icon is Blue versus the regular purple Visual Studio icon.
Once the Visual Studio Code ARM extension is installed, it can be seen as Enabled in the extensions tab.
Since there are many available ARM templates for deploying SQL Server and SQL Database, it can often be the case that the templates need to be customized to meet the desired use case. To customize and work with the ARM Template, save the ARM template as a .json extension and open the file in Visual Studio Code which will make use of the enabled ARM tools that were installed in this pre-requisite section.
Within the code section, notice that we can also generate a parameters.json file and use them within the template as validation to test and debug the customized ARM templates as needed.
These customizations to the ARM templates are truly limitless. For example, I can add additional Key Vault secrets linked to the credentials for Password and Username. Additionally, I can define the order for deploying resources in the ARM Templates using the dependsOn JSON element.
Method #1: Deploy Logical SQL Server and SQL Database Using a Static Azure Key Vault Secret ID
In this method of deploying the Azure SQL Server and Database, I have referenced a Key Vault Secret for the adminPassword and adminLoginUser. Additionally, the types for these elements have been set to securestring.
For more information related to ARM Template best practices along with security recommendations for parameters, specifically for securestring, read ARM Template Best Practices.
By default, a securestring datatype requires a secret name, version, and resource id that must be specified at deployment.
The json code below is an ARM template that will be used in Azure Blueprints to create an Azure SQL Server and SQL Database.
{
"$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"sqlServerName": {
"type": "string"
},
"administratorLogin": {
"type": "securestring"
},
"administratorLoginPassword": {
"type": "securestring"
},
"sqldbName": {
"type": "string",
"minLength": 1
},
"sqldbCollation": {
"type": "string",
"minLength": 1,
"defaultValue": "SQL_Latin1_General_CP1_CI_AS"
},
"sqldbEdition": {
"type": "string",
"defaultValue": "Basic",
"allowedValues": [
"Basic",
"Standard",
"Premium"
]
},
"sqldbRequestedServiceObjectiveName": {
"type": "string",
"defaultValue": "Basic",
"allowedValues": [
"Basic",
"S0",
"S1",
"S2",
"P1",
"P2",
"P3"
],
"metadata": {
"description": "Describes the performance level for Edition"
}
}
},
"resources": [
{
"name": "[parameters('sqlServerName')]",
"type": "Microsoft.Sql/servers",
"location": "[resourceGroup().location]",
"apiVersion": "2015-05-01-preview",
"dependsOn": [],
"properties": {
"administratorLogin": "[parameters('administratorLogin')]",
"administratorLoginPassword": "[parameters('administratorLoginPassword')]",
"version": "12.0"
},
"resources": [
{
"name": "AllowAllWindowsAzureIps",
"type": "firewallrules",
"location": "[resourceGroup().location]",
"apiVersion": "2015-05-01-preview",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('sqlServerName'))]"
],
"properties": {
"startIpAddress": "0.0.0.0",
"endIpAddress": "0.0.0.0"
}
},
{
"name": "[concat(parameters('sqlServerName'), '/', parameters('sqldbName'))]",
"type": "Microsoft.Sql/servers/databases",
"location": "[resourceGroup().location]",
"apiVersion": "2014-04-01-preview",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('sqlServerName'))]"
],
"properties": {
"collation": "[parameters('sqldbCollation')]",
"edition": "[parameters('sqldbEdition')]",
"maxSizeBytes": "1073741824",
"requestedServiceObjectiveName": "[parameters('sqldbRequestedServiceObjectiveName')]"
}
}
]
}
]
}
In short, the artifact parameters would contain the resource group definitions along with the acceptable parameters for the SQL Server and Database. Note that in this method we must specify the Resource ID, secret version, and secret name at deployment. We’ll need to do this for the administratorLogin and administratorPassword since we set these data types to securestring. Additionally, we can specify the SQL server and SQL database related details.
Since these resources have been deployed in the original resource group which contains the pre-requisite resources, we can now see the addition of the SQL Server and Database to the resource group.
Method #2: Deploy Logical SQL Server and SQL Database Using a Dynamic Azure Key Vault Secret ID
In a scenario where we are trying to create and use a parameter file for the deployment along with parameterized values for the vault subscription, vault resource group and vault name, we will not be able to dynamically generate the resource id in the parameter file and will need to use the following template which contains a nested template which passes the dynamically generated parameters.
The json code for this customized ARM Template is listed below. The code has been customized to create an Azure SQL Server and SQL Database using key vault secrets for the adminLoginUser and adminPassword. Additionally, the key vault resource id will be dynamically created based on the parameters.
{
"$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"location": {
"type": "string",
"defaultValue": "[resourceGroup().location]",
"metadata": {
"description": "The location where the resources will be deployed."
}
},
"vaultName": {
"type": "string",
"metadata": {
"description": "The name of the keyvault that contains the secret."
}
},
"databaseName": {
"type": "string",
"metadata": {
"description": "The name of the Database"
}
},
"adminPasswordsecretName": {
"type": "string",
"metadata": {
"description": "The name of the Password secret."
}
},
"sqlServerName": {
"type": "string",
"metadata": {
"description": "The name of the SQL Server."
}
},
"adminLoginUsersecretName": {
"type": "string",
"metadata": {
"description": "The name of the LoginUser secret."
}
},
"vaultResourceGroupName": {
"type": "string",
"metadata": {
"description": "The name of the resource group that contains the keyvault."
}
},
"vaultSubscription": {
"type": "string",
"defaultValue": "[subscription().subscriptionId]",
"metadata": {
"description": "The name of the subscription that contains the keyvault."
}
}
},
"resources": [
{
"type": "Microsoft.Resources/deployments",
"apiVersion": "2018-05-01",
"name": "dynamicSecret",
"properties": {
"mode": "Incremental",
"expressionEvaluationOptions": {
"scope": "inner"
},
"template": {
"$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"adminLoginUser": {
"type": "securestring"
},
"adminPassword": {
"type": "securestring"
},
"databaseCollation": {
"type": "string",
"minLength": 1,
"defaultValue": "SQL_Latin1_General_CP1_CI_AS"
},
"databaseName": {
"type": "string",
"metadata": {
"description": "The name of the Database"
}
},
"databaseEdition": {
"type": "string",
"defaultValue": "Basic",
"allowedValues": [
"Basic",
"Standard",
"Premium"
]
},
"transparentDataEncryption": {
"type": "string",
"allowedValues": [
"Enabled",
"Disabled"
],
"defaultValue": "Enabled",
"metadata": {
"description": "Enable or disable Transparent Data Encryption (TDE) for the database."
}
},
"databaseServiceObjectiveName": {
"type": "string",
"defaultValue": "Basic",
"allowedValues": [
"Basic",
"S0",
"S1",
"S2",
"P1",
"P2",
"P3"
],
"metadata": {
"description": "Describes the performance level for Edition"
}
},
"location": {
"type": "string"
},
"sqlServerName": {
"type": "string"
}
},
"resources": [
{
"name": "[parameters('sqlServerName')]",
"type": "Microsoft.Sql/servers",
"apiVersion": "2019-06-01-preview",
"location": "[parameters('location')]",
"tags": {
"displayName": "SqlServer"
},
"properties": {
"administratorLogin": "[parameters('adminLoginUser')]",
"administratorLoginPassword": "[parameters('adminPassword')]",
"version": "12.0"
},
"resources": [
{
"name": "[parameters('databaseName')]",
"type": "databases",
"apiVersion": "2019-06-01-preview",
"location": "[parameters('location')]",
"tags": {
"displayName": "Database"
},
"properties": {
"edition": "[parameters('databaseEdition')]",
"collation": "[parameters('databaseCollation')]",
"requestedServiceObjectiveName": "[parameters('databaseServiceObjectiveName')]"
},
"dependsOn": [
"[parameters('sqlServerName')]"
],
"resources": [
{
"comments": "Transparent Data Encryption",
"name": "current",
"type": "transparentDataEncryption",
"apiVersion": "2014-04-01-preview",
"properties": {
"status": "[parameters('transparentDataEncryption')]"
},
"dependsOn": [
"[parameters('databaseName')]"
]
}
]
},
{
"name": "AllowAllMicrosoftAzureIps",
"type": "firewallrules",
"apiVersion": "2015-05-01-preview",
"location": "[parameters('location')]",
"properties": {
"endIpAddress": "0.0.0.0",
"startIpAddress": "0.0.0.0"
},
"dependsOn": [
"[parameters('sqlServerName')]"
]
}
]
}
],
"outputs": {
"sqlFQDN": {
"type": "string",
"value": "[reference(parameters('sqlServerName')).fullyQualifiedDomainName]"
}
}
},
"parameters": {
"location": {
"value": "[parameters('location')]"
},
"sqlServerName": {
"value": "[parameters('sqlServerName')]"
},
"databaseName": {
"value": "[parameters('databaseName')]"
},
"adminLoginUser": {
"reference": {
"keyVault": {
"id": "[resourceId(parameters('vaultSubscription'), parameters('vaultResourceGroupName'), 'Microsoft.KeyVault/vaults', parameters('vaultName'))]"
},
"secretName": "[parameters('adminLoginUsersecretName')]"
}
},
"adminPassword": {
"reference": {
"keyVault": {
"id": "[resourceId(parameters('vaultSubscription'), parameters('vaultResourceGroupName'), 'Microsoft.KeyVault/vaults', parameters('vaultName'))]"
},
"secretName": "[parameters('adminPasswordsecretName')]"
}
}
}
}
}
],
"outputs": {}
}
In short, the artifact parameters would contain the resource group definition along with the acceptable parameters for the SQL Server and Database. Note that in this method we do not have to specify the Resource ID or Secret version at deployment. Additionally, we can specify the vault name, vault resource group name and vault subscription dynamically.
Since these resources have been deployed in the original resource group which contains the pre-requisite resources, we can now see the addition of the SQL Server and Database to the resource group.
Next Steps
- For more information on using Azure Key Vault parameters during resource deployments, see: Use Azure Key Vault to pass secure parameter value during deployment.
- For more information on Azure Key Vault Secrets, see: Access Key Vault secret when deploying Azure Managed Applications.
- For potential solutions to issues around passing Managed Identity Object IDs to Key Vault deployment templates, see: How to pass Managed Identity Object ID to Key Vault template in Azure Blueprints.
- For more information on working with Key Vault and Passwords using Power shell, read: Microsoft Azure Key Vault for Password Management for SQL Server Applications.
- For an alternative method of deploying Azure SQL Server and SQL Database, read: Deploy SQL Database from Azure DevOps with an ARM Template and DACPAC File.
- See this GitHub QuickStart Template for the unmodified SQL Database with TDE ARM template
- For more on dependent resource in ARM Templates, see Tutorial: Create ARM templates with dependent resources.
- For a more robust Azure SQL DB ARM Template, see: The mother of all Azure SQL Database ARM templates.
About the author
Ron L'Esteve is a trusted information technology thought leader and professional Author residing in Illinois. He brings over 20 years of IT experience and is well-known for his impactful books and article publications on Data & AI Architecture, Engineering, and Cloud Leadership. Ron completed his Master�s in Business Administration and Finance from Loyola University in Chicago. Ron brings deep tecThis author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2020-08-24
