DEV Community

Sanjeevi Subramani
Sanjeevi Subramani

Posted on • Originally published at lkgforit.com on

Managed Identity in Dotnet Core API deployed in Azure App Service to Azure SQL Server

image.png

1. Grant database access to Azure AD user

First, enable Azure Active Directory authentication to SQL Database by assigning an Azure AD user as the admin of the server. This user is different from the Microsoft account you used to sign up for your Azure subscription. It must be a user that you created, imported, synced, or invited into Azure AD. For more information on allowed Azure AD users, see Azure AD features and limitations in SQL Database.

If your Azure AD tenant doesn't have a user yet, create one by following the steps at Add or delete users using Azure Active Directory.

Find the object ID of the Azure AD user using the az ad user list and replace . The result is saved to a variable.

azureaduser=$(az ad user list --filter "userPrincipalName eq '<user-principal-name>'" --query [].objectId --output tsv)

Enter fullscreen mode Exit fullscreen mode

Add this Azure AD user as an Active Directory admin using az sql server ad-admin create command in the Cloud Shell. In the following command, replace with the server name (without the .database.windows.net suffix).

az sql server ad-admin create --resource-group myResourceGroup --server-name <server-name> --display-name ADMIN --object-id $azureaduser

Enter fullscreen mode Exit fullscreen mode

2. Set up your dev environment

Visual Studio for Windows is integrated with Azure AD authentication. To enable development and debugging in Visual Studio, add your Azure AD user in Visual Studio by selecting File > Account Settings from the menu, and select Sign in or Add.

To set the Azure AD user for Azure service authentication, select Tools > Options from the menu, then select Azure Service Authentication > Account Selection. Select the Azure AD user you added and select OK.

Older way of using System.Data.SqlClient before new version of Microsoft.Data.SqlClient version came to picture

using Azure.Core;
using Azure.Identity;
using Microsoft.Azure.Services.AppAuthentication;

namespace DummyApi
{
    public class AzureSqlAuthTokenService : IDBAuthTokenService
    { 
        // See https://docs.microsoft.com/azure/active-directory/managed-identities-azure-resources/services-support-managed-identities#azure-sql
        private static readonly string[] _azureSqlScopes = new[]
        {
        "https://database.windows.net//.default"
        };

        private static readonly TokenCredential _credential = new ChainedTokenCredential(
            new ManagedIdentityCredential(),
            new EnvironmentCredential());

        public string GetToken()
        {
            AzureServiceTokenProvider provider = new AzureServiceTokenProvider(serviceProviderConnection);
            var token = provider.GetAccessTokenAsync("https://database.windows.net/").Result;
            return token;
        }
    }
}

Enter fullscreen mode Exit fullscreen mode

In Above code "serviceProviderConnection" will have following value when debugging in local Visual Studio:

RunAs=Developer; DeveloperTool=VisualStudio

Enter fullscreen mode Exit fullscreen mode

It will be updated to following when deployed to App Service:

RunAs=App

Enter fullscreen mode Exit fullscreen mode

Refer full values in this link :

https://docs.microsoft.com/en-us/dotnet/api/overview/azure/service-to-service-authentication#connection-string-support

How to call the above code when connecting to SQLConnection - follow this:


                using (SqlConnection _connection = new SqlConnection())
                {
                    _connection.ConnectionString = sqlConnectionString;
                    _connection.AccessToken = authTokenService.GetToken();

                    _connection.Open();

                    // do some stuff with the sqlconnection to read or write record in SQL.

                    _connection.Close();

                    return true;
                }

Enter fullscreen mode Exit fullscreen mode

3. Modify your project - new way of using Microsoft.Data.SqlClient

In Visual Studio, open the Package Manager Console and add the NuGet package Microsoft.Data.SqlClient

Install-Package Microsoft.Data.SqlClient -Version 4.0.1

Enter fullscreen mode Exit fullscreen mode

Token creation part is handled by this library by default.

In the ASP.NET Core and SQL Database tutorial, the MyDbConnection connection string in appsettings.json isn't used at all yet. The local environment and the Azure environment both get connection strings from their respective environment variables in order to keep connection secrets out of the source file. But now with Active Directory authentication, there are no more secrets. In appsettings.json, replace the value of the MyDbConnection connection string with:

"Server=tcp:<server-name>.database.windows.net;Authentication=Active Directory Default; Database=<database-name>;"



                using (SqlConnection _connection = new SqlConnection(sqlConnectionString))
                {
                    _connection.Open();

                    // do some stuff with the sqlconnection to read or write record in SQL.

                    _connection.Close();

                    return true;
                }

Enter fullscreen mode Exit fullscreen mode

4. Use managed identity connectivity

Next, you configure your App Service app to connect to SQL Database with a system-assigned managed identity.

Enable managed identity on app

To enable a managed identity for your Azure app, use the az webapp identity assign command in the Cloud Shell. In the following command, replace .

az webapp identity assign --resource-group myResourceGroup --name <app-name>

Enter fullscreen mode Exit fullscreen mode

Here's an example of the output:

{
  "additionalProperties": {},
  "principalId": "21dfa71c-9e6f-4d17-9e90-1d28801c9735",
  "tenantId": "72f988bf-86f1-41af-91ab-2d7cd011db47",
  "type": "SystemAssigned"
}

Enter fullscreen mode Exit fullscreen mode

Grant permissions to managed identity

In the Cloud Shell, sign in to SQL Database by using the SQLCMD command. Replace with your server name, with the database name your app uses, and and with your Azure AD user's credentials.

sqlcmd -S <server-name>.database.windows.net -d <db-name> -U <aad-user-name> -P "<aad-password>" -G -l 30

Enter fullscreen mode Exit fullscreen mode

In the SQL prompt for the database you want, run the following commands to grant the permissions your app needs. For example,

CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
ALTER ROLE db_ddladmin ADD MEMBER [<identity-name>];
GO

Enter fullscreen mode Exit fullscreen mode

is the name of the managed identity in Azure AD. If the identity is system-assigned, the name is always the same as the name of your App Service app. For a deployment slot, the name of its system-assigned identity is /slots/. To grant permissions for an Azure AD group, use the group's display name instead (for example, myAzureSQLDBAccessGroup).

Type EXIT to return to the Cloud Shell prompt.

5. Publish your changes

All that's left now is to publish your changes to Azure.

image.png

Reference:

https://docs.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database?tabs=windowsclient%2Cefcore%2Cdotnet

Discussion (0)