ARM templates - web.config transform not working

Question

I am trying to deploy a website and sql azure using an Arm template and I am trying to get the sql connection string to be transformed with the deployed website using the deployed database name. the website is created, source deployed and sql azure database are created but the connection string does not get changed though. I have followed the method here: https://github.com/Azure/azure-quickstart-templates/tree/master/201-web-app-sql-database

My website.json looks like this:

{
  "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "hostingPlanName": {
      "type": "string",
      "minLength": 1
    },
    "environmentName": {
      "type": "string",
      "allowedValues": [
        "integration",
        "qa"
      ]
    },
    "skuName": {
      "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 capacity. Check details at https://azure.microsoft.com/en-us/pricing/details/app-service/"
      }
    },
    "skuCapacity": {
      "type": "int",
      "defaultValue": 1,
      "minValue": 1,
      "metadata": {
        "description": "Describes plan's instance count"
      }
    },
    "sqlserverAdminLogin": {
      "type": "string",
      "minLength": 1
    },
    "sqlserverAdminLoginPassword": {
      "type": "securestring"
    },
    "databaseName": {
      "type": "string",
      "minLength": 1
    },
    "databaseCollation": {
      "type": "string",
      "minLength": 1,
      "defaultValue": "SQL_Latin1_General_CP1_CI_AS"
    },
    "databaseEdition": {
      "type": "string",
      "defaultValue": "Basic",
      "allowedValues": [
        "Basic",
        "Standard",
        "Premium"
      ]
    },
    "databaseRequestedServiceObjectiveName": {
      "type": "string",
      "defaultValue": "Basic",
      "allowedValues": [
        "Basic",
        "S0",
        "S1",
        "S2",
        "P1",
        "P2",
        "P3"
      ],
      "metadata": {
        "description": "Describes the performance level for Edition"
      }
    }
  },
  "variables": {
    "webSiteName": "[concat('webSite-MyApp-', uniqueString(resourceGroup().id))]",
    "sqlserverName": "[concat('sqlserver-', parameters('environmentName'), '-',  uniqueString(resourceGroup().id))]"
  },
  "resources": [
    {
      "apiVersion": "2015-08-01",
      "name": "[parameters('hostingPlanName')]",
      "type": "Microsoft.Web/serverfarms",
      "location": "[resourceGroup().location]",
      "tags": {
        "displayName": "HostingPlan"
      },
      "sku": {
        "name": "[parameters('skuName')]",
        "capacity": "[parameters('skuCapacity')]"
      },
      "properties": {
        "name": "[parameters('hostingPlanName')]"
      }
    },
    {
      "apiVersion": "2015-08-01",
      "dependsOn": [
        "[resourceId('Microsoft.Web/serverfarms/', parameters('hostingPlanName'))]"
      ],
      "location": "[resourceGroup().location]",
      "name": "[variables('webSiteName')]",
      "properties": {
        "name": "[variables('webSiteName')]",
        "serverFarmId": "[resourceId('Microsoft.Web/serverfarms', parameters('hostingPlanName'))]"
      },
      "resources": [
        {
          "apiVersion": "2016-03-01",
          "type": "config",
          "name": "connectionstrings",
          "dependsOn": [
            "[variables('webSiteName')]"
          ],
          "properties": {
            "DefaultConnection": {
              "value": "[concat('Data Source=tcp:', reference(concat('Microsoft.Sql/servers/', variables('sqlserverName'))).fullyQualifiedDomainName, ',1433;Initial Catalog=', parameters('databaseName'), ';User Id=', parameters('sqlserverAdminLogin'), '@', reference(concat('Microsoft.Sql/servers/', variables('sqlserverName'))).fullyQualifiedDomainName, ';Password=', parameters('sqlserverAdminLoginPassword'), ';')]",
              "type": "SQLAzure"
            }
          }
        }
      ],
      "tags": {
        "[concat('hidden-related:', resourceGroup().id, '/providers/Microsoft.Web/serverfarms/', parameters('hostingPlanName'))]": "Resource",
        "displayName": "Website"
      },
      "type": "Microsoft.Web/sites"
    },
    {
      "name": "[variables('sqlserverName')]",
      "type": "Microsoft.Sql/servers",
      "location": "[resourceGroup().location]",
      "tags": {
        "displayName": "SqlServer"
      },
      "apiVersion": "2014-04-01",
      "properties": {
        "administratorLogin": "[parameters('sqlserverAdminLogin')]",
        "administratorLoginPassword": "[parameters('sqlserverAdminLoginPassword')]",
        "version": "12.0"
      },
      "resources": [
        {
          "name": "[parameters('databaseName')]",
          "type": "databases",
          "location": "[resourceGroup().location]",
          "tags": {
            "displayName": "Database"
          },
          "apiVersion": "2015-01-01",
          "dependsOn": [
            "[variables('sqlserverName')]"
          ],
          "properties": {
            "edition": "Basic",
            "collation": "SQL_Latin1_General_CP1_CI_AS",
            "maxSizeBytes": "1073741824",
            "requestedServiceObjectiveName": "Basic"
          }
        },
        {
          "type": "firewallrules",
          "apiVersion": "2014-04-01",
          "dependsOn": [
            "[variables('sqlserverName')]"
          ],
          "location": "[resourceGroup().location]",
          "name": "AllowAllWindowsAzureIps",
          "properties": {
            "endIpAddress": "0.0.0.0",
            "startIpAddress": "0.0.0.0"
          }
        }
      ]
    }
  ],
  "outputs": {
    "siteUri": {
      "type": "string",
      "value": "[reference(concat('Microsoft.Web/sites/', variables('webSiteName'))).hostnames[0]]"
    },
    "sqlServerFullyQualifiedDomain": {
      "type": "string",
      "value": "[reference(concat('Microsoft.Sql/servers/', variables('sqlserverName'))).fullyQualifiedDomainName]"
    }
  }
}

My parameter file is as follows:

{
  "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentParameters.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "hostingPlanName": {
      "value": "MyAppIntegration"
    },
    "environmentName": {
      "value": "integration"
    },
    "sqlserverAdminLogin": {
      "value": "azureuser"
    },
    "sqlserverAdminLoginPassword": {
      "reference": {
        "keyVault": {
          "id": "/subscriptions/XXXXXXXX/resourceGroups/resourceGroupName/providers/Microsoft.KeyVault/vaults/MyAppVault"
        },
        "secretName": "SqlAzurePassword"
      }
    },
    "databaseName": {
      "value": "myapp-integration" 
    }
  }
}

and my web.config looks like this:

ml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-MyApp-20170201054732.mdf;Initial Catalog=aspnet-MyApp-20170201054732;Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>

Can anyone tell me why the DefaultConnection never changes?

Answer 1

I've just deployed it and can confirm that the application settings gets populated properly. Web.config should not get overwritten with this template, the template creates application settings, which the app reads:

If the application setting(s) happen to already exist in your web.config file, Windows Azure Web Sites will automatically override them at runtime using the values associated with your website. Connection strings work in a similar fashion, with a small additional requirement. Remember from earlier that there is a connection string called “example-config_db” that has been associated with the website. If the website’s web.config file references the same connection string in the configuration section, then Windows Azure Web Sites will automatically update the connection string at runtime using the value shown in the portal.

Reference: https://azure.microsoft.com/en-us/blog/windows-azure-web-sites-how-application-strings-and-connection-strings-work/

Answer 2

Your DefaultConnection connection string in the web.config does not get updated via your ARM deployment simply because you actually need to deploy the web.config file as part of a MSDeploy package with a parameter for updating the DefaultConnection connection string, using the MSDeploy extension resource in the ARM Template.

E.g. In your MSDeploy Package parameters.xml, you need to define a parameter to allow updating the value for the connection string with the name DefaultConnection.

  <parameter name="Default Connection String" description="Connection string to enter into config" tags="SQL, Hidden,NoStore">
    <parameterEntry kind="XmlFile" scope="Web\.config$" match="//connectionStrings/add[@name='DefaultConnection']/@connectionString" />
  </parameter>

In addition to the above, you will also need to add the MSDeploy extension resource, which is a child resource under your website resource and set the Default Connection String parameters with your Azure SQL connection string in your ARM Template.

{
  "apiVersion": "2015-08-01",
  "dependsOn": [
    "[resourceId('Microsoft.Web/serverfarms/', parameters('hostingPlanName'))]"
  ],
  "location": "[resourceGroup().location]",
  "name": "[variables('webSiteName')]",
  "properties": {
    "name": "[variables('webSiteName')]",
    "serverFarmId": "[resourceId('Microsoft.Web/serverfarms', parameters('hostingPlanName'))]"
  },
"resources": [
    {
        "name": "MSDeploy",
        "type": "extensions",
        "location": "[resourceGroup().location]",
        "apiVersion": "2016-08-01",
        "dependsOn": [
            "[concat('Microsoft.Web/sites/', variables('webSiteName'))]"
        ],
        "tags": {
            "displayName": "webDeploy"
        },
        "properties": {
            "packageUri": "[parameters('MSDeployPackageUri')]", 
            "dbType": "None",
            "connectionString": "",
            "setParameters": {
                "Application Path": "[variables('webSiteName')]",
                "Default Connection String": "[concat('Data Source=tcp:', reference(concat('Microsoft.Sql/servers/', variables('sqlserverName'))).fullyQualifiedDomainName, ',1433;Initial Catalog=', parameters('databaseName'), ';User Id=', parameters('sqlserverAdminLogin'), '@', reference(concat('Microsoft.Sql/servers/', variables('sqlserverName'))).fullyQualifiedDomainName, ';Password=', parameters('sqlserverAdminLoginPassword'), ';')]"
            }
        }
    }
],
  "tags": {
    "[concat('hidden-related:', resourceGroup().id, '/providers/Microsoft.Web/serverfarms/', parameters('hostingPlanName'))]": "Resource",
    "displayName": "Website"
  },
  "type": "Microsoft.Web/sites"
},...

Reference: Deploy a web app with MSDeploy, custom hostname and SSL certificate