Azure SQL Authentication using Managed Identity

Have you ever felt a need of getting rid of UserID and Password in Connection String? We know the fact that Security is very imperative and we need to be compliant with existing security standards in our product. Well! I found an alternative making sure we do not have to compromise on any security standards! The solution is using Managed Identity in Azure Resources.

Lets talk a little more about it!

The Scenario:

The app service that is hosted on Azure portal pulls the connection string to connect to the database from the configuration and that connection string contains secured information like User Id and Password and we wanted to get rid of it.

The Solution:

Use of managed identity, you have to add a managed identity to your app service and then give the required permission to the Azure SQL server.

To do this you have to follow the below steps:

1> Login to Azure Portal.

2> Go to the App Service that on which the site is deployed.

3> Click on Identity from the left menu.

4> Toggle the status to “On”.

5> Click Save.

6> Once saved, the object ID will be created

7> Login to Azure SQL Server using SSMS or from the Azure Portal.

8> Execute below queries to set allow the managed identity access on the Azure SQL database.

CREATE USER [{app_service_name}] FROM EXTERNAL PROVIDER;
GO
ALTER ROLE db_datareader ADD MEMBER [{app_service_name}]
ALTER ROLE db_datawriter ADD MEMBER [{app_service_name}]
ALTER ROLE db_owner ADD MEMBER [{app_service_name}]
GO

NOTE: The SSMS login user should have permission to execute above queries on database. (Set your user AAD admin on SQL server)

9> Below is a sample code where the AccessToken property of the SqlConnection is the Azure AD token that you must update in the code base.

using (SqlConnection con = new SqlConnection(CS))
{
var credential = new Azure.Identity.DefaultAzureCredential();
var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { “https://database.windows.net/.default" }));
con.AccessToken = token.Token;
SqlCommand cmd = new SqlCommand(“SELECT * FROM Persons”, con);
cmd.CommandType = CommandType.Text;
con.Open();
}

10> Deploy the updated code to app service and remove the User Id and Password and you are good to Go with Managed Identity solution.

So, you think we are done!? Nope not yet, now is the time we hear the chant as a developer — — “Debug Debug Debug .. “! Thats right my friends we got to do what we got to do! We got to debug and check for unknown issues until we traverse through each line and get to the source of the problem!

I realized that even after the Code is uploaded to the app service we still face few issues related to connection using managed identity. Let’s look at that below.

Debugging locally using Azure Managed Service Identity:

Follow below steps to DEBUG the token authentication on your local development.

1. Sign into the Azure portal and navigate to App registrations.
2. Click on the “New registration” button.
3. Provide a name and select the account types that can access the application.
4. In the “Redirect URI” section, choose “Web” and enter a dummy URL such as “https://localhost".
5. Click on the “Register” button to create the application.
6. Navigate to the Azure SQL Server instance.
7. In the left menu, select “Active Directory admin” and then click on the “Set admin” button.
8. Choose “Azure AD admin” and select the registered application created earlier.
9. Click on the “Save” button to set the Azure AD admin for the server.
10. In the left menu, select “Networking”.
11. Under “Firewall rules”, add a rule to allow access from the IP address of the on-premises Windows Service.
12. In the code that connects to the Azure SQL Server, add the following code to authenticate with Azure AD and obtain an access token:

So yes this is how I got rid of username and password in the connection string on Azure albeit using the best standards of security as recommended.

Waiting to hear from you! We can connect on Discussion Forums and learn more!