aspire: Connection to SQL Server (was Azure SQL Edge) Fails When .NET API Is Started Using an Aspire Host (Issue Limited to macOS?)

Update: Turns out standard SQL Server now works on Mac with Appel Silicon, but the problem is exactly the same.

I’ve configured a .NET API and ~Azure SQL Edge~ SQL Server using Aspire, and the connection specifically fails when initiated from an Aspire Host. Here is the exception:

Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - 20)

It took me forever to isolate, but it turns out that this only happens when the API is started by the Aspire Host. I’ve created a Git repository that showcases the problem here: https://github.com/tjementum/AspireWithSqlServer, with even more info in the README.

If the same connection is initiated by starting the .NET API directly, everything works correctly. In fact, if I start everything using Aspire and let it fail, and then manually start the API it works. The repository has a Gif that shows the problem.

Here is a summary.

AppHost.Program.cs:

using Projects;

var builder = DistributedApplication.CreateBuilder(args);

var sqlPassword = "YourSecretPassword01!"; 

var sqlServer = builder.AddSqlServerContainer("localhost", sqlPassword, 1433)
    .WithAnnotation(
        new ContainerImageAnnotation { Registry = "mcr.microsoft.com", Image = "azure-sql-edge", Tag = "latest" }
    );

builder.AddProject<AspireWithSqlServer_WebApi>("WebApi")
    .WithEnvironment("SQL_PASSWORD", sqlPassword)
    .WithEnvironment("SQL_SERVER", sqlServer.Resource.Name);

builder.Build().Run();

Update: Turns out standard SQL Server now works on Mac with Appel Silicon, but the problem is exactly the same. I’ve updated this issue.

In the API, I connect to the database using Entity Framework with this connection string:

public static string GetConnectionString(this IConfiguration configuration)
{
    var connectionString = configuration.GetConnectionString("Default");
    var sqlServer = Environment.GetEnvironmentVariable("SQL_SERVER") ?? "localhost";
    var sqlPassword = Environment.GetEnvironmentVariable("SQL_PASSWORD") ?? "YourSecretPassword01!";
    return connectionString!
        .Replace("${SQL_SERVER}", sqlServer)
        .Replace("${SQL_PASSWORD}", sqlPassword);
}

appsettings.json:

{
  "ConnectionStrings": {
    "Default": "Server=${SQL_SERVER};Database=weather;User Id=sa;Password=${SQL_PASSWORD};Encrypt=False;TrustServerCertificate=True"
  }
}

I’m running this on a MacBook M1 with an ARM processor ~, which necessitates the use of Azure SQL Edge, as the standard SQL Server is not supported on ARM architectures~.

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Reactions: 1
  • Comments: 70 (49 by maintainers)

Commits related to this issue

Most upvoted comments

Here’s my workaround for macOS - use the built-in strategy on startup but extending it with an additional error number 0 which I receive on mac when SQL Server container is not ready:

using (var scope = app.Services.CreateScope())
{
    var dbContext = scope.ServiceProvider.GetRequiredService<CustomerDbContext>();
    
    var strategy = new SqlServerRetryingExecutionStrategy(dbContext, 10,
        TimeSpan.FromSeconds(5), new[] { 0 });

    await strategy.ExecuteAsync(async () =>
    {
        var dataFactory = scope.ServiceProvider.GetRequiredService<DataFactory>();
        await dbContext.Database.EnsureDeletedAsync();
        await dbContext.Database.EnsureCreatedAsync();
        await dataFactory.SeedAsync();
    });
}

The samples were updated for preview.5 to include retrying for this error and is now verified on Ubuntu as part of CI. Can you check again on macOS?

Can we make the EF core components handle this until EF makes that change? That’s a .NET 9 change no?

Looks like @AndriySvyryd made an 8.0 fix/backport - https://github.com/dotnet/efcore/pull/33261.

@AndriySvyryd , looks like 203 on the Mac is the only error code in the Errors collection. On Windows looks like it is 10054 when setup under the debugger to break on the exception.

I’ll try to reproduce this with preview2 on macOS

I’ve tested the repo provided by @tjementum and I have the same issue on Ubuntu 23.10.

Both main and change-to-ip-and-let-aspire-host-pass-connection-string branches fail with Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - Unknown error 203).

Also note:

“Azure SQL Edge based on ARM64 architecture is retired”

from:

https://techcommunity.microsoft.com/t5/sql-server-blog/azure-sql-edge-update-september-2023/ba-p/3930827

I hope you are unblocked by being able to run SQL Server using Docker Desktop 4.25+ on M1+

Wouldn’t a workaround to this just to run Rosetta on a Macbook?

Also note:

“Azure SQL Edge based on ARM64 architecture is retired”

from:

https://techcommunity.microsoft.com/t5/sql-server-blog/azure-sql-edge-update-september-2023/ba-p/3930827

I hope you are unblocked by being able to run SQL Server using Docker Desktop 4.25+ on M1+

This issue can be closed as it’s tracked elsewhere - https://github.com/dotnet/SqlClient/issues/2438 This shouldn’t be an issue if WaitForResource is used, otherwise use the execution strategy https://github.com/dotnet/aspire-samples/blob/98e9c0eea6f8111ef3e4d45546d098b9de61ea65/samples/Shared/RetryingSqlServerRetryingExecutionStrategy.cs

I believe this is fixed in EF 9? @AndriySvyryd ?

I don’t think there is anything to do on Aspire’s side, but I don’t feel like closing this issue either so we can remember to track the progress. If EF had to do something it would be to also catch 0 error codes, and I believe it’s not a solution.

I was able to repro the issue locally using WSL. I tried the fix in EF Core but it didn’t solve the problem so I debugged it. The first exception is 203 and EF retries correctly (the inner exception is correct too), but then I get a number 0 with this message:

Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - Success)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning(Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
   at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
   at Microsoft.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(SqlConnectionEncryptOption encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable, Boolean& fedAuthRequired, Boolean tlsFirst, String serverCert)
   at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnectionString connectionOptions, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen()
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.<>c__DisplayClass20_0.<<ExistsAsync>b__0>d.MoveNext()
ClientConnectionId:86207f62-55b7-4db8-811a-4b45ea17c8a8

Then I updated the retry logic in EF to handle 0 also and @tjementum 's hack was not required anymore. I’ll let the EF team be judge of how to interpret error 0 but I would risk thinking the problem lies in SqlClient.

@AndriySvyryd if you need more info on debugging this feel free to ping me.

I am looking into this

@tjementum I think there has been a regression in SqlClient - can you try to add this to your connection string:

ConnectRetryCount=0

The connection string is generated by Aspire, so I don’t know how to do that. But if that is the case, then this is the fix the Aspire team needs to implement.

You can see an example here: https://github.com/tjementum/AspireWithSqlServer (and the dirty workaround here).

@dbreshears Ok, but we need the error numbers to be able to reliably detect this in code. The numbers in the message generally don’t match the ones in the exception instance.

@davidfowl , I don’t reproduce the slower startup on macos. For me after examining the container logs it’s about 8 seconds (mac) to 10 seconds (windows) seconds to startup the sql container due to all its system level DB updates. Its the same whether I manually start the container or let DCP through Aspire.

The main issue is 1 above on Mac (and assume Linux) and doesn’t reproduce on Windows since the transient errors are being handled by the EF execution strategy. Addressing that should alleviate any specific retry logic in the application.

I will take a look and report what I find.

@karolz-ms is there any follow up here?

I am sorry I have been super busy with P4 work, never had a chance to repro this. Looks like this will be the case till EOW 😞

@AndriySvyryd

Perhaps the approach suggested in #2090 could be generalized to block whenever the connection string is requested until the endpoint is ready.

Unfortunately the AfterEndpointsAllocatedAsync lifecycle hook I’m using in the sample for #2090 is called after the connection strings for the endpoints are available, but before the database container is running. I’m not sure using that as is would solve the exceptions @tjementum is having.

Two ideas that may solve for applying migrations from AppHost:

  1. Adding a lifecycle hook for when a container is ready and listening.
  2. Use the existing AfterEndpointsAllocatedAsync lifecycle hook like I do in #2090, but start a background thread or task to retry and wait applying the migrations.

Can someone explain the issue? (there’s been a lot of “Updates” above, and it is hard to follow what the current state is)

Let me summarize. The problem occurs on Mac and Linux when you use Aspire to configure a SQL Server like this in the AppHost’s Program.cs:

using Projects;

var builder = DistributedApplication.CreateBuilder(args);

var sqlServer = builder.AddSqlServerContainer("default")
    .AddDatabase("weather");

builder.AddProject<AspireWithSqlServer_WebApi>("WebApi")
    .WithReference(sqlServer);

builder.Build().Run();

When starting the WebApi, it throws one of the following two different exceptions when using EF Core:

Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - 20)

image

System.Net.Sockets.SocketException (22): Invalid argument

image

This repository https://github.com/tjementum/AspireWithSqlServer reproduces the error.

This seems to be a problem only when the API is trying to connect to the SQL server before it’s ready, in this case, when EF Core is running migrations during startup.

@davidfowl gave a lot of feedback to try to resolve the issue. I still have the problem, but the repository is now even more to the point.

NOTE: This repository also has the following workaround that retries the migrations in a loop, and eventually, it succeeds. This makes me conclude that it’s a timing issue.

var retryCount = 0;
while (retryCount <= 30)
{
    if (retryCount >= 1)
        logger.LogInformation("Waiting for databases to be ready. Retry count: {RetryCount}", retryCount);

    try
    {
        var dbContext = scope.ServiceProvider.GetService<WeatherForecastContext>();

        var strategy = dbContext.Database.CreateExecutionStrategy();

        await strategy.ExecuteAsync(() => dbContext.Database.MigrateAsync(cancellationToken));

        break;
    }
    catch (SqlException ex) when (ex.Message.Contains("an error occurred during the pre-login handshake"))
    {
        // Known error in Aspire, when SQL Server is not ready. See: https://github.com/dotnet/aspire/issues/1023
        retryCount++;
        Thread.Sleep(TimeSpan.FromSeconds(1));
    }
    catch (SocketException ex) when (ex.Message.Contains("Invalid argument"))
    {
        // Known error in Aspire, when SQL Server is not ready. See: https://github.com/dotnet/aspire/issues/1023
        retryCount++;
        Thread.Sleep(TimeSpan.FromSeconds(1));
    }

Is the problem that your app is trying to connect to the SQL Server instance, but the SQL Server is not ready for connections yet? Yes, you need to handle the server not being there yet, and wait until it is ready.

This is kind of what I’m doing with the loop. BUT if I start the SQL Server using a manual Docker run, then it does not fail. So, in my opinion, it’s clearly Aspire that should not start the API until the Docker server is ready. The Aspire Sample project also has the same problem.

Got it.

Is the workaround to retry while applying runtime migrations?

@tjementum just confirming, you’re on macOS?

Could this be being caused by the Traefik proxy being spun-up in front of the SQL Server container? We intend to change that behavior for scenarios like this AFAIK. @davidfowl do we have an issue for that?

Seems like this is private.

Apologies, please try again: https://github.com/tjementum/AspireWithSqlServer

I created this repo recalling tweets from you about how unhelpful ‘it does not work’ bug reports are. 😄

@mitchdenny ran into a similar issue and ended up using 127.0.0.1 instead of localhost.

I’ve tried switching from localhost to 127.0.0.1, but I’m afraid I still facing the same error.

That will set the connection string “Default” in your application.

I just attempted that, but now I received a “ConnectionString is missing.” error. I might not be setting it up correctly. I’ve created a pull request with these changes. Though, this might not be related to our current issue.

I wonder if we can detect this and make it just work with the SQL server resource.

Azure SQL Edge and SQL Server aren’t identical (see Azure SQL unsupported-features). I would prefer being explicit about such differences. I’m speaking from experience here. 😃 I would rather have a separate AddAzureSqlContainer() in Aspire.