Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Connection Open() having delay in establishing connection using Managed Identity Authentication #2149

Closed
ronyabrahammathew opened this issue Sep 8, 2023 · 3 comments
Labels
🔗 External Issue is in an external component

Comments

@ronyabrahammathew
Copy link

Describe the bug

We are seeing 14-15 sec time delay in establishing connection to Azure SQL database using Managed Identity access from .Net console application as well as Azure Function App when we are using Open().
But while using Database service account to connect to Azure SQL with the same open() it is taking less than 1 sec for connection establishment.
For the MI access, while using OpenAsync() the connection establishment is much more faster compared to the Open(). We need to understand why Open() is causing an issue.

To reproduce

Below is the code piece we were using for testing connection. We removed the server/db/user/password related information form the connection string

using Microsoft.Data.SqlClient;
using System.Data;

try
{
    //using (SqlConnection con = new SqlConnection("Server=tcp:****.database.windows.net,1433;Initial Catalog=****;Persist Security Info=False;User ID=****;Password=***********;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;ApplicationIntent=ReadWrite;"))
    using (SqlConnection con = new SqlConnection("Server=tcp:****.database.windows.net;Authentication=Active Directory Default; Encrypt=True;Initial Catalog=****;Connection Timeout=30;"))
    {
        con.Open();
        using SqlCommand cmd = new SqlCommand("select 1 from sys.tables with (nolock)", con);
        cmd.CommandType = CommandType.Text;
        Console.WriteLine(cmd.ExecuteScalar());
        con.Close();
    }
}
catch (Exception ex)
{
    Console.WriteLine(ex.ToString());
}

Expected behavior

In ideal scenario, while using Open() the connection establishment shouldn't take more than 1 sec

Further technical details

Microsoft.Data.SqlClient version: 5.1.1
.NET target: .Net 6.0
SQL Server version: Azure SQL Server 12.0.2000.8
Operating system: Windows 10 Enterprise

Additional context
We are a Unified Support customer. We are facing issue on the connection establishment from our Azure function app to Azure sql server using the MI authentication. As part of the issue, we have an active MS ticket to resolve the issue.

Tagging @AshleyGrant

@JRahnama JRahnama added the 🆕 Triage Needed For new issues, not triaged yet. label Sep 8, 2023
@JRahnama
Copy link
Contributor

@ronyabrahammathew thank you for reporting this issue. We will look into this and will get back to you.

@JRahnama JRahnama removed the 🆕 Triage Needed For new issues, not triaged yet. label Sep 12, 2023
@David-Engel
Copy link
Contributor

Authentication = Active Directory Default uses a chain set of credentials to try to obtain an access token (docs). Most likely one of those is causing the delay. I would guess it's the Managed Identity credential. But it depends on how your environment is set up. MS Support should be able to help you troubleshoot.

@akanieski
Copy link

akanieski commented Oct 10, 2023

Questions on this:

  • You were able to repro this consistently from both Azure VM (you mentioned console app.. wasn't sure from where that was running) and from Azure Functions?
  • Can you share the con.ClientConnectionId and timestamp for an example connection that took this 14-15 seconds?

As @David-Engel points out, the "Azure Directory Default" leans on DefaultAzureCredential from MSAL .. which in turn iterates through a series of credentialing methods until it finds one that works. It starts with EnvironmentCredential, then WorkloadCredential then ManagedIdentityCredential.

It's possible delays to getting a token for a given ManagedIdentity could come from the underlying IMDS (Instance MetaData Service) that provides the token. Usually delays from IMDS come from overloading the service too many requests for tokens in a short period of time. There are SLAs and limits for how many tokens be requested at once. Although I would imagine this is unlikely given your running a single query. Unless of course the VM you tested from (if it was a VM) was running other workloads.

@David-Engel David-Engel added the 🔗 External Issue is in an external component label Jun 15, 2024
@David-Engel David-Engel closed this as not planned Won't fix, can't repro, duplicate, stale Jun 15, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
🔗 External Issue is in an external component
Projects
Development

No branches or pull requests

4 participants