Configuring Managed Identities for Azure App Service and Azure SQL Database.

As developers, we always use credentials in our code. When you need to display Azure SQL database data in an ASP.NET application You have to add SQL connection string in the web config file. Using that SQL connection string ASP.NET application can fetch data from the Azure SQL Database. In that SQL connection string contains User ID and password. If an unauthorized person gets to access the web config file, he or she can get User Id and password and he or she will be able to access your data in Azure SQL Database. Therefore you should keep credential free code.

Using the Azure Key Vault.

One of the services you could use as a developer to mitigate this issue was to use Azure Key Vault. Using this service, you could create a secret in Azure Key Vault and store your Azure SQL Database connection string in it. At runtime, your ASP.NET application could read the Key Vault, obtain the user ID and password, and send them over to Azure SQL Database. And this is all working great, but there is a small question. How would your ASP.NET application authenticate to Azure Key Vault? To be able to authenticate to Azure Key Vault, you have to present a valid Azure Active Directory client ID and secret. You would create a new application in Azure Active Directory, obtain the client ID and secret for this application, and store them in the ASP.NET application configuration file. Azure Key Vault is a much safer approach comparing to storing plain user ID and password sin the configuration file. However, a malicious user could still access your configuration file and grab the client ID and secret of the application. So keeping service credentials in application configuration is not the most secure way because credentials could get checked into source control, or the configuration file can get compromised. We can achieve credential free code using Managed Identities for Azure Services.

What is the Managed Identities for Azure Services?

Managed identities for Azure services provides Azure services with an automatically managed identity. You can use this identity to authenticate to any service that supports Azure AD authentication without any credentials in your code.

The managed identities is a service of Azure Active Directory. It works with services that support Azure AD authentication. It is very important to note that managed identities provides authentication, so basically it tells other Azure services who your application is. It doesn't provide authorization. You still need to configure your target service and grant needed permissions to this identity.

There are two types of managed identities, the system assigned and user assigned. So system-assigned managed identities are enabled directly on an Azure service instance. For example, you log in to the Azure portal, navigate to your App Service dashboard page, and then enable the service identity for your app service. On the other hand, user-assigned identities are created as standalone Azure resources and then assigned to your services. You only can have one system-assigned identity per Azure service instance. For example, you can only have one system- assigned identity for your function app. On the other hand, user-assigned identities can be assigned to one or more Azure service instances. Each time you clean up an Azure service instance, the created system-assigned identity for that service will be deleted too. However, because user-assigned identities are standalone entities, their lifecycle is separate from the lifecycle of Azure services to which they are assigned. And as you will see later, system-assigned identities are widely supported by Azure resources. On the other hand, user-assigned identities might be in preview for some resources. Check the supported Azure resources.

Steps to configure  Managed Identities for Azure App Service and Azure SQL Database

Step 1

I'm going to guide you step by step to configure Managed Identities for Azure App Service and Azure SQL Database.

I have created an ASP.NET Core Web API called 'MIDemoAPI' to get the list of mobile phones. For that created an Azure SQL Database called 'db-MIDemo'. There is the mobile phone table inside the 'db-MIDemo' database.

Step 2

Next, you need to create a new app service in the Azure Portal. I have created a new app service called 'app-midemo-api'.

Then go to the Identity section in the app service and enable the system assigned Managed Identities. after that copy the object id value.

Step 3

Open the cloud shell and type following bash command with your azure resource group, SQL server name, and previously copied Object Id.

Let's go ahead and run that. Here we go, so I have successfully created an Azure SQL Database user, which is linked to my system-assigned identity. Let's have a closer look at the command we executed. So the command name is az sql server ad-admin. You need to specify the resource group, which is rg-MIDemo, the server name will be the name of my Azure SQL Database. the display name is the name of the user I'm going to create, and the object-id is the object ID of the created system- assigned identity. 

To confirm that my user is created, I'm going to click on SQL databases, click on my database, click on the server, and click on Active Directory admin. As you can see, a new user called admin_user is created for me. So now everything is set on the Azure side

Step 4

Add your database connection string without User Id and User Password to the appsettings.json file in the ASP.NET Core Web API.

Here I have created a basic controller for the demonstration purpose to access database data. Before implementing the code You need to install the following NuGet package.

PM > Install-Package Microsoft.Azure.Services.AppAuthentication

Using this NuGet package, we are getting an access token for Azure SQL Database and then assign the access token to the SQL connection object. And this is all I need to do. Let's make sure we get a clean build. So now let's go ahead and publish this application to the Azure.

Step 5

Let's open the browser and call our API. 

as you can see, We could successfully connect to the database, without any user ID or password in my code.