Connect a web app to a SQL database

In this scenario you will learn how to create an Azure SQL database and an Azure web app. Then you will link the SQL database to the web app using app settings.

If needed, install the Azure PowerShell using the instruction found in the Azure PowerShell guide, and then run Login-AzureRmAccount to create a connection with Azure.

Sample script


# Generates a Random Value
$Random=(New-Guid).ToString().Substring(0,8)

# Variables
$ResourceGroup="MyResourceGroup$Random"
$AppName="webappwithSQL$Random"
$Location="West US"
$ServerName="webappwithsql$Random"
$StartIP="0.0.0.0"
$EndIP="0.0.0.0"
$Username="ServerAdmin"
$Password="<provide-a-password>"
$SqlServerPassword=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $Username,(ConvertTo-SecureString -String $Password -AsPlainText -Force)

# Create a Resource Group
New-AzureRMResourceGroup -Name $ResourceGroup -Location $Location

# Create an App Service Plan
New-AzureRMAppservicePlan -Name WebAppwithSQLPlan -ResourceGroupName $ResourceGroup -Location $Location -Tier Basic

# Create a Web App in the App Service Plan
New-AzureRMWebApp -Name $AppName -ResourceGroupName $ResourceGroup -Location $Location -AppServicePlan WebAppwithSQLPlan

# Create a SQL Database Server
New-AzureRMSQLServer -ServerName $ServerName -Location $Location -SqlAdministratorCredentials $SqlServerPassword -ResourceGroupName $ResourceGroup

# Create Firewall Rule for SQL Database Server
New-AzureRmSqlServerFirewallRule -FirewallRuleName "AllowYourIp" -StartIpAddress $StartIP -EndIPAddress $EndIP -ServerName $ServerName -ResourceGroupName $ResourceGroup

# Create SQL Database in SQL Database Server
New-AzureRMSQLDatabase -ServerName $ServerName -DatabaseName MySampleDatabase -ResourceGroupName $ResourceGroup

# Assign Connection String to Connection String 
Set-AzureRMWebApp -ConnectionStrings @{ MyConnectionString = @{ Type="SQLAzure"; Value ="Server=tcp:$ServerName.database.windows.net;Database=MySampleDatabase;User ID=$Username@$ServerName;Password=$password;Trusted_Connection=False;Encrypt=True;" } } -Name $AppName -ResourceGroupName $ResourceGroup

Clean up deployment

After the script sample has been run, the following command can be used to remove the resource group, web app, and all related resources.

Remove-AzureRmResourceGroup -Name myResourceGroup -Force

Script explanation

This script uses the following commands. Each command in the table links to command specific documentation.

Command Notes
New-AzureRmResourceGroup Creates a resource group in which all resources are stored.
New-AzureRmAppServicePlan Creates an App Service plan.
New-AzureRmWebApp Creates a web app.
New-AzureRMSQLServer Creates a SQL Database server.
New-AzureRmSqlServerFirewallRule Creates a firewall rule for a SQL Database server.
New-AzureRMSQLDatabase Creates a database or an elastic database.
Set-AzureRmWebApp Modifies a web app's configuration.

Next steps

For more information on the Azure PowerShell module, see Azure PowerShell documentation.

Additional Azure Powershell samples for Azure App Service Web Apps can be found in the Azure PowerShell samples.