Ef6: DB queries hang when adding password fallback mechanism to primary AAD connection

[AmirGeorge]

We are currently facing issues when trying to implement fallback logic in the way we connect to our Azure SQL database from our Azure cloud services.

We currently connect to our Azure SQL DB using AAD authentication. What we are trying to do is implement fallback logic for this connection, where if there is a failure in the initial connection (ex. when acquiring the AAD access token), we fallback to a secondary connection. The secondary connection uses username + password authentication.

The issue we are facing is that on an Azure cloud service using EF6, the fallback path doesn't work. What we get is that the connection itself is initialized successfully, but any DB call via EF hangs forever with no response. During the investigation, we have done the following:
1) Trying the same on an Azure function app, it works. So it seems to only fail on Azure cloud services.
2) Issue is not reproducible locally, only on a remote Azure Cloud service.
3) When using username + password as primary authentication (no AAD connection anymore), things work, so it seems that it is not an issue with password authentication on Azure cloud services in general.
4) Examining long running queries on our Azure SQL instance, we don't find the queries that the cloud service indicates that are hanging. So the queries seem to never reach the DB.

Steps to reproduce

Code snippet for initial AAD logic we had:

```C#
public static SqlConnection InitializeSqlConnectionWithAad(
string dbConnectionString,
string dbClientIdThumbprintPair)
{
var accessToken = AsyncHelpers.RunSync(() => GetAccessToken(dbClientIdThumbprintPair));

        var connection = new SqlConnection(dbConnectionString)
        {
            AccessToken = accessToken
        };
        if (connection.AccessToken != null)
        {
            connection.Open();
        }

        return connection;
    }

private static async Task GetAccessToken(
string dbClientIdThumbprintPair)
{
if (string.IsNullOrWhiteSpace(dbClientIdThumbprintPair))
{
return null;
}

        var credentialsPair = dbClientIdThumbprintPair.Split(';');
        if (credentialsPair.Length != 2)
        {
            Logger.LogError("Invalid Client ID Thumbprint pair provided for database connection");
            throw new Exception("Invalid Client ID Thumbprint pair provided for database connection");
        }

        var clientId = credentialsPair[0];
        var clientThumbprint = credentialsPair[1];
        var cert = CertificateHelper.FindCertificateByThumbprint(clientThumbprint);

        if (cert == null)
        {
            Logger.LogError("Certificate not found for database connection");
            throw new Exception("Certificate not found for database connection");
        }

        var clientCertificate = new ClientAssertionCertificate(clientId, cert);
        string aadInstance = ConfigAccessor.GetSetting("OAuthAADInstance");
        string tenant = ConfigAccessor.GetSetting("OAuthAADTenant");
        string authority = string.Format(System.Globalization.CultureInfo.InvariantCulture, aadInstance, tenant);
        string dbAppURI = ConfigAccessor.GetSetting("DBResourceURI");

        var authContext = new AuthenticationContext(authority);
        var authenticationResult = await authContext.AcquireTokenAsync(
            dbAppURI,
            clientCertificate,
            sendX5c: true).ConfigureAwait(false);

        return authenticationResult.AccessToken;
    }

Code snippet for fallback logic (When an exception is thrown from AAD path, the fallback part with password is the path having issues):
```C#
public static SqlConnection InitializeSqlConnection(
            string dbConnectionString,
            string dbClientIdThumbprintPair,
            string dbConnStrWithPassword)
        {
            SqlConnection connection = null;
            bool aadConnectionSucceeded;

            try
            {
                connection = InitializeSqlConnectionWithAad(dbConnectionString, dbClientIdThumbprintPair);
                aadConnectionSucceeded = true;
            }
            catch (Exception ex)
            {
                Logger.LogError("Sql AAD connection failed. Falling back to secondary connection with password.", ex.ToString());
                aadConnectionSucceeded = false;
            }

            if (!aadConnectionSucceeded)
            {
                try
                {
                    connection = new SqlConnection(dbConnStrWithPassword);
                }
                catch (Exception ex)
                {
                    Logger.LogError("Sql password fallback connection failed.", ex.ToString());
                    throw;
                }
            }

            return connection;
        }

Further technical details

EF version: 6.3
Target framework: Net Framework 4.6.2
Operating system: Windows Server 2016
IDE: _Not reproducible locally_

[roji]

@AmirGeorge transferred this issue from https://github.com/dotnet/efcore, which is about Entity Framework Core.

[ajcvickers]

@AmirGeorge There doesn't seem anything directly related to EF here--it's all code that uses SqlClient only. Is there a specific reason that you filed this as an EF issue?

[kayousef]

@ajcvickers Thank you for your attention to this. The main reason we think this is kind of related to EF is that we checked on the SQL server side, and the queries don't reach the server at all. So, our best guess is that EF are blocking the queries for some reason.

Is what we are trying to do a common scenario? Have you seen it working before?

Do you have any suggestions for us to dig deeper into this to identify the responsible component?

[ajcvickers]

@kayousef There is nothing in EF that would intentionally block queries. I would suggest that you debug into what is actually happening on the client, potentially starting with logs from SqlClient to understand what is happening there.

[AmirGeorge]

@ajcvickers turned out to be an issue in our client code that we fixed, so we can close this issue.

For future reference in case anyone faces a similar issue, the problem was in the implementation of AsyncHelpers.RunSync which is provided in many online resources. The original implementation is as follows:

public static T RunSync<T>(Func<Task<T>> task)
        {
            var oldContext = SynchronizationContext.Current;
            var synch = new ExclusiveSynchronizationContext();
            SynchronizationContext.SetSynchronizationContext(synch);
            T ret = default(T);
            synch.Post(
                async _ =>
                {
                    try
                    {
                        ret = await task();
                    }
                    catch (Exception e)
                    {
                        synch.InnerException = e;
                        throw;
                    }
                    finally
                    {
                        synch.EndMessageLoop();
                    }
                }, null);
            synch.BeginMessageLoop();
            SynchronizationContext.SetSynchronizationContext(oldContext);
            return ret;
        }

Now the issue with the above is that the synchronization context was only set back to the old context only if no exception was thrown. If an exception was thrown, this doesn't happen which risks deadlocks, which happened in our case. Now to solve the issue, we simply moved the restoring of the synchronization context to be inside a finally statement:

public static T RunSync<T>(Func<Task<T>> task)
        {
            var oldContext = SynchronizationContext.Current;
            var synch = new ExclusiveSynchronizationContext();

            try
            {
                SynchronizationContext.SetSynchronizationContext(synch);
                T ret = default(T);
                synch.Post(
                    async _ =>
                    {
                        try
                        {
                            ret = await task();
                        }
                        catch (Exception e)
                        {
                            synch.InnerException = e;
                            throw;
                        }
                        finally
                        {
                            synch.EndMessageLoop();
                        }
                    }, null);
                synch.BeginMessageLoop();
                return ret;
            }
            finally
            {
                SynchronizationContext.SetSynchronizationContext(oldContext);
            }
        }