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
sqlServerName="azclidemo" sqlServerUsername="mheath" sqlServerPassword='[email protected]' # 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:
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"
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…
Hey Mark I got here from google and I think this is a great article / resource.Alex Drenea
I know it's old and it was correct at the time but in case other people get here via google, I thought I'd leave a comment re: Getting connection string from SQL. There is now an az cli command that allows you to get the connection string cormat for your database:
You could use something like this in the script:
$SqlConnectionString = (az sql db show-connection-string `
--client ado.net `
--auth-type SQLPassword `
$SqlConnectionString = $SqlConnectionString.replace("<password>",$SQLAdminLoginPassword).replace("<username>",$SQLAdminLogin)
thanks Alex, that's a really helpful tip. Might see if I can get round to updating the post to include itMark Heath