Connect a Web App to a SQL Database with the Azure CLI

Continuing my series on the Azure CLI, today I want to show how we can create a Web App and a SQL Database and connect the two together.

Create a Web App

First up, let’s create a web app. I’ll create a resource group to put it in, an app service plan with az appservice plan create (using basic hosting tier) an then create the web app itself with az webapp create.

resourceGroup="CliWebAppDemo"
location="westeurope"
appName="azcliwebappdemo"
planName="CliWebAppDemo"

# create resource group
az group create -n $resourceGroup -l $location

# create the app service plan
# allowed sku values B1, B2, B3, D1, F1, FREE, P1, P1V2, P2, P2V2, P3, P3V2, S1, S2, S3, SHARED.
az appservice plan create -n $planName -g $resourceGroup -l $location --sku B1

# create the webapp
az webapp create -n $appName -g $resourceGroup --plan $planName

Deploy From GitHub

Next, let’s set our web app up to deploy code from GitHub. We’ll use this simple ASP.NET Core project that uses EF Core to talk to a SQL database.

Web apps support many different deployment methods, and there are also multiple ways to set up Git deployment. Here, I’m going for the simple --manual-integration option, which only re-syncs when you explicitly ask it to.

We can use az webapp deployment source config, to tell our webapp where the repository is and what branch to sync from. This will cause it to download and build the code.

gitrepo="https://github.com/markheath/azure-cli-snippets"

az webapp deployment source config -n $appName -g $resourceGroup \
    --repo-url $gitrepo --branch master --manual-integration

If we’ve pushed more changes to our GitHub repository, we can trigger a re-sync with:

az webapp deployment source sync -n $appName -g $resourceGroup

Create a SQL Database

Our demo application expects to talk to a SQL database, so we need to create one. This is a two-step process. We need to create a SQL server with az sql server create, supplying a user name and password for the administrator, and then create a database with az sql db create. When we create the database, we can choose the pricing tier with the --service-objective argument.

sqlServerName="azclidemo"
sqlServerUsername="mheath"
sqlServerPassword='!SecureP@assword1'

# create the SQL server
az sql server create -n $sqlServerName -g $resourceGroup \
            -l $location -u $sqlServerUsername -p $sqlServerPassword

databaseName="SnippetsDatabase"

# create the database
az sql db create -g $resourceGroup -s $sqlServerName -n $databaseName \
          --service-objective Basic

Connect the Web App to the Database

To connect our web app to the new database we just created, we need to give the web app the connection string, and we also need to create a firewall rule to allow the web app access to the SQL server, as it is locked down by default.

To find out what IP addresses our web app is using we can use:

az webapp show -n $appName -g $resourceGroup --query "outboundIpAddresses" \
               -o tsv

What you’ll find is that this returns a comma separated list of four IP addresses. So we really ought to create a rule for each one of those. However, there is a short-cut (albeit not as secure) – we can specify an IP address of 0.0.0.0 which means, “allow any internal traffic coming from within an Azure datacenter”.

This allows us to create a firewall rule like this:

az sql server firewall-rule create -g $resourceGroup -s $sqlServerName \
     -n AllowWebApp1 --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0

There doesn’t seem to be a CLI command to ask for the database connection string, but we can derive it anyway from information we already know:

connectionString="Server=tcp:$sqlServerName.database.windows.net;Database=$databaseName;User ID=$sqlServerUsername@$sqlServerName;Password=$sqlServerPassword;Trusted_Connection=False;Encrypt=True;"

And now we can provide the connection string to our web app, giving it the name “SnippetsContext” which is what my sample application is expecting:

az webapp config connection-string set \
    -n $appName -g $resourceGroup \
    --settings "SnippetsContext=$connectionString" \
    --connection-string-type SQLAzure

Now, if you’ve followed along these instructions, you can visit the site at the /migrate endpoint to trigger it to run the initial migrations. Once that’s done, the main site will be all ready to use. You can get the base URI for the web app with the following command:

az webapp show -n $appName -g $resourceGroup --query "defaultHostName"

Summary

As you can see, it’s really very simple to not just create web apps and SQL databases with the CLI, but to configure things like deployment settings, connection strings and server firewall rules.

Previous entries in my Azure CLI series of posts…

• Manage Storage Queues with the Azure CLI
• Manage Blob Storage with the Azure CLI
• Create and Configure a VM with the Azure CLI
• Automate away your Azure Portal usage with the Azure CLI
• Using Queries with the Azure CLI
• Introducing the Azure CLI