Azure AD Managed Identity: Connecting Azure Web App and Slots with Azure SQL DB (without credentials)
Tags: azure, azuresql, managedidentity, azurewebapp
Published: 2020-06-17 13:55:46

<h1><a name="Introduction"></a>
Introduction</h1>
<span style="font-family: 'Segoe UI', SegoeUI, 'Helvetica Neue', Helvetica, Arial, sans-serif;">Managed Identity is a great way for connecting services in Azure without having to provide credentials like username, password or even clientid or client secrets. Please note that not <a href="https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/services-support-managed-identities" target="_blank" style="font-family: 'Segoe UI', SegoeUI, 'Helvetica Neue', Helvetica, Arial, sans-serif;"> all azure services support managed identity.</a><span style="font-family: 'Segoe UI', SegoeUI, 'Helvetica Neue', Helvetica, Arial, sans-serif;">&nbsp; There are many great articles and blogs which discuss about in depth of managed identity and their types.You can see some of them in the See Also section.In this article we are going to see 2 main popular Azure resources to connect each other without providing any credentials in the code, they are </span><a href="https://docs.microsoft.com/en-in/azure/app-service/overview" target="_blank" style="font-family: 'Segoe UI', SegoeUI, 'Helvetica Neue', Helvetica, Arial, sans-serif;">azure app service</a><span style="font-family: 'Segoe UI', SegoeUI, 'Helvetica Neue', Helvetica, Arial, sans-serif;">&nbsp;(front-end) and </span><a href="https://azure.microsoft.com/en-in/services/sql-database/" target="_blank" style="font-family: 'Segoe UI', SegoeUI, 'Helvetica Neue', Helvetica, Arial, sans-serif;">azure sql database</a><span style="font-family: 'Segoe UI', SegoeUI, 'Helvetica Neue', Helvetica, Arial, sans-serif;">&nbsp;(back-end) by using System Managed Identity. 
<br>
<h1><a name="Using_typical_Connection_String_way"></a>Using typical Connection String way</h1>
<span style="font-family: 'Segoe UI', SegoeUI, 'Helvetica Neue', Helvetica, Arial, sans-serif;">Usually we use the connection string to connect an azure sql database from any front-end. For instance lets say we are developing a dotnet application and want to connect to a back end (azure sql db) we will simply use the connection string like in the <strong><em>web.config</em></strong> below <em>(which may be different for some other applications)</em><br>

``` xml
<connectionStrings>
<add name="DbConnection" connectionString="server=<sqlinstance>.database.windows.net;database=<dbname>;Uid=localadmin;Pwd={your_password_here};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;" providerName="System.Data.SqlClient" />
</connectionStrings>
```

From the <strong>C# code</strong> we will consume the connection string from the web.config to establish the connectivity from our code to backend like <br>


``` C#
using (var connection = new SqlConnection(connectionstring))
{
connection.Open();
 
//Executing some sql code
 
}
```

<br>
<h2><a name="Azure_Key_Vault_for_Connecting_String"></a>Azure Key Vault for Connection String</h2>
It is always good to store this type of connection string in a secure place like <a href="https://social.technet.microsoft.com/wiki/contents/articles/51871.net-core-2-managing-secrets-in-web-apps.aspx" target="_blank">
azure key vault secrets</a>. However we still need to store the client id and client secret&nbsp;&nbsp;in a web.config if we aren't using the Managed Identity.See again storing a secret in a web.config, which is more like a chicken and egg problem &nbsp;🐔🥚🐔<br>
Here we need more sophisticated solution to solve this, which is the Managed Identity.<br>
<h1><a name="Using_System_Managed_Identity_way"></a>Using System Managed Identity way</h1>
<br>
<h2><a name="Step_1_Enabling_System_Managed_Identity_in_Web_App"></a>Step 1: Enabling System Managed Identity in Web App</h2>
First we need to enable the System Managed Identity in our web app.

<a href="https://social.technet.microsoft.com/wiki/cfs-file.ashx/__key/communityserver-wikis-components-files/00-00-00-00-05/4405.0.JPG"><img alt="" src="https://social.technet.microsoft.com/wiki/resized-image.ashx/__size/550x0/__key/communityserver-wikis-components-files/00-00-00-00-05/4405.0.JPG" style="border-width: 0px; border-style: solid;"></a><br>
<p>If you are using any slots you should also enable the same options in the slots as well&nbsp;<br>
<a href="https://social.technet.microsoft.com/wiki/cfs-file.ashx/__key/communityserver-wikis-components-files/00-00-00-00-05/365844751.1.JPG"><img alt="" src="https://social.technet.microsoft.com/wiki/resized-image.ashx/__size/550x0/__key/communityserver-wikis-components-files/00-00-00-00-05/365844751.1.JPG" style="border-width: 0px; border-style: solid;"></a></p>
<br>
<h2 style="line-height: 25px;"><a name="Step_2_Creating_Managed_Identity_User_in_Azure_SQL"></a>Step 2: Creating Managed Identity User in Azure SQL</h2>
<h2><a name="After_we_enabled_the_System_Managed_Identity"></a></h2>
<p>After we enabled the System Managed Identity in Azure App, we have to create a Managed Identity User in Azure sql db. To do so we must enable the Azure Active Directory Admin, then login to the database using the Active Directory account from either SSMS
or Azure Data Studio. <br>
<a href="https://social.technet.microsoft.com/wiki/cfs-file.ashx/__key/communityserver-wikis-components-files/00-00-00-00-05/404335.2.JPG"><img alt="" src="https://social.technet.microsoft.com/wiki/resized-image.ashx/__size/550x0/__key/communityserver-wikis-components-files/00-00-00-00-05/404335.2.JPG" style="border-width: 0px; border-style: solid;"></a><br>
</p>
<p>We have to run the below query in the corresponding database.<br>
</p>

```sql
CREATE USER [<App Service Name>] FROM EXTERNAL PROVIDER 
 
CREATE USER [<App Service Name>/slots/<slotname>] FROM EXTERNAL PROVIDER -- For apps in the slots
 
ALTER ROLE db_datareader ADD MEMBER [<App Service Name>] -- gives permission for normal app read to database
ALTER ROLE db_datawriter ADD MEMBER [<App Service Name>] -- gives permission for normal app write to database

ALTER ROLE db_datareader ADD MEMBER [<App Service Name>/slots/<slotname>] -- gives permission for slot app  read to database
ALTER ROLE db_datawriter ADD MEMBER [<App Service Name>/slots/<slotname>] -- gives permission for slot app  write to database
```

By doing the above we are creating a user for our system managed identity of our app in the backend and providing required permissions like read/write a database.

<a href="https://social.technet.microsoft.com/wiki/cfs-file.ashx/__key/communityserver-wikis-components-files/00-00-00-00-05/2112.3.JPG"><img alt="" src="https://social.technet.microsoft.com/wiki/resized-image.ashx/__size/550x0/__key/communityserver-wikis-components-files/00-00-00-00-05/2112.3.JPG" style="border-width: 0px; border-style: solid;"></a><br>
<h2 style="line-height: 25px;"><a name="Step_3_Remove_the_credentials_from_the_Connection_String"></a>Step 3: Remove the credentials from the Connection String</h2>
Finally we have to remove the credentials details like userid , password from the connection string ,like below<br>

```xml
<connectionStrings>
<add name="DbConnection" connectionString="server=<sqlinstance>.database.windows.net;database=<dbname>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;" providerName="System.Data.SqlClient" />
</connectionStrings>
```

<br>
<h2><a name="Step_4_1-Line_Magic_Code"></a>Step 4: 1-Line Magic Code</h2>
Now we just need to add a single line of code (magic) in our application before <em> opening the connection </em>like below

```C#
connection.AccessToken = await (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/");
```
Sometime during the local testing if you have more than one subscription you will get error at the above line. To resolve that please use the optional parameters called tenant id for <em>GetAccessTokenAsync </em>method

```c#
connection.AccessToken = await (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/", <tenant-id>);
```

In order to use the above code you also need to import a package called&nbsp;<strong>Microsoft.Azure.Services.AppAuthentication.
</strong> You can install it from nuget&nbsp; like below

```powershell
Install-Package Microsoft.Azure.Services.AppAuthentication -Version 1.5.0
```

So finally our code will look like below

```C#
using (var connection = new SqlConnection(connectionstring))
{
connection.AccessToken = await (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/");
connection.Open();
  
//Executing some sql code
  
}
```

<h2><a name="Step_5_Testing_it_Locally"></a>Step 5: Testing it Locally</h2>
In order to test this from local machine we have to use <a href="https://docs.microsoft.com/en-us/cli/azure/install-azure-cli?view=azure-cli-latest" target="_blank">
Azure CLI 2.0 or above</a>. So install the Az Cli then run the command&nbsp;

```powershell
az login
```

It will prompt you for the credentials. Once you successfully logged in you will see your subscription details like in the below pic&nbsp;<br>
<a href="https://social.technet.microsoft.com/wiki/cfs-file.ashx/__key/communityserver-wikis-components-files/00-00-00-00-05/4861.azlogin.jpg"><img alt="" src="https://social.technet.microsoft.com/wiki/resized-image.ashx/__size/550x0/__key/communityserver-wikis-components-files/00-00-00-00-05/4861.azlogin.jpg" style="border-width: 0px; border-style: solid;"></a> Now you can run your application in local.

<em>(Note: If you have more than 1 tenant access you have to explicitly pass the tenantid in the&nbsp;<span style="background-color: #ffffff; font-family: monospace; color: #000000;">GetAccessTokenAsync</span> method)</em>

After the successful run you can deploy the application in Azure. <br>
<h1><a name="Conclusion"></a>Conclusion</h1>
Azure Managed Identity is going to remove the way of storing credentials in code even in azure key vault. In this article we saw only 2 services. But there are more and more services are coming along the way. Using this great feature we can do all the things inside Azure very safely and not leaking any credentials to others.&nbsp;<br>
<br>

<h1>See Also</h1>
- https://joonasw.net/view/azure-service-bus-managed-identities
- https://thomasthornton.cloud/2019/01/07/azure-managed-identities/
- https://github.com/Azure-Samples/app-service-msi-entityframework-dotnet