SqlClient: Active Directory Managed Identity authentication method not working

Describe the bug

While using the Active Directory Managed Identity authentication method in .NET 4.7, the connection is not working. Same connection is working in Core 3.1.

Exception message:
No Managed Identity found for specified ClientId/ResourceId/PrincipalId.
Stack trace:
Microsoft.Data.SqlClient.SqlException: Received a non-retryable error. Identity Response Code: BadRequest, Response: {"StatusCode":400,"Message":"No Managed Identity found for specified ClientId/ResourceId/PrincipalId.","CorrelationId":"2744aeb6-881e-4f69-b9c0-7151a107607f"}at Microsoft.Data.SqlClient.AzureManagedIdentityAuthenticationProvider.<AcquireTokenAsync>d__13.MoveNext()

To reproduce

Create a .NET 4.7 function app with the code below and publish it to Azure V1 function app.

using System;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.Azure.WebJobs.Host;
using Microsoft.Data.SqlClient;

namespace FunctionApp2
{
    public static class Function1
    {
        [FunctionName("Function1")]
        public static async Task<HttpResponseMessage> Run([HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)]HttpRequestMessage req, TraceWriter log)
        {
            log.Info("C# HTTP trigger function processed a request.");

            var str = Environment.GetEnvironmentVariable("sqldb_connection");

            try {
                using (SqlConnection conn = new SqlConnection(str))
                {
                    conn.Open();
                    string t = DateTime.Now.ToString();
                    var text = "INSERT INTO timetest VALUES('"+t+"');";

                    using (SqlCommand cmd = new SqlCommand(text, conn))
                    {
                        // Execute the command and log the # rows affected.
                        var rows = await cmd.ExecuteNonQueryAsync();
                        log.Info("1 row" + t + " inserted");
                    }
                }
            }
            catch (Exception ex)
            {
                log.Info(ex.ToString());
            }

            return null;
        }
    }
}

Add User assigned managed Identity to that azure function app, assign owner role of the azure SQL database to that Identity. Add the app setting sqldb_connection with the connection string as its value to the azure function app. Connection string:

Server=mysqlserver.database.windows.net,1433;database=mydatabase;User Id=object id of the Identity;Authentication=Active Directory Managed Identity;

Expected behavior

A new record get inserted into my database table.

Further technical details

.NET version: 4.7 Microsoft.Data.SqlClient version: 2.1.2

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Comments: 27 (12 by maintainers)

Most upvoted comments

Thank you @cheenamalhotra for the tests and information shared!

As I just tested, with only Azure.Identity 1.3 and Azure function v1 SDK, the function will also throw the exception System.IO.FileNotFoundException: Could not load file or assembly 'System.Runtime.CompilerServices.Unsafe, Version=4.0.4.1, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies.

So as I understand, to fix the original issue No Managed Identity found for specified ClientId/ResourceId/PrincipalId., I need to use the preview version of the Microsoft.Data.SqlClient. But the preview version Microsoft.Data.SqlClient requires v1.3 Azure.Identity which has compatibility issue with Azure function v1 SDK, it will cause the 4.0.4.1 issue.

Is that correct?