Deploying a web app and an Azure SQL database with ARM template without providing any password

Do you really need to provide a database password in the parameters of your ARM template?

Using an ARM template to create a Website and a SQL Database together is an easy task with Azure. However, each time you need to provide an SQL Admin username and password.

In a previous article, we've discovered how to automatically generate a password for an Azure SQL database with ARM template.

Today, following this article, we will learn how to deploy a web app in Azure App Service and an Azure SQL Database with ARM template without providing any password. Our ARM template will be created in a new Azure Resource Group deployment project in Visual Studio.

Creation

Let's declare the parameters of the ARM template:

{
  "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "databaseCollation": {
      "type": "string",
      "defaultValue": "SQL_Latin1_General_CP1_CI_AS"
    },
    "databaseEdition": {
      "type": "string",
      "defaultValue": "Basic",
      "allowedValues": [
        "Basic",
        "Standard",
        "Premium"
      ]
    },
    "databaseMaxSizeBytes": {
      "type": "string",
      "defaultValue": "1073741824"
    },
    "databaseRequestedServiceObjectiveName": {
      "type": "string",
      "defaultValue": "Basic",
      "allowedValues": [
        "Basic",
        "S0",
        "S1",
        "S2",
        "P1",
        "P2",
        "P3"
      ],
      "metadata": {
        "description": "Describes the performance level for Edition"
      }
    },
    "hostingPlanSkuName": {
      "type": "string",
      "defaultValue": "F1",
      "allowedValues": [
        "F1",
        "D1",
        "B1",
        "B2",
        "B3",
        "S1",
        "S2",
        "S3",
        "P1",
        "P2",
        "P3",
        "P4"
      ],
      "metadata": {
        "description": "Describes plan's pricing tier and instance size. Check details at https://azure.microsoft.com/en-us/pricing/details/app-service/"
      }
    },
    "hostingPlanSkuCapacity": {
      "type": "int",
      "defaultValue": 1,
      "minValue": 1,
      "metadata": {
        "description": "Describes plan's instance count"
      }
    }
  }
  ...
}

databaseCollation: the collation of the database, by default SQL_Latin1_General_CP1_CI_AS.
databaseEdition: the edition of the database, by default Basic.
databaseMaxSizeBytes: the size of database, by default 1GB.
databaseRequestedServiceObjectiveName: the pricing tier of the database, by default Basic.
hostingPlanSkuName: the pricing tier of the hosting plan, by default F1.
hostingPlanSkuCapacity: the hosting plan's instance count, by default 1.

Now we will declare the variables of the ARM template:

{
  ...
  "variables": {
    "databaseName": "[concat('mydb', uniqueString(resourceGroup().id, 'E931CAC0-7259-4FA1-80B4-BD47CBA4E040'))]",
    "hostingPlanName": "[concat('myhostingPlan', uniqueString(resourceGroup().id))]",
    "sqlserverAdminLogin": "[concat('l', uniqueString(resourceGroup().id, '9A08DDB9-95A1-495F-9263-D89738ED4205'))]",
    "sqlserverAdminPassword": "[concat('P', uniqueString(resourceGroup().id, '224F5A8B-51DB-46A3-A7C8-59B0DD584A41'), 'x', '!')]",
    "sqlserverName": "[concat('mysqlserver', uniqueString(resourceGroup().id))]",
    "websiteName": "[concat('mywebsite', uniqueString(resourceGroup().id, '274ACF17-FF18-4DB2-B3A8-AABCA4CB7D54'))]"
  }
  ...
}

The variables were already described in the previous article, but here what we can pay attention to:

We use a lot the ARM template function uniqueString allowing us to create a deterministic hash string based on the values provided as parameters.
We are able to create a random database name, admin login, admin password, SQL server name.
The names will be unique for the resource group.
To make sure that we are compliant with the Azure SQL database policy "Your password must contain characters from three of the following categories – English uppercase letters, English lowercase letters, numbers (0-9), and non-alphanumeric characters (!, $, #, %, etc.)", we insert one character for each category before and after the unique string.

And to finish we will declare the resources of the ARM template:

{
  ...
  "resources": [
    {
      "apiVersion": "2015-08-01",
      "name": "[variables('hostingPlanName')]",
      "type": "Microsoft.Web/serverfarms",
      "location": "[resourceGroup().location]",
      "properties": {
        "name": "[variables('hostingPlanName')]"
      },
      "sku": {
        "name": "[parameters('hostingPlanSkuName')]",
        "capacity": "[parameters('hostingPlanSkuCapacity')]"
      },
      "tags": {
        "displayName": "HostingPlan"
      }
    },
    {
      "apiVersion": "2014-04-01-preview",
      "name": "[variables('sqlserverName')]",
      "type": "Microsoft.Sql/servers",
      "location": "[resourceGroup().location]",
      "properties": {
        "administratorLogin": "[variables('sqlserverAdminLogin')]",
        "administratorLoginPassword": "[variables('sqlserverAdminPassword')]"
      },
      "tags": {
        "displayName": "SqlServer"
      },
      "resources": [
        {
          "apiVersion": "2014-04-01-preview",
          "name": "[variables('databaseName')]",
          "type": "databases",
          "location": "[resourceGroup().location]",
          "dependsOn": [
            "[concat('Microsoft.Sql/servers/', variables('sqlserverName'))]"
          ],
          "properties": {
            "edition": "[parameters('databaseEdition')]",
            "collation": "[parameters('databaseCollation')]",
            "maxSizeBytes": "[parameters('databaseMaxSizeBytes')]",
            "requestedServiceObjectiveName": "[parameters('databaseRequestedServiceObjectiveName')]"
          },
          "tags": {
            "displayName": "Database"
          }
        },
        {
          "apiVersion": "2014-04-01-preview",
          "name": "AllowAllWindowsAzureIps",
          "type": "firewallrules",
          "location": "[resourceGroup().location]",
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers', variables('sqlserverName'))]"
          ],
          "properties": {
            "startIpAddress": "0.0.0.0",
            "endIpAddress": "0.0.0.0"
          }
        }
      ]
    },
    {
      "apiVersion": "2015-08-01",
      "name": "[variables('websiteName')]",
      "type": "Microsoft.Web/sites",
      "location": "[resourceGroup().location]",
      "dependsOn": [
        "[concat('Microsoft.Web/serverFarms/', variables('hostingPlanName'))]"
      ],
      "properties": {
        "name": "[variables('websiteName')]",
        "serverFarmId": "[resourceId('Microsoft.Web/serverfarms', variables('hostingPlanName'))]"
      },
      "tags": {
        "[concat('hidden-related:', resourceGroup().id, '/providers/Microsoft.Web/serverfarms/', variables('hostingPlanName'))]": "empty",
        "displayName": "Website"
      },
      "resources": [
        {
          "apiVersion": "2015-08-01",
          "name": "connectionstrings",
          "type": "config",
          "dependsOn": [
            "[resourceId('Microsoft.Web/sites', variables('websiteName'))]"
          ],
          "properties": {
            "MyDbConnectionString": {
              "value": "[concat('Data Source=tcp:', reference(concat('Microsoft.Sql/servers/', variables('sqlserverName'))).fullyQualifiedDomainName, ',1433;Initial Catalog=', variables('databaseName'), ';User Id=', variables('sqlserverAdminLogin'), '@', variables('sqlserverName'), ';Password=', variables('sqlserverAdminPassword'), ';')]",
              "type": "SQLServer"
            }
          }
        }
      ]
    }
  ]
  ...
}

Here we declare the website, the SQL Server and its database:

As you can notice, we are declaring the connection strings of the website.
We are setting the complete connection string MyDbConnectionString for our database using the generated password.

Example of use

The ARM template is now ready, let's open a Windows PowerShell and try it:

.\Deploy-AzureResourceGroup.ps1 -ResourceGroupName 'MyResourceGroupName' -ResourceGroupLocation 'canadaeast' -TemplateFile '.\azuredeploy.json'

...

Resource Microsoft.Sql/servers/databases 'mysqlserverdprkifjzxpany/mydbodsrno26t2xnk' provisioning status is succeeded

If everything goes well, you should see the same kind of message as above.

To go further

If you check the application settings via the portal, the connection string will look like the following:

MyDbConnectionString = Data Source=tcp:mysqlserverdprkifjzxpany.database.windows.net,1433;Initial Catalog=mydbodsrno26t2xnk;User Id=lgjytuqjz44e7s@mysqlserverdprkifjzxpany;Password=Pxhhg2lv4jbx2qx!;

As no user needs to know the password, you could enforce the security by changing the password on every deployment using the deployment name in the ARM template function uniqueString. Change the password variable as following:

"sqlserverAdminPassword": "[concat('P', uniqueString(resourceGroup().id, deployment().name, '224F5A8B-51DB-46A3-A7C8-59B0DD584A41'), 'x', '!')]"

Summary

You can't access the SQL database as you don't know the generated password but the web app can access it and that's what we want. Now via Entity Framework Code First for example, your web application would now be able to create the database tables.

We have seen how to create an ARM template that will deploy a web app and an Azure SQL database without providing any password.

You can download the example solution here:

Download full sources

Browse the GitHub repository

Please feel free to comment or contact me if you have any question about this article.

Comments

Friday, March 15, 2019 4:02 AM - Matt Frear

English

Interesting idea. One downside is that your web app will be accessing the database as an admin, which is probably not desirable.

Add a comment

Language

Name

E-mail (Will not be published)

Website url

Message

Back to articles