Connect an App Service app to SQL Database using CLI

This sample script creates a database in Azure SQL Database and an App Service app. It then links the database to the app using app settings.

If you don't have an Azure subscription, create an Azure free account before you begin.

Prerequisites

Sample script

Launch Azure Cloud Shell

The Azure Cloud Shell is a free interactive shell that you can use to run the steps in this article. It has common Azure tools preinstalled and configured to use with your account.

To open the Cloud Shell, just select Try it from the upper right corner of a code block. You can also launch Cloud Shell in a separate browser tab by going to https://shell.azure.com.

When Cloud Shell opens, verify that Bash is selected for your environment. Subsequent sessions will use Azure CLI in a Bash environment, Select Copy to copy the blocks of code, paste it into the Cloud Shell, and press Enter to run it.

Sign in to Azure

Cloud Shell is automatically authenticated under the initial account signed-in with. Use the following script to sign in using a different subscription, replacing <Subscription ID> with your Azure Subscription ID. If you don't have an Azure subscription, create an Azure free account before you begin.

subscription="<subscriptionId>" # add subscription here

az account set -s $subscription # ...or use 'az login'

For more information, see set active subscription or log in interactively

Run the script

# Connect an App Service app to SQL Database
# set -e # exit if error
# Variable block
let "randomIdentifier=$RANDOM*$RANDOM"
location="East US"
resourceGroup="msdocs-app-service-rg-$randomIdentifier"
tag="connect-to-sql.sh"
appServicePlan="msdocs-app-service-plan-$randomIdentifier"
webapp="msdocs-web-app-$randomIdentifier"
server="msdocs-azuresql-$randomIdentifier"
database="msdocsazuresqldb$randomIdentifier"
login="azureuser"
password="Pa$$w0rD-$randomIdentifier"
startIp="0.0.0.0"
endIp="0.0.0.0"

# Create a resource group.
echo "Creating $resourceGroup in "$location"..."
az group create --name $resourceGroup --location "$location" --tag $tag

# Create an App Service Plan
echo "Creating $appServicePlan"
az appservice plan create --name $appServicePlan --resource-group $resourceGroup \
--location "$location"

# Create a Web App
echo "Creating $webapp"
az webapp create --name $webapp --plan $appServicePlan --resource-group $resourceGroup 

# Create a SQL Database server
echo "Creating $server"
az sql server create --name $server --resource-group $resourceGroup --location "$location" --admin-user $login --admin-password $password

# Configure firewall for Azure access
echo "Creating firewall rule with starting ip of $startIp" and ending ip of $endIp
az sql server firewall-rule create \
--server $server \
--resource-group $resourceGroup \
--name AllowYourIp \
--start-ip-address $startIp --end-ip-address $endIp

# Create a database called 'MySampleDatabase' on server
echo "Creating $database"
az sql db create --server $server \
 --resource-group $resourceGroup --name $database \
--service-objective S0

# Get connection string for the database
connstring=$(az sql db show-connection-string --name $database --server $server \
--client ado.net --output tsv)

# Add credentials to connection string
connstring=${connstring//<username>/$login}
connstring=${connstring//<password>/$password}

# Assign the connection string to an app setting in the web app
az webapp config appsettings set --name $webapp \
--resource-group $resourceGroup \
--settings "SQLSRV_CONNSTR=$connstring"

Clean up resources

Use the following command to remove the resource group and all resources associated with it using the az group delete command - unless you have an ongoing need for these resources. Some of these resources may take a while to create, as well as to delete.

az group delete --name $resourceGroup

Sample reference

This script uses the following commands to create a resource group, App Service app, SQL Database, and all related resources. Each command in the table links to command specific documentation.

Command Notes
az group create Creates a resource group in which all resources are stored.
az appservice plan create Creates an App Service plan.
az webapp create Creates an App Service app.
az sql server create Creates a server.
az sql db create Creates a new database.
az sql db show-connection-string Generates a connection string to a database.
az webapp config appsettings set Creates or updates an app setting for an App Service app. App settings are exposed as environment variables for your app.

Next steps

For more information on the Azure CLI, see Azure CLI documentation.

Additional App Service CLI script samples can be found in the Azure App Service documentation.