Connecting an App Service to Azure SQL Database and Storage Account using Azure CLI

Effective data management is crucial for optimal application performance, and SQL databases and storage accounts play a critical role in streamlining data storage and access.

SQL databases provide a reliable and scalable method of storing and managing data in a structured manner, making it easier to search, retrieve, and analyze, while storage accounts offer a secure and scalable platform for storing and accessing large amounts of unstructured data. By combining these technologies, developers can create a robust data management strategy that meets their app's unique needs.

In this article, we will explore how to connect an App Service to an Azure SQL database and Storage Account using Azure CLI, a command-line tool that allows you to manage and interact with Azure resources using a shell or command-line interface.

Prerequisites

Before we get started, ensure that you have the following in place:

• An active Azure Subscription
• Azure CLI installed
• Basic knowledge of Azure

Connect to our Azure Account

To connect to our Azure account, we can use the Azure CLI to sign in by running the command below:

subscription="<subscriptionId>" # add subscription here
az account set -s $subscription # ...or use 'az login'

Run the below command to obtain your subscription ID if you are unaware of it

az login

Upon executing the command, a prompt will appear requesting login credentials for your Azure account. After successful authentication, your terminal should display an output similar to the one below:

[
  {
    "cloudName": "AzureCloud",
    "homeTenantId": "56d87bs9-fr5n-8zzq-qq01-419l20234j0f",
    "id": "5h12gg64-60d2-1b4h-6j7c-c810419k33v2",
    "isDefault": true,
    "managedByTenants": [],
    "name": "Visual Studio Enterprise Subscription",
    "state": "Enabled",
    "tenantId": "56d87bs9-fr5n-8zzq-qq01-419l20234j0f",
    "user": {
      "name": "[e-mail]",
      "type": "user"
    }
  }
]

The "id" value is your subscription ID.

Create a Resource Group

In Azure, all resources created are tied to a Resource Group. A Resource Group is a logical container that holds related Azure resources, such as virtual machines, storage accounts, and virtual networks.

To create a Resource Group, run the below script

export location="east us"
export resourceGroup="DemoRG"
export tags="connect-to-sql"

echo "Creating $resourceGroup in "$location"..."
az group create --name $resourceGroup --location "$location" --tag $tag

Environment variables are dynamic values that can affect the behavior of programs and scripts. They can be referenced by using their name in dollar signs. In the Azure CLI, exporting variables allows us to reference them using a dollar sign instead of giving the name directly in the command. Exported variables are available to all processes launched from the current shell session, but need to be re-exported if the shell or terminal is changed.

In the above script, we exported the environment variables (location, resourceGroup, and tags) which are used in creating the resource group. Because these variables have been exported into the system, we can reference them when needed.

You can choose a different Resource group name, a different location, and tag as well.

After running the previous script, you should get the below output

Creating DemoRG in east us...
{
  "id": "/subscriptions/5h12gg64-60d2-1b4h-6j7c-c810419k33v2/resourceGroups/DemoRG",
  "location": "eastus",
  "managedBy": null,
  "name": "DemoRG",
  "properties": {
    "provisioningState": "Succeeded"
  },
  "tags": {},
  "type": "Microsoft.Resources/resourceGroups"
}

This shows that the Resource Group was successfully created. You can still check directly in your Azure portal to be sure.

Create an App Service Plan

In Azure, an App Service Plan is a logical container for hosting Azure App Service apps. It defines the underlying virtual machine instance and resources that are required to host the apps. You might confuse this for Azure App Service itself.

Azure App Service is a Platform as a Service (PaaS) offering in Microsoft Azure that enables developers to quickly build, deploy, and manage web apps, mobile app backends, and RESTful APIs. It provides a fully managed platform for hosting and scaling web applications without the need to manage the underlying infrastructure.

An Azure App Service Plan, on the other hand, is a logical container for hosting one or more Azure App Service apps. It provides the necessary infrastructure resources, such as virtual machines, memory, and storage, to run and scale the apps. The resources allocated to an App Service Plan determine the capacity and performance of the apps hosted in that plan.

To create an App Service plan, run the below command;

export appServicePlan="demo_app_service"

echo "Creating $appServicePlan"
az appservice plan create --name $appServicePlan --resource-group $resourceGroup --location "$location"

From the above, we exported an appServicePlan variable which the command will reference to create the App Service Plan. You can check your Azure portal to see the Azure app service plan created.

Create a Web App

Recall that an App Service Plan is used to host one or more App Services while an App Service itself is used to deploy and manage web apps.

When you create a Web App from the Azure CLI, it is automatically associated with an App Service. This is because a Web App needs a platform to run on, and an App Service provides that platform. It is created within an App Service as a child resource.

An App Service Plan is needed in Azure to allocate and manage the computing resources required for hosting one or more App Services. It defines the underlying infrastructure resources needed to run and scale your Web Apps, such as CPU, memory, disk space, and network bandwidth. You must choose an App Service Plan when creating an App Service, which allows you to scale up or down the resources allocated to your App Service based on demand. An App Service Plan ensures that your Web Apps have the necessary resources to operate efficiently and can easily scale resources up or down to meet changing demands.

To create a Web App, run the below command:

export webapp="FavourWebApp"

echo "Creating $webapp"
az webapp create --name $webapp --plan $appServicePlan --resource-group $resourceGroup

Again, we exported a variable webapp which the command referenced in creating the Web App. As you can see, previous exported variable didn’t need to be referenced again since it was done previously. The web app is then created under the same Resource Group and location as the App Service Plan.

NB: If you encounter an error message similar to the one below, it indicates that another user has already created or deployed a web app with that name. When you create a Web App, it will have a subdomain of azurewebsites.net. If the name you have chosen is already being used, you will need to select a different name to proceed. For instance, when I used the name DemoApp, it returned an error because the name was already in use.

Creating DemoApp
Webapp 'DemoApp' already exists. The command will use the existing app's settings.
Unable to retrieve details of the existing app 'DemoApp'. Please check that the app is a part of the current subscription

After successfully deploying the web app you can check it on the Azure Portal. It can be found under App Services.

You can also load the url on your browser to see it.

Create an SQL Server

An SQL Server is a software program that provides a platform for creating, managing, and accessing databases using a structured query language (SQL). It is a necessary component for managing databases because it provides the infrastructure to store, manage, and secure data. A database must be attached to a SQL Server in order to access and manage the data it contains. Without a SQL Server, a database cannot be accessed or managed.

Before we create the SQL database, we need to create a SQL Server that the database will run on.

To do this, run the below command:

export server="favourdemo"
export login="favour"
export password="yourpassword@123"

echo "Creating $server"
az sql server create --name $server --resource-group $resourceGroup --location "$location" --admin-user $login --admin-password $password

In the above configuration, you can choose a different server name to be exported, a login name, and a password of your choice.

Similar to how a subdomain is created for a Web App, creating an SQL Server on Azure results in a subdomain of database.windows.net. If the chosen name for the SQL Server is already taken, an error message will be displayed.

The rules for choosing a password are

• Your password must be at least 8 characters in length.
• Your password must be no more than 128 characters in length.
• Your password must contain characters from three of the following categories – English uppercase letters, English lowercase letters, numbers (0-9), and non-alphanumeric characters (!, $, #, %, etc.).
• Your password cannot contain all or part of the login name. Part of a login name is defined as three or more consecutive alphanumeric characters.

NB: If you do not follow the password rules, you will get an error.

You can check your Azure portal to see the created SQL Server.

Configure Access to the SQL Server

After creating the SQL Server, we need to configure a firewall for access to control access to the server. This is important for security reasons as it allows only authorized users or applications to access the SQL Server. Without proper firewall configuration, the server may be vulnerable to unauthorized access and potential security breaches.

To do this, run the below command:

export startIp="0.0.0.0"
export endIp="0.0.0.0"

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

From the above configuration, we are exporting a start IP and an End IP which refer to the range of IP addresses that are allowed to connect to the server.

The start IP is the first IP address in the range of allowed addresses, while the end IP is the last IP address in the range. Any IP address outside this range will be blocked from accessing the SQL Server.

NB: Because both startIP and endIP are set to “0.0.0.0”, it means we are opening the firewall to allow access from anywhere. This is strictly for demo purposes. It is unsafe for production environments so always ensure to restrict access to your server.

Create an Azure SQL Database

An SQL database is a type of relational database management system (RDBMS) that stores data in a structured format using tables consisting of rows and columns. It uses SQL (Structured Query Language) to manage the data and allows for easy retrieval, insertion, and modification of data. When an App Service app is connected to a database, it typically stores structured data that can be queried and manipulated using SQL or other database management tools. Examples of this type of data include user information, product catalogs, and transaction logs.

To create an Azure SQL database in Azure, run the below command:

export database="demodatabase"

echo "Creating $database"
az sql db create --server $server --resource-group $resourceGroup --name $database --service-objective S0

The above configuration export the database variable (name), and creates the database using the variable on the SQL Server we previously created under the same Resource Group.

--service-objective S0 is a parameter used in the Azure CLI command to set the performance level of the Azure SQL Database when it is created or updated. In this case, S0 is the service objective, which represents the Standard service tier of Azure SQL Database.

Get Connection String for the Database

A connection string for a database is a text string that contains the information required to establish a connection to a database server. It typically includes the name and location of the database, the name of the server hosting the database, and authentication information such as a username and password.

The connection string is required by applications or programs to establish a connection to the database server.

To get the connection string for the just created database, run the below command:

connstring=$(az sql db show-connection-string --name $database --server $server --client [ado.net](http://ado.net) --output tsv

-client ado.net: This specifies the client driver to use for the connection string. In this case, we are using the .NET client driver.

-output tsv: This specifies that the output of the command should be in TSV (tab-separated values) format, which is a type of text format that is easy to parse programmatically.

connstring=$(...): This assigns the output of the command to a shell variable called connstring. The $() syntax allows you to execute a command and capture its output.

The command az sql db show-connection-string is used to retrieve the connection string for the Azure SQL database that was just created. The output of this command is a string that contains information such as the server name, database name, login credentials, and other settings required to connect to the database as seen below.

Server=tcp:favourdemo.database.windows.net,1433;Initial Catalog=demodatabase;Persist Security Info=False;User ID=<username>;Password=<password>;MultipleActiveResultSets=False;Encrypt=true;TrustServerCertificate=False;Connection Timeout=30;

By assigning the output of the command to a variable named "connstring" using the syntax connstring=$(az sql db show-connection-string --name $database --server $server --client [ado.net](http://ado.net) --output tsv), the connection string can be referenced later in the script.

Add your Credentials to Connstring

After getting the connection string using the command az sql db show-connection-string, the resulting string will contain placeholders for the user ID and password, indicated by <username> and <password> respectively. We need to replace these placeholders with our actual login credentials to be able to connect to the database. Therefore, we need to add our credentials to the connection string to establish a successful connection to the Azure SQL Database.

To do this, run the below;

connstring=${connstring//<username>/$login}
connstring=${connstring//<password>/$password}

Assign the Connection String to an App Setting in the Web App

Application settings are configuration values that are stored within the App Service, and can be accessed by the application code at runtime. These settings can include things like database connection strings, API keys, and other configuration values specific to your application.

Assigning the connection string to an app setting in the web app makes it easy to manage and update the connection string without having to modify the code of the web app. By storing the connection string as an app setting, the web app can access it at runtime and establish a connection to the database without exposing sensitive information like usernames and passwords in the code. This also makes it easier to switch between different databases or servers, as the connection string can be updated in one central location (the app settings) rather than having to update it in multiple places in the code.

To do this, run the below command:

az webapp config appsettings set --name $webapp --resource-group $resourceGroup --settings "SQLSRV_CONNSTR=$connstring"

Create a storage account

A storage account in Azure is a secure and scalable cloud-based storage solution that allows users to store and access various types of data such as files, blobs, queues, tables, and disks, among others. Connecting an App Service app to a storage account allows the app to access and store data in the storage account, which can be used to store unstructured data such as files, images, videos, and other data that the app may require.

To create a storage account, run the below command;

export storage="favourdemostore"

echo "Creating $storage"
az storage account create --name $storage --resource-group $resourceGroup --location "$location" --sku Standard_LRS

--sku Standard_LRS is a parameter used in the Azure CLI command to set the pricing tier and replication scheme of the Azure Storage account being created. In this case, Standard_LRS represents the Standard pricing tier and the Locally Redundant Storage (LRS) replication scheme.

NB: The storage account name must be between 3 and 24 characters in length and use numbers and lower-case letters only.

Retrieve the storage account connection string

The connection string here is a string that contains all the necessary information to connect to a storage account. This includes details like the storage account name, access key, and endpoint. The connection string allows you to access and manage the data stored in the storage account.

To connect an app service app to a storage account in Azure, we need to obtain the storage account connection string. This connection string is crucial because it enables the app to access and manipulate the data stored in the storage account, such as uploading and downloading files, accessing and modifying blobs, and more. Without the connection string, the app will not be able to communicate with the storage account and perform these actions.

To retrieve the connection string, run the below command:

connstr=$(az storage account show-connection-string --name $storage --resource-group $resourceGroup --query connectionString --output tsv)

Assign the connection string to an App setting in the Web app

Now, we will set the connection string as a value for an App Setting in the configuration of a Web app deployed on Azure. This allows the Web app to access the storage account, as it can now retrieve the connection string from its configuration settings.

Assigning the connection string to an App Setting ensures that the connection string is securely stored, and not exposed in the application code. It also provides an easy way to update the connection string if needed, as it can be modified in the Web app configuration without having to redeploy the application code.

To do this, run the below command:

az webapp config appsettings set --name $webapp --resource-group $resourceGroup --settings "STORAGE_CONNSTR=$connstr"

After carrying out the above processes, check the configuration settings under the Web App in App Services, you should see the applications settings (connection strings) we have configured.

And that is how you connect an App Service to an Azure SQL Database and a Storage Account.

Resource Clean up

It is important to clean up resources that were created to avoid unnecessary charges after tests. Fortunately, all resources created during this process are tied to the same Resource Group, so they can be terminated with a single command. This makes cleanup quick and easy.

To perform the clean up, run the below command:

az group delete --name $resourceGroup

Conclusion

This article provided a comprehensive guide on how to connect an App Service app to both an SQL Database and a Storage Account. SQL Databases are essential for storing structured data such as user information, while Storage Accounts are used for storing unstructured data like media files or logs.

By following the steps outlined in this article, you can easily establish a connection between your App Service app and these services, allowing your app to access and manipulate data in a secure and efficient manner.