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

[tjementum]

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.

[davidfowl]

: https://github.com/tjementum/AspireWithSqlServer, with even more info in the README.

Seems like this is private.

You can simplify your app definition:

var builder = DistributedApplication.CreateBuilder(args);

var sqlPassword = "YourSecretPassword01!"; 

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

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

builder.Build().Run();

That will set the connection string "Default" in your application.

As for why this isn't working, my only guess is that it has something to do with the proxy we have. @mitchdenny ran into a similar issue and ended up using 127.0.0.1 instead of localhost.

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.

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

[tjementum]

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.

[davidfowl]

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

😄 Good! This is a helpful repo. I'll try to reproduce this on my M2.

Added a comment to your PR.

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.

Got it. Makes sense.

[stuartpa]

With Docker Desktop 4.25+, SQL Server does now run in docker on M1+. Does this help your repro?

https://www.brentozar.com/archive/2023/01/how-to-install-sql-server-and-the-stack-overflow-database-on-a-mac/

[stuartpa]

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+

[josephaw1022]

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?

[tjementum]

I've updated my solution to use the default SQL Server 2022. But I get the exact same error. I've pushed the changes to the main branch here.

I also got the simplified version of passing the connection string from the AppHost to the API (see this pull request). But now I get the error System.Net.Sockets.SocketException (22): Invalid argument.

See this screenshot:

The connection string works fine if I connect manually. But notice, the SQL server is running on a random port even though I've been explicit about port 1433. This seems like a different bug, as I have not been able to spin up the SQL Server on port 1433.

I believe I'm doing it all by the book now, and I'm not convinced it's a Mac problem anymore. Did anyone get SQL Server to work with Aspire? I have not been able to find any YouTube or blog post claiming that it works. I would be great if a Windows user could test the branch and report back if it works. Or point to a sample somewhere that shows that SQL Server works with Aspire.

[L-Sypniewski]

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).

[DamianEdwards]

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?

[tjementum]

I just tried to upgrade to .NET Aspire Preview 2 🎉, and I have the same problem.

In fact, I have this problem with the official Aspire Sample showcasing SQL Server.

Any workaround would be greatly appreciated.