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)
Azure.Identity issue created https://github.com/Azure/azure-sdk-for-net/issues/21361
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?