User Assigned Identities with App Service and Azure SQL does that work?

Question

I am trying to get App Service to connect with Azure Sql database. I can git is nicely work with System Assigned Identities with the same code, but I prefer to use User Assigned Identities (UAI), but I cannot get it work.

Steps which I do:

1. Created a UAI via the portal, name of the UAI "uai-dev-appname-001"
2. At the Identity tab of the Azure App Service I selected 'User Assigned Identity' and selected the UAI made in the previous step.
3. Ran the following SQL CMD

CREATE USER [uai-dev-appname-001] FROM EXTERNAL PROVIDER
ALTER ROLE db_datareader ADD MEMBER [uai-dev-appname-001]
ALTER ROLE db_datawriter ADD MEMBER [uai-dev-appname-001]

4. Set Connectionstring in the ASP.NET to:

   Data Source=sqlsrv-name-dev-001.database.windows.net; Initial Catalog=sqldb-name-dev-001;

5. Using the following code in mine ASP.NET Core:

SqlConnection connection = new SqlConnection
{
   ConnectionString = configuration.GetConnectionString("nameDatabase")
};
AzureServiceTokenProvider provider = new AzureServiceTokenProvider();
var token = provider.GetAccessTokenAsync("https://database.windows.net/").Result;
connection.AccessToken = token;

6. Deploy to Azure App Service and watched the URL. The result is: error 500.30
7. Looking in the Application Event Log:

   Unhandled exception. System.AggregateException: One or more errors occurred. (Parameters: Connection String: [No connection string specified], Resource: https://database.windows.net, Authority: . Exception Message: Tried the following 3 methods to get an access token, but none of them worked. Parameters: Connection String: [No connection string specified], Resource: https://database.windows.net, Authority: . Exception Message: Tried to get token using Managed Service Identity. Access token could not be acquired. Received a non-retryable error. MSI ResponseCode: BadRequest, Response: {"StatusCode":400,"Message":"No MSI found for specified ClientId/ResourceId.","CorrelationId":"a68bf757-518a-42e1-85a9-342320d39b5a"} Parameters: Connection String: [No connection string specified], Resource: https://database.windows.net, Authority: . Exception Message: Tried to get token using Visual Studio. Access token could not be acquired. Visual Studio Token provider file not found at "D:\local\LocalAppData.IdentityService\AzureServiceAuth\tokenprovider.json" Parameters: Connection String: [No connection string specified], Resource: https://database.windows.net, Authority: . Exception Message: Tried to get token using Azure CLI. Access token could not be acquired. 'az' is not recognized as an internal or external command, operable program or batch file.

The most interesting part in IMO is:

Response: {"StatusCode":400,"Message":"No MSI found for specified ClientId/ResourceId.","CorrelationId":"a68bf757-518a-42e1-85a9-342320d39b5a"}

Mine question are:

• Does User Assigned Identies work with Azure SQL?
• If so what do I do wrong?
• Does someone has a working example.

Answer 1

User-assigned Managed Identity is supported from version 1.2.1 of Microsoft.Azure.Services.AppAuthentication.

So, please update the version of Microsoft.Azure.Services.AppAuthentication to the latest.

Then set AzureServicesAuthConnectionString in the Appsettings of the AppService to RunAs=App;AppId={ClientId of user-assigned identity}

E.g.

RunAs=App;AppId=587f16c8-81ed-41c7-b19a-9ded0dbe2ca2

Documentation can be found here.

Once you do these steps, your code should be using user-assigned managed identity.