Connecting Azure SQL-Database and App Service using system-assigned identity

Emil Pettersson — Fri, 31 May 2024 08:44:01 +0000

I had built an application using an Azure SQL-Database for storage and I wanted to upload it to Azure as an App Service to run it in the cloud. I had assumed it was going to be like connecting an App Service to BlobStorage or TableStorage in Azure, but it turns out it is a bit different for a SQL-Database. Primarily how the system-assigned identity is used to authenticate. After figuring out how to get it to work, I decided to write the short guide I myself would have wanted to find when I started googling on why I couldn’t connect to my database.

Creating the database

To create a database we will first have to create a SQL-Server resource to host the database. When creating we will use the Authentication Method “Use Microsoft Entra-only authentication”, and set ourselves as Admin for the server.

To allow other azure resources to access our new server we have to go to Security > Networking. There we select to give public network access to “Selected networks”.

We then add our own IP Address to the Firewall Rules since we will want to test connecting to our SQL-Server locally. It is also needed to log in to our SQL-database in Azure Portal later.

In the special case that your App Service and SQL-Server will be in different Azure Subscriptions. you will have to check “Allow Azure services and resources to access this server” under Exceptions. Note that this is a big risk since it opens up your SQL-Server to EVERY Azure service and resource, even those belonging to other subscriptions and other Azure users, customers and organizations. To be secure there are solutions using virtual networks and configuring Private Access. But this is not included in the scope of this tutorial, so we will accept the shortcut of allowing this exception. This can also be a tool for troubleshooting if you suspect you have problems with connecting to the SQL-Server.

Next step is to create the SQL Database, so go to Overview and select Create Database. In the tutorial we assume the database is only for learning purposes so we want to keep costs as low as possible. So for Compute + Storage we select “Configure database” and slide the vCores and Data max Size to their lowest settings.

Then for Backup storage redundancy we select “Locally-redundant backup storage”, the weakest kind.

When the resource has been created, go to Query editor (preview) and log in with Microsoft Entra authentication. If it does not work make sure you have whitelisted your own IP.

We then create a new table by running:



CREATE TABLE animals (id INT PRIMARY KEY, name VARCHAR(100));

This creates a very simple table called “animals” where we can list different kinds of animals. Add a few animals:



INSERT INTO [dbo].[animals] (id, name) VALUES (1, 'Lion');
INSERT INTO [dbo].[animals] (id, name) VALUES (2, 'Fox');
INSERT INTO [dbo].[animals] (id, name) VALUES (3, 'Cat');

You can then see your animals by running:



SELECT * FROM [dbo].[animals]

And you can count them by running:



SELECT COUNT(*) FROM [dbo].[animals]

Creating the code

We will now build a small C# program that connects to the SQL Database and runs the SQL-command for counting the animals. Create a new ASP.NET Core Empty project in Visual Studio.

This gives you a project with only a Program.cs with minimal amount of code. We will be able to write our whole program here. First thing to do is to install the NuGet Microsoft.Data.SqlClient;

We then create a minimal api to connect to our SQL-database and execute the command to count our animals, and delete the auto-generated code we won’t need. This leaves us with a Program.cs like:

Then we go to Overview in your database-resource and select “See connection strings” under Connect to application. Take the connection string for “ADO.NET (Microsoft Entra passwordless authentication)” and put it into your appsettings of the newly created project.

For me, where both SQL-Server and SQL-database are named “charlie-foxtrot-sql” it will look like:

(Note1: Treat your Connection Strings as secrets, the resources my connection string are referencing will have been deleted when this tutorial is published)
(Note2: In the connection string you can see that we set authentication to be “Active Directory Default”. Entra ID used to be called Active Directory. This setting means that we can authenticate with our account assigned in visual studio when we run locally, and with managed identities given certain roles when we run it as an App Service)

Then run your program and make a call to the /test-db endpoint. You can do this using swagger, your favorite testing tool (e.g Postman or Insomnia) or your web browser.

If you get an error “Globalization Invariant Mode is not supported” you’ll have to unload your project and remove

from your project file (or set it to false). Then load the project again.

If you get trouble with authentication, make sure you are using the same account for Azure Service Authentication in Visual Studio as you used to create the SQL-Server and database in Azure, and that the account has the proper IAM-role to access them. You can check this under Access Control (IAM) for your SQL-Server.

You should then get a response that corresponds to the value you get by running the count-command directly in the Query Editor in Azure Portal.

Now we will create an App Service to run this code in Azure.

Creating the App Service

Create a repository for the code in Github (or Azure DevOps or Bitbucket), and push your code, except for your appsettings, to it. We will deploy the code to our App Service from there.

In Azure Portal, go to App Services and press Create > Web App and create your App Service. Then we have to connect our App Service to Github so that it knows which code to deploy. We get two options for Github Action workflow to authenticate to our App Service when deploying; the default “User-assigned identity” and “Basic authentication”. If you have sufficient permissions to use “User-assigned identity” that is recommended. You need to be able to assign role-based access to the identity. If you lack these permissions you can instead use “Basic authentication”.

If you use “Basic authentication” you first have to go to Settings > Configuration in your App Service and put SCM Basic Auth Publishing Credentials to On and press Save. If you use “User-assigned identity” you don’t have to do this.

Then go to Deployment > Deployment Center and choose your Source for the code. Choose your way of authentication and press Save.

Make sure that it builds. Then go to Settings > Environment variables and add your connection string.

Make sure you click Apply once after adding this applications setting, then once again to confirm you are done editing your application settings in general.

Note the naming of the Application Setting and compare it to how it is in your local appsettings.json. This is how Azure deals with hierarchy in JSON;



"Level1": {
    "Level2": {
        "Level3:": "Value",
    }
},

would in the Azure App Service application settings become a setting with the name
"Level1:Level2:Level3" and the value “Value”.

Then go to Settings > Identity and set Status to On for System Assigned identity. Remember to save.

For most resources in Azure you would then give a role to this managed identity that let the App Service access it. This is called Role-Based Access Control (RBAC). But for Azure SQL Database we have to do it in a different way; We want to add our App Service as a user to the database and then give the user read and write permissions. So go to the Query Editor in your database and run



CREATE USER [<appservice>] FROM EXTERNAL PROVIDER;

where is the name of your App Service. Then run both



ALTER ROLE db_datareader ADD MEMBER [<appservice>];
ALTER ROLE db_datawriter ADD MEMBER [<appservice>];

to give the new role proper permissions.
(Note: In our code we are only reading from the database, but the common case is to want both read and write permissions.)

Now you can go to your App Service Overview where you will see which “Default Domain” your app has

go to the test-endpoint of this domain. My App Service is called charlie-foxtrot-sql so for me it is https://charlie-foxtrot-sql.azurewebsites.net/test-db. This should now show you the number of animals in your database.

DEV Community: Emil Pettersson

Connecting Azure SQL-Database and App Service using system-assigned identity

Creating the database

Creating the code

Creating the App Service