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
- Replace retired Azure SQL Edge with SQL Server for development and use Aspire AppHost to replace Docker Compose (#293) ### Summary & Motivation Modify the development environment to utilize the fu... — committed to platformplatform/PlatformPlatform by tjementum 3 years ago
- Use dbContext.Database.CreateExecutionStrategy when migrating data https://github.com/dotnet/aspire/issues/1023#issuecomment-1904481448 — committed to tjementum/AspireWithSqlServer by tjementum 3 years ago
- Replace retired Azure SQL Edge with SQL Server for development and use Aspire AppHost to replace Docker Compose (#293) ### Summary & Motivation Modify the development environment to utilize the fu... — committed to Smurf-Engineer/Platform by tjementum 3 years ago
Here’s my workaround for macOS - use the built-in strategy on startup but extending it with an additional error number
0which I receive on mac when SQL Server container is not ready: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?
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
mainandchange-to-ip-and-let-aspire-host-pass-connection-stringbranches fail withMicrosoft.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).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 ?
This is the similar to the workaround I put in the samples https://github.com/dotnet/aspire-samples/blob/98e9c0eea6f8111ef3e4d45546d098b9de61ea65/samples/Shared/RetryingSqlServerRetryingExecutionStrategy.cs
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
0error 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
0with this message:Then I updated the retry logic in EF to handle
0also and @tjementum 's hack was not required anymore. I’ll let the EF team be judge of how to interpret error0but 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
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 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
Unfortunately the
AfterEndpointsAllocatedAsynclifecycle 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:
AfterEndpointsAllocatedAsynclifecycle hook like I do in #2090, but start a background thread or task to retry and wait applying the migrations.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:
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)System.Net.Sockets.SocketException (22): Invalid argumentThis 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.
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?
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. 😄
I’ve tried switching from localhost to 127.0.0.1, but I’m afraid I still facing the same error.
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.
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.