SqlException: Login failed for user '<token-identified principal>'.

[TresaL]

Hi,
I'm using ef core 3.1.2 with .net core 3.1 which uses Microsoft.Data.SqlClient 2.1.1 to run scheduled tasks on azure function under consumption plan. I use azure managed user identity to connect to Azure SQL server. My connection string is like:
Server={serverModel.DatabaseServerName}; Authentication=Active Directory Managed Identity; User Id={userIdentityId}; Database={serverModel.DatabaseName};

I'm intermittently facing SQL Exception as below:
Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user ''.
Inner exception Microsoft.Data.SqlClient.SqlException handled at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw:
at Microsoft.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod (Microsoft.Data.SqlClient, Version=2.0.20168.4, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject (Microsoft.Data.SqlClient, Version=2.0.20168.4, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest (Microsoft.Data.SqlClient, Version=2.0.20168.4, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection (Microsoft.Data.SqlClient, Version=2.0.20168.4, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection (Microsoft.Data.SqlClient, Version=2.0.20168.4, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection (Microsoft.Data.SqlClient, Version=2.0.20168.4, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal (Microsoft.Data.SqlClient, Version=2.0.20168.4, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection (Microsoft.Data.SqlClient, Version=2.0.20168.4, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
at Microsoft.Data.SqlClient.SqlConnection.TryOpen (Microsoft.Data.SqlClient, Version=2.0.20168.4, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
at Microsoft.Data.SqlClient.SqlConnection.Open (Microsoft.Data.SqlClient, Version=2.0.20168.4, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
at Microsoft.Data.SqlClient.SqlConnection.Open (Microsoft.Data.SqlClient, Version=2.0.20168.4, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
at Core.Repository.WritableDatabaseContext.ExecuteReader (Core, Version=1.0.0.0, Culture=neutral, PublicKeyToken=nullCore, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null: /home/vsts/work/1/s/Core/Repository/WritableDatabaseContext.csCore, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null: 83)

My ef db context is like:

public class MyDbContext : DbContext
{
    private string connectionString;

    public SnapCommsDbContext(string connectionString)
    {
        this.connectionString = connectionString;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(this.connectionString,
            option => option.EnableRetryOnFailure(3, TimeSpan.FromSeconds(30), Enumerable.Empty<int>().ToList()));
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
}
}

The exception occurred at Microsoft.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod. Why this method returns SQL exception with Login failed message? Probably, I might have reached any resource limitation on consumption plan?

Any inside on this is much appreciated.

[cheenamalhotra]

Hi @TresaL

I'm hoping your connection string is static and database(s) you connect to all recognize the User Identity being used here? e.g. ensure your connection string always contains name of database you're connecting to, lack of Catalog connects to master which may not recognize your user account.

From driver perspective, a new access token is directly fetched from IMDS when a new connection is made. When pooling connections, the same access token will be used until it's expiry and then it's renewed from IMDS again.

But if you don't modify your connection string and same connection string fails intermittently you could reach out to Azure Support for any possible network issues in your environment. They can also provide you insights for any resource limits if they exist in your subscription.

Also, could you also provide more info on what is your connection usage like and the pattern of intermittent failures so we can try to reproduce it too? If you could wrap up a repro would also be of great help!

[TresaL]

Hi @cheenamalhotra ,
Thanks for your response. My connection string is kind of dynamic. I have a scheduled task (one azure function fan-out and fan-in) runs against different databases, but each database resides in different SQL Server, that is one database per server.
There is no particular pattern which I could find for this exception.

But it happens every 2nd or 3rd day.

I'll enable retry on this error and see whether it helps. but what happen in driver when this error occur? Does it remove the connection from pool? and what is the default expiry of access token when fetched from IMDS?

Also the stack trace origin from
at Microsoft.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod (Microsoft.Data.SqlClient, Version=2.0.20168.4, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject (Microsoft.Data.SqlClient, Version=2.0.20168.4, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5).
What could go wrong from CheckPoolBlockingPeriod() which endups in login failure error?

Thanks,
Tresa

[David-Engel]

@TresaL Can you try updating to the latest 5.2 release? This may have been fixed via #2238 and #2273.

[cheenamalhotra]

Closing as stale and inactive.