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

Question

I have an 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 an 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 haven't 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:<SQLSERVERNAME>.database.windows.net,1433;Initial Catalog=<DBNAME>;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 connectionStringin ARM Template?

David Gardiner · Accepted Answer · 2022-01-19 02:42:06Z

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.

this doesn't set the connection string in the app service now does it? I believe that was the question

PaulVrugt · Accepted Answer · 2020-08-12 14:15:59Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related