Connection Timeout issue when trying to execute Non Async method using Dapper, Microsoft.Data.SqlClient V3.0.0 Stable in .NETFrameowork 4.8 WebApplication

[somnath-pramanik123]

Describe the bug

A clear and concise description of what the bug is.

Getting timeout issue while trying to execute Non Async method using Dapper and Microsoft.Data.SqlClient,.

Exception message: 
Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.

Stack trace:
System.InvalidOperationException at Dapper.SqlMapper.QueryMultipleImpl


### To reproduce
                    using (conn)
                    {                        

                        using (var multiple = conn.QueryMultiple("GetResult", commandType: System.Data.CommandType.StoredProcedure))
                        {                            
                            var result = multiple.Read().ToList();
                        }

                    }

Add below line in Application_Startup method in Global.asax:
AppContext.SetSwitch("Switch.Microsoft.Data.SqlClient.EnableRetryLogic", true);

Using Active Directory Integrated Authentication to connect Azure SQL DB.

### Expected behavior
It should return the result

### Further technical details
Microsoft.Data.SqlClient version: (found on the nuget  V3.0.0 stable)
.NET target: (e.g. Framework 4.8)
ASP.NET MVC Application
SQL Server version: Azure SQL DB
Operating system: (e.g. Windows 10)

**Additional context**
Below is the Event Tracing in SqlClient: 


<prov.DbConnectionHelper.ConnectionString_Get|API> 1
<sc.SqlConfigurableRetryLogicManager.ConnectionProvider|INFO> Requested the ConnectionProvider value.
<sc.AppConfigManager.FetchConfigurationSection|INFO> Successfully loaded the configurable retry logic settings from the configuration file's section 'SqlConfigurableRetryLogicConnection'.
<sc.AppConfigManager.FetchConfigurationSection|INFO>: Unable to load section `SqlConfigurableRetryLogicCommand`. ConfigurationManager failed to load due to configuration errors: System.Configuration.ConfigurationErrorsException: The value for the property 'maxTime' is not valid. The error is: The value must be inside the range 00:00:00-00:02:00. 
   at System.Configuration.BaseConfigurationRecord.EvaluateOne(String[] keys, SectionInput input, Boolean isTrusted, FactoryRecord factoryRecord, SectionRecord sectionRecord, Object parentResult)
   at System.Configuration.BaseConfigurationRecord.Evaluate(FactoryRecord factoryRecord, SectionRecord sectionRecord, Object parentResult, Boolean getLkg, Boolean getRuntimeObject, Object& result, Object& resultRuntimeObject)
   at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject)
   at System.Configuration.BaseConfigurationRecord.GetSection(String configKey)
   at System.Web.HttpContext.GetSection(String sectionName)
   at System.Web.Configuration.HttpConfigurationSystem.GetSection(String sectionName)
   at System.Web.Configuration.HttpConfigurationSystem.System.Configuration.Internal.IInternalConfigSystem.GetSection(String configKey)
   at System.Configuration.ConfigurationManager.GetSection(String sectionName)
   at Microsoft.Data.SqlClient.AppConfigManager.FetchConfigurationSection[T](String name) in H:\tsaagent1\_work\18\s\src\Microsoft.Data.SqlClient\src\Microsoft\Data\SqlClient\Reliability\AppConfigManager.cs:line 29
<sc.AppConfigManager.FetchConfigurationSection|INFO>: Unable to load custom `SqlConfigurableRetryLogicCommand`. Default value of `T` type returns.
<sc.SqlConfigurableRetryLogicLoader.CreateRetryLogicProvider|INFO> Entry point.
<sc.SqlConfigurableRetryLogicLoader.CreateRetryLogicProvider|INFO> Successfully created a SqlRetryLogicOption object to use on creating a retry logic provider from the section 'SqlConfigurableRetryLogicConnection'.
<sc.SqlConfigurableRetryLogicLoader.ResolveRetryLogicProvider|INFO> Entry point.
<sc.SqlConfigurableRetryLogicLoader.LoadType|INFO> The 'null' type is resolved.
<sc.SqlConfigurableRetryLogicLoader.CreateInstance|INFO> Entry point.
<sc.SqlConfigurableRetryLogicLoader.CreateInstance|INFO> The given type `SqlConfigurableRetryFactory` infers as internal `Microsoft.Data.SqlClient.SqlConfigurableRetryFactory` type.
<sc.SqlConfigurableRetryLogicLoader.CreateInstance|INFO> The `Microsoft.Data.SqlClient.SqlConfigurableRetryFactory.CreateFixedRetryProvider()` method has been discovered as the `CreateFixedRetryProvider` method name.
<sc.SqlConfigurableRetryLogicLoader.PrepareParamValues|INFO> Parameters are prepared to invoke the `Microsoft.Data.SqlClient.SqlConfigurableRetryFactory.CreateFixedRetryProvider()` method.
<sc.SqlConfigurableRetryLogicLoader.CreateInstance|INFO> Parameters are prepared to invoke the `Microsoft.Data.SqlClient.SqlConfigurableRetryFactory.CreateFixedRetryProvider()` method.
<sc.SqlConfigurableRetryLogicLoader.ResolveRetryLogicProvider|INFO> The created instace is a Microsoft.Data.SqlClient.SqlRetryLogicBaseProvider type.
<sc.SqlConfigurableRetryLogicLoader.CreateRetryLogicProvider|INFO> Successfully created a SqlRetryLogicBaseProvider object from the section 'SqlConfigurableRetryLogicConnection'.
<sc|SqlAuthenticationProviderManager|Ctor|Info>Neither SqlClientAuthenticationProviders nor SqlAuthenticationProviders configuration section found.
<sc.TdsParser.Connect|SEC> Active Directory Default authentication
<sc.TdsParser.Connect|SEC> Sending prelogin handshake
<sc.TdsParser.SendPreLoginHandshake|INFO> ClientConnectionID XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXX, ActivityID XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXX:2
<sc.TdsParser.Connect|SEC> Consuming prelogin handshake
<sc.TdsParser.Connect|SEC> Prelogin handshake successful
<sc.TdsParser.TdsLogin|SEC> Sending federated authentication feature request
<sc.TdsParser.TryRun|SEC> Received federated authentication info token
<sc.TdsParser.TryProcessFedAuthInfo> Processed FEDAUTHINFO token stream: STSURL: https://login.windows.net/XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXX, SPN: https://database.windows.net/
<sc.SqlInternalConnectionTds.OnFedAuthInfo> 4, Generating federated authentication token

Thanks..

[JRahnama]

@somnath-pramanik123 base on the part of the code you posted you are using MARS enabled in your connection string. Is that correct? Can you send your connection string as well please (Please do not send any sensitive information such as password)?

Thank you.

[somnath-pramanik123]

@JRahnama,
Below is the connection string:

    private SqlConnection GetMyDbConnection()
    {
        return new SqlConnection($"Server={serverName}.database.windows.net; Authentication=Active Directory Integrated;      Database={dbName};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;");
    }

[JRahnama]

@somnath-pramanik123 Can you test your application by setting the TrustServerCertificate=True. There is a possibility that the connection was not stablished to sql server and it got timed out. If that did not solve the issue can you increase the ConnectTimeout value to see if that solves the issue?

[somnath-pramanik123]

@JRahnama : Tried with TrustServerCertificate=True and increasing ConnectionTimeout value..but no luck.

Did you try with the set up I mentioned and is it working on your end?. If yes, can you share the code.

[JRahnama]

@somnath-pramanik123 let's check if the token is acquired from MSAL library. Can you run the application on this gist. Do not forget to provide your server name at line 22 string server = "<server>.database.windows.net";. We just want to confirm that a token is successfully acquired. The only difference my application had with yours I used a SqlConnectionStringBuilder to provide information to SqlConnection.

[smartstoremx]

Dear All,

We are trying to get the access token to use to request my own api. I am using Microsoft. Identity.Client nuget. version: 4.36.0

a) In Net core MVC web application, unit test, console , Library projects works.
b) In Net framework 4.7.2 in unit test, console, library projects works.

c) In MVC web application with net framework 4.7.2 do not work. When you execute await app.AcquireTokenForClient(scopes) . It stay forever not response.

We did the following. you can replicate the problem.

Open visual studio 2019.

Create a new project using MVC web application net framework 4.7.2

Inside the home controller add the following code:

public async Task<string> TokenGetAsync()
 {
     IConfidentialClientApplication app;

     app = ConfidentialClientApplicationBuilder.Create(" your AD client ID ")  
            .WithClientSecret(" Secret ID ")  
            .WithAuthority(new Uri("https://login.microsoftonline.com/ {domain name} .onmicrosoft.com"))
            .Build();
     string[] scopes = new string[] { "https://{domain name}.onmicrosoft.com/xxxxxx/.default" };

     AuthenticationResult result = null;
     try
     {
         // **************Here stay for ever never return the token in MVC net framework 4.7.2******************
         result = await app.AcquireTokenForClient(scopes)
             .ExecuteAsync();    
     }
     catch (MsalServiceException ex) when (ex.Message.Contains("AADSTS70011"))
     {
            Console.WriteLine("Scope provided is not supported");
            return "";
     }
     return result.AccessToken;
 }

 public ActionResult Index()
 {
     ViewBag.Token = TokenGetAsync().GetAwaiter().GetResult(); 
     return View();
 }

[JRahnama]

@smartstoremx can open a new issue for this matter. It will make it easier for others to follow the problem in a related context and also will not mix this issue with any other issue. In that issue please mention if you are using System.Data.SqlClient or Microsoft.Data.SqlClient and what version of them.

[JRahnama]

@smartstoremx plus at this part of your code Microsoft.Data.SqlClient nor System.Data.SqlClient is involved. you are using either using ADAL or MSAL library to acquire token. I think the setup is explained in here. For more information you can open an issue with related group.

[cheenamalhotra]

@smartstoremx

The issue you posted was discussed here:

• How to call AcquireTokenInteractively synchronously? AzureAD/microsoft-authentication-library-for-dotnet#2823 (comment)
• Possible threading issues in version 3.0 when using Azure Managed Identity auth  #1209 (comment)

It was fixed in PR #1213 so please try with latest v4.0.0-preview1+ and it should be resolved. I will close this issue as the original issue was fixed with this PR too.