Using Azure Blueprints to deploy Azure SQL Server and Database with Key Vault Secrets


By:   |   Updated: 2020-08-24   |   Comments   |   Related: More > Azure


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.

AKVSecrets Image of how to create AKV Secrets

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.

AKVAccessPolicies Image on how to set ARM Access policies

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.

VSCode Visual Studio versus Visual Studio Code icons

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.

VSCodeExtension Image of VS Code extensions and demo json code format

Method #1: Deploy Logical SQL Server and SQL Database Using a Static Azure Key Vault Secret ID

StaticAKVProcess ARM template deployment using Static ID and AKV

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.

StaticMethodArtifactParams Image of parameters for the deployment of the blueprint using StaticID for key vaults

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.

StaticAKVDeployedResources Image confirming that the resources for SQL Server and Db were deployed.

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.

DynamicAKVProcess ARM template deployment using Dynamic ID and AKV

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.

DynamicIDAKVArtifactParams Image of parameters for the deployment of the blueprint using DynamicID for key vaults


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.

DynamicAKVDeployedResources Image confirming that the resources for SQL Server and Db were deployed.
Next Steps


Last Updated: 2020-08-24


get scripts

next tip button



About the author
MSSQLTips author Ron L'Esteve Ron L'Esteve is a seasoned Data Architect who holds an MBA and MSF. Ron has over 15 years of consulting experience with Microsoft Business Intelligence, data engineering, emerging cloud and big data technologies.

View all my tips
Related Resources





Comments For This Article





download





Recommended Reading

Adding Users to Azure SQL Databases

Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1

Transfer Files from SharePoint To Blob Storage with Azure Logic Apps

Continuous database deployments with Azure DevOps

Auto Scale Azure SQL DB using Azure Logic Apps








get free sql tips
agree to terms


Learn more about SQL Server tools