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"
      }
    }
  }
  ...
}

 

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:

 

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:

 

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

Or

Browse the GitHub repository

 

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

Add a comment

(Will not be published)

Back to articles