How to configure connectionString in ARM-template for Azure Web App to SQL database

Question

I have a ARM-Template that deploys an Azure Web App and I want to add a connectionString to a existing SQL database hosted in Azure SQL using the concat function. But when I deploy using the template I get error message saying that unable to process template language expression.

I have tried a couple of different ways to write the connectionstring with the concat function but it does not work, tried googling it but havent found any solution.

"DBConnection": {
    "value": "[concat('Server=tcp:', concat(resourceId('Microsoft.Sql/servers', parameters('sqlServerName'))).fullyQualifiedDomainName, ',1433;Initial Catalog=', parameters('sqlDBName'), ';User Id=', parameters('sqlAdminLogin'), '@', parameters('sqlServerName'), ';Password=', parameters('sqlAdminPassword'), ';Trusted_Connection=False;Encrypt=True;Connection Timeout=30;MultipleActiveResultSets=True')]"


"DBConnection": {
    "value": "[concat('Server=tcp:', resourceId('Microsoft.Sql/servers', parameters('sqlServerName'))).fullyQualifiedDomainName, ',1433;Initial Catalog=', parameters('sqlDBName'), ';User Id=', parameters('sqlAdminLogin'), '@', parameters('sqlServerName'), ';Password=', parameters('sqlAdminPassword'), ';Trusted_Connection=False;Encrypt=True;Connection Timeout=30;MultipleActiveResultSets=True')]",

"DBConnection": {
    "value": "[concat('Server=tcp:', parameters('sqlServerName').fullyQualifiedDomainName,',1433;Initial Catalog=', parameters('sqlDBName'), ';User Id=', parameters('sqlAdminLogin'), '@', parameters('sqlServerName'), ';Password=', parameters('sqlAdminPassword'), ';Trusted_Connection=False;Encrypt=True;Connection Timeout=30;MultipleActiveResultSets=True')]"

The connection string should be in the following format if working correctly "Server=tcp:.database.windows.net,1433;Initial Catalog=;Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Does anyone have a input on how to write the concat for connectionString in ARM-Template?

Answer 1

You can use something like below.

"outputs": {
  "DbAdoConnString": {
    "type": "string",
    "value": "[concat('Server=tcp:',reference(parameters('yourservernameName')).fullyQualifiedDomainName,',1433;Initial Catalog=',parameters('dbnameName')';Persist Security Info=False;User ID=',reference(parameters('yourservernameName')).administratorLogin,';Password=',reference(parameters('yourservernameName')).administratorLoginPassword,';MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;')]"
 }
}

Here is the output for the same:

dbAdoConnString String Server=tcp:yourservername.database.windows.net,1433;Initial Catalog=dbname;Persist Security Info=False;User ID=VeryWiseAdmin;Password= ReplaceWithTheMostSecurePasswordThatEverExisted&NeverShareLikeThisWithAnyone!;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

for reference , i am adding a sample template:

{
  "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "yourservernameName": {
      "type": "string",
      "defaultValue": "yourservername2"
    },
    "yourservernameAdminLogin": {
      "type": "string",
      "defaultValue": "VeryWiseAdmin",
      "minLength": 1
    },
    "yourservernameAdminLoginPassword": {
      "type": "securestring",
      "defaultValue": "ReplaceWithTheMostSecurePasswordThatEverExisted&NeverShareLikeThisWithAnyone!"
    },
    "dbnameName": {
      "type": "string",
      "defaultValue": "dbname",
      "minLength": 1
    },
    "dbnameCollation": {
      "type": "string",
      "minLength": 1,
      "defaultValue": "SQL_Latin1_General_CP1_CI_AS"
    },
    "dbnameEdition": {
      "type": "string",
      "defaultValue": "Basic"
    },
    "dbnameRequestedServiceObjectiveName": {
      "type": "string",
      "defaultValue": "Basic"
    }
  },
  "variables": {
  },
  "resources": [
    {
      "name": "[parameters('yourservernameName')]",
      "type": "Microsoft.Sql/servers",
      "location": "West Europe",
      "apiVersion": "2014-04-01-preview",
      "dependsOn": [],
      "tags": {
        "displayName": "yourservername"
      },
      "properties": {
        "administratorLogin": "[parameters('yourservernameAdminLogin')]",
        "administratorLoginPassword": "[parameters('yourservernameAdminLoginPassword')]",
        "version": "12.0"
      },
      "resources": [
        {
          "name": "[concat(parameters('yourservernameName'),'/AllowAllWindowsAzureIps')]",
          "type": "Microsoft.Sql/servers/firewallRules",
          "location": "[resourceGroup().location]",
          "apiVersion": "2014-04-01-preview",
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers', parameters('yourservernameName'))]"
          ],
          "properties": {
            "startIpAddress": "0.0.0.0",
            "endIpAddress": "0.0.0.0"
          }
        },
        {
          "name": "[concat(parameters('yourservernameName'),'/',parameters('dbnameName'))]",
          "type": "Microsoft.Sql/servers/databases",
          "location": "West Europe",
          "apiVersion": "2014-04-01-preview",
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers', parameters('yourservernameName'))]"
          ],
          "tags": {
            "displayName": "dbname"
          },
          "properties": {
            "collation": "[parameters('dbnameCollation')]",
            "edition": "[parameters('dbnameEdition')]",
            "maxSizeBytes": "1073741824",
            "requestedServiceObjectiveName": "[parameters('dbnameRequestedServiceObjectiveName')]"
          }
        }
      ]
    }
  ],
  "outputs": {
    "SomeString": {
      "type": "string",
      "value": "What ever you want to put here"
    },
    "ServerNameParam": {
      "type": "string",
      "value": "[parameters('yourservernameName')]"
    },
    "ServerResourceID": {
      "type": "string",
      "value": "[resourceId('Microsoft.Sql/servers', parameters('yourservernameName'))]"
    },
    "ServerObject": {
      "type": "object",
      "value": "[reference(parameters('yourservernameName'))]"
    },
    "SqlServerURL": {
      "type": "string",
      "value": "[reference(parameters('yourservernameName')).fullyQualifiedDomainName]"
    },
    "DbResourceID": {
      "type": "string",
      "value": "[resourceId('Microsoft.Sql/servers/databases', parameters('yourservernameName'), parameters('dbnameName'))]"
    },
    "DbObject": {
      "type": "object",
      "value": "[reference(parameters('dbnameName'))]"
    },
    "DbAdoConnString": {
      "type": "string",
      "value": "[concat('Server=tcp:',reference(parameters('yourservernameName')).fullyQualifiedDomainName,',1433;Initial Catalog=',parameters('dbnameName'),';Persist Security Info=False;User ID=',reference(parameters('yourservernameName')).administratorLogin,';Password=',reference(parameters('yourservernameName')).administratorLoginPassword,';MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;')]"
    }
  }

}

See if this helps.

Answer 2

You can add a connection string to an app service by adding the following sub-resource to your app service resource:

{
          "name": "connectionstrings",
          "type": "config",
          "apiVersion": "2018-11-01",
          "dependsOn": [
            "[resourceId('Microsoft.Web/sites', variables('MyAppService'))]",
            "[resourceId('Microsoft.Sql/servers/databases', variables("MySqlServerName"), variables('MyDatabaseName'))]"
          ],
          "tags": {
            "displayName": "Connection strings"
          },
          "properties": {
            "MyConnectionStringName": {
              "value": "[concat('Data Source=',reference(resourceId('Microsoft.Sql/servers', variables('MySqlServerName'))).fullyQualifiedDomainName,';Initial Catalog=',variables('MyDatabaseName'),';UID=someUser;pwd=somePassword')]",
              "type": "SQLServer"
            }
          }
}

This resource depends on the app service and database to have been created. It then fills the connection string in the app service using the fully qualified name of the database server.