c# - The 'ClientID' option must be provided - Azure API - Connect to AzureSQL using managed identity

I have built an API as an Azure Web app that will be hosted through Azure API Management Service. I need the app to connect to an AzureSQL database using a system assigned managed identity.

var credential = new Azure.Identity.DefaultAzureCredential(); // system-assigned identity
  
// Get token for Azure SQL Database
var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net/.default" }));

// Add the token to the SQL connection
var conn = new SqlConnection(_connStringRules);
conn.AccessToken = token.Token;

conn.Open();

Connection string: Server=xxx;Initial Catalog=xxx;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication="Active Directory Managed Identity";

But I am getting the error The 'ClientID' option must be provided I am using dapper instead of EF, and following the below tutorial (coding aspect) https://learn.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database?tabs=windowsclient%2Cef%2Cdotnet

I am using a system assigned identity, with the Contributor role assigned to it

So I am not sure why I am being asked for a clientid?

Does this refer to client id you get when adding an identity provider?

Or am I barking up the wrong tree? Any ideas welcome!!!

Solution

To connect Azure sql server from Azure web API with system assigned managed identity authentication give connection string in below format in Appsetting.json

"ConnectionStrings": {
        "QuotesDatabase": "Server=tcp:<servename>.database.windows.net,1433; Database=<databasename>;" }

Use below code for connection.

var connectionString = Configuration.GetConnectionString("<connectionstringname>");
                services.AddTransient(a =>{
                    var sqlConnection = new SqlConnection(connectionString);
                    var credential = new DefaultAzureCredential();
                    var token = credential
                            .GetToken(new Azure.Core.TokenRequestContext(
                                new[] { "https://database.windows.net/.default" }));
                    sqlConnection.AccessToken = token.Token;
                    return sqlConnection;

enter image description here

I set admin as you want to the sql server.

enter image description here

choose administrator account for azure service authentication to retrieve the token credentials.

Image for reference:

enter image description here

Enable system assigned manage identity in on state of Azure app service.

enter image description here

create user [<appName>] from external provider;
alter role db_datareader add member [<appName>];
alter role db_datawriter add member [<appName>];

enter image description here

The database successfully connected to the app.

Image for reference:

enter image description here