AppService to Azure SQL Using Managed Identity

Written by: Steve Vandenbush
March 22, 2022

I am guessing most of you, like me, have traditionally spun up an Azure SQL database, enabled SQL Authentication, stuffed that username and password into a connection string either in Azure KeyVault or the appsettings.config/web.config .

I also assume if you have gotten to this post, you are either looking to change that approach on your own, have been told by your security team to do better or maybe some of both.

A lot of what I am going to detail below is lifted from here but with a UI and some screenshots to help.

To preface some of what I will show here, the example project is using the following:

```
dotnet 5
```
```
EFCore
```
```
Windows App Service
```
```
Azure SQL Serverless
```

I won’t be going into detail on how to setup EFCore using SQL Server as there are plenty of other examples, but I do want to focus on how to get your App Service talking to your Azure SQL Database using a managed identity.

Azure SQL Authentication Method

If you are just creating your Azure SQL Server now, make sure you select Use Azure Active Directory (Azure AD) authentication :

If you already have an Azure SQL Server and Database, go to your server and click on Settings -> Azure Active Directory :

By selecting this option (for either) you are effectively disabling the SQL Authentication for the server. You will also have to assign an AD Admin for the server, make sure to assign an admin account you have access to as you will need it shortly…

Enabling Managed Identity

This should be pretty straightforward, go to your App Service, find Settings -> Identity and click Status -> On :

Your app service now has a proper identity.

Creating the Contained User

For this next step, you will need to make sure you have the server Firewall opened up enough for you to access the database and run some SQL, but you might as well grant access for the Azure Services too:

Now that you have line of sight to the server, open the database and you can either open up the Query Editor:

Or if you rather SQL Server Management Studio :

Regardless, you need to execute the following SQL:

CREATE USER [your-app-service] FROM EXTERNAL PROVIDER

ALTER ROLE db_datareader ADD MEMBER [your-app-service];

ALTER ROLE db_datawriter ADD MEMBER [your-app-service];

ALTER ROLE db_ddladmin ADD MEMBER [your-app-service];

Where your-app-service is the actual name of your App Service in Azure (please make sure this is unique, both within App Services but also within Active Directory -> App Registrations ). You will get an error upon creation if it cannot find the actual resource by name, so you won’t have to worry about botching it up yet… Also make sure to use applicable roles for your App Service.

Alright so we have Active Directory Authentication enabled for the SQL Server, a Managed Identity for our App Service, an actual user in the database for that Identity with roles, we have the database server Firewall accepting traffic from Azure resources, all we need now is a proper connection string:

ConnectionString

Server=tcp:yourDbServer.database.windows.net;Authentication=Active Directory Default; Database=YourDatabase;

And realistically, at this point, you should be done!

Additional note, I ran into some odd errors about authentication is 'unknown type' specifically for the ConnectionString. It had seemed to be an issue with the Microsoft.Data.SqlClient that is included in the EFCore SQL Server package. Simply by adding the Microsoft.Data.SqlClient package to the solution/project, to its latest version resolved this error. You may or may not need to follow suit.

Happy Breaking!

Azure, Microsoft, SQL