Aspire SQL component connection fails when running a query

[alexwolfmsft]

When I configure the Aspire SQL component using the steps in the readme, the connection fails when attempting to execute a query with this error in the C# code:

SqlException: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.)

I also see this error in the container logs: ERROR: Unable to set system administrator password: Password validation failed. The password does not meet SQL Server password policy requirements because it is not complex enough. The password must be at least 8 characters long and contain characters from three of the following four sets: Uppercase letters, Lowercase letters, Base 10 digits, and Symbols..

I have attempted to resolve this issue by deleting and repulling the latest image/container, and by adding Trusted_Connection=true to the connection string. @eerhardt was also able to reproduce this issue.

Apphost code:

var builder = DistributedApplication.CreateBuilder(args);

var sql = builder.AddSqlServerContainer("sql").AddDatabase("sqldata");

var myService = builder.AddProject<Projects.AspireSQL>("aspiresql")
                       .WithReference(sql);

builder.Build().Run();

Web app code:

builder.AddSqlServerClient("sqldata");

Sample queries:

@inject SqlConnection connection

@code {
  protected override async Task OnInitializedAsync()
  {
      connection.Open();
      using(SqlCommand command = new SqlCommand("CREATE TABLE Persons (ID int NOT NULL PRIMARY KEY IDENTITY, FirstName varchar(255), LastName varchar(255));", connection))
      {
          command.ExecuteNonQuery();
      }
  }
}

    var c = _serviceProvider.CreateScope().ServiceProvider.GetRequiredService<SqlConnection>();

    _logger.LogCritical($"got {c}");

    c.Open();
    using (var cmd = new SqlCommand("select 1", c))
    {
        cmd.ExecuteScalar();
    }

@mitchdenny are you able to assist with this?

[DamianEdwards]

I think this is a dupe of #971 which is already fixed for preview.2

Can you try using latest daily builds of Aspire packages/workload?

[eerhardt]

I already have that fix locally and am still seeing the same error @alexwolfmsft shows above. But you need to actually issue a command:

        var c = _serviceProvider.CreateScope().ServiceProvider.GetRequiredService<SqlConnection>();

        _logger.LogCritical($"got {c}");

        c.Open();
        using (var cmd = new SqlCommand("select 1", c))
        {
            cmd.ExecuteScalar();
        }

[eerhardt]

Note also this is a different error message than #971

      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 - An existing connection was forcibly closed by the remote host.)
       ---> System.ComponentModel.Win32Exception (10054): An existing connection was forcibly closed by the remote host.
         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)
Unhandled 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 - An existing connection was forcibly closed by the remote host.)
 ---> System.ComponentModel.Win32Exception (10054): An existing connection was forcibly closed by the remote host.
   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.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at OrderProcessor.OrderProcessingWorker.ExecuteAsync(CancellationToken stoppingToken) in C:\git\aspire2\samples\eShopLite\OrderProcessor\OrderProcessingWorker.cs:line 31
   at Microsoft.Extensions.Hosting.BackgroundService.StartAsync(CancellationToken cancellationToken)
   at Microsoft.Extensions.Hosting.Internal.Host.<StartAsync>b__15_1(IHostedService service, CancellationToken token)
   at Microsoft.Extensions.Hosting.Internal.Host.ForeachService[T](IEnumerable`1 services, CancellationToken token, Boolean concurrent, Boolean abortOnFirstException, List`1 exceptions, Func`3 operation)
   at Microsoft.Extensions.Hosting.Internal.Host.StartAsync(CancellationToken cancellationToken)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.Run(IHost host)
   at Program.<Main>$(String[] args) in C:\git\aspire2\samples\eShopLite\OrderProcessor\Program.cs:line 12
ClientConnectionId:dc0eeb32-a416-4250-abba-2756272129f9
Error Number:10054,State:0,Class:20
         at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
         at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
         at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
         at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
         at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
         at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
         at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
         at Microsoft.Data.SqlClient.SqlConnection.Open()
         at OrderProcessor.OrderProcessingWorker.ExecuteAsync(CancellationToken stoppingToken) in C:\git\aspire2\samples\eShopLite\OrderProcessor\OrderProcessingWorker.cs:line 31
         at Microsoft.Extensions.Hosting.BackgroundService.StartAsync(CancellationToken cancellationToken)
         at Microsoft.Extensions.Hosting.Internal.Host.<StartAsync>b__15_1(IHostedService service, CancellationToken token)
         at Microsoft.Extensions.Hosting.Internal.Host.ForeachService[T](IEnumerable`1 services, CancellationToken token, Boolean concurrent, Boolean abortOnFirstException, List`1 exceptions, Func`3 operation)
      ClientConnectionId:dc0eeb32-a416-4250-abba-2756272129f9
      Error Number:10054,State:0,Class:20

[DamianEdwards]

Can you try running the sample I added to dotnet/aspire-samples this week?

[eerhardt]

I ran the sample at https://github.com/dotnet/aspire-samples/tree/main/samples/VolumeMount and that worked.

Then I went back to my repro app. I was executing a command at startup of OrderProcessor, which was failing. I'm assuming because the DB server hadn't started up yet (which sort of makes sense given the error message).

I moved the code to be on a button on the website, and that worked to connect to the DB and execute a command.

@alexwolfmsft is this the same problem you are having?

[alexwolfmsft]

@eerhardt and @DamianEdwards a few things here:

I was able to get the volumemount sample working, and then I retrofitted that setup back onto a simple out of the box solution like the one in my original issue description. I was able to get it working, but with some caveats:

1) The sql server container never works properly for me unless you manually create a password and pass it in, even though its an optional parameter. Unless you define your own password, you always get this on container startup:

ERROR: Unable to set system administrator password: Password validation failed. The password does not meet SQL Server password policy requirements because it is not complex enough. The password must be at least 8 characters long and contain characters from three of the following four sets: Uppercase letters, Lowercase letters, Base 10 digits, and Symbols..

Shouldn't the default behavior set a secure password to keep that parameter optional? If I set my own stronger password in user secrets and pass it into the method, this issue is resolved using the following code:

// Set in user-secrets
var sqlpassword = builder.Configuration["sqlpassword"]; 

var sql = builder.AddSqlServerContainer("sql", sqlpassword).AddDatabase("sqldata");

2) I was only able to connect to the database after creating it manually inside the container using the sqlcmd tool that comes with the image (or through a sql query). This makes sense, but I wanted to confirm what the intended workflow here is with and without entity framework migrations. I'm not using EF in my example. Based on the volumemount sample - so the assumption is that the database should be stored on a shared volume if the user wants to persist the database between app sessions? The container seems to be destroyed when you stop the app, so you lose the database every time - if they don't want to use a shared volume, they would have to run a script or migration every time the app launches to recreate/seed the database? Just looking for a confirmation of the intended workflow here for docs purposes.

[DamianEdwards]

The SQL generated password issue is already fixed for preview 2.

Correct on the point about volumes and managing the state. In our samples that use a database and EF, we either have code that ensures the database is updated on each launch, or we use a persistent volume and rely on the EF Core middleware to provide the developer a UX for running migrations the first time.

[alexwolfmsft]

The SQL generated password issue is already fixed for preview 2.

Correct on the point about volumes and managing the state. In our samples that use a database and EF, we either have code that ensures the database is updated on each launch, or we use a persistent volume and rely on the EF Core middleware to provide the developer a UX for running migrations the first time.

Thanks, I remember now that the first one was addressed by https://github.com/dotnet/aspire/issues/971. We can put together some guidance for this flow for the db components.

[alexwolfmsft]

Following up after more experimentation - is there a sample somewhere showing how to initially create the database in the container if you're not using Entity Framework? It seems like the injected SqlConnection is already setup to point to a specific database, in this case aspiredb:

var sql = builder.AddSqlServerContainer("sql", sqlpassword).AddDatabase("aspiredb");

The injected SqlConnection works if the database already exists in the container, but it seems as though you can't use an injected SqlConnection to actually create the database if it doesn't already exist because its already pointing to a specific db. Potential work around might be to run a separate startup script with a different connection string - is there an example of how to handle this scenario?

Maybe I am misunderstanding something here.

[DamianEdwards]

Following up after more experimentation - is there a sample somewhere showing how to initially create the database in the container if you're not using Entity Framework?

Nope. Today all our samples rely on EF to create the database if it doesn't exist already.

[asztinubu]

@alexwolfmsft In your sample, you mention for Web app code:

builder.AddSqlServerClient("sqldata")

However, I noticed that AddSqlServerClient is an extension method for IHostApplicationBuilder. Did you manually revert the template's default WebApplicationBuilder, to use the older IHostApplicationBuilder.

[DamianEdwards]

IHostApplicationBuilder was a new API introduced in .NET 8. I think you might be confusing it with IHostBuilder?

[asztinubu]

Right. I shouldn't have said "old". But my question is, in the aspire template, the web app is initialized with WebApplicationBuilder. So to use AddSqlServerClient, should we switch it to use IHostApplicationBuilder. Shouldn't there be an extension extension method for adding Sql Client to WebApplicationBuilder?

[DamianEdwards]

Yes, WebApplicationBuilder implements IHostApplicationBuilder, so you should see the AddSqlServerClient method on it if you've referenced the Aspire.Microsoft.Data.SqlClient package.

[DamianEdwards]

Note we have a sample now that shows how to configure the various supported database containers to be initialized using their integrated features: https://github.com/dotnet/aspire-samples/tree/main/samples/DatabaseContainers

[mitchdenny]

Given the original issue reported here was about password generation for the SQL resource type, and that was resolved in preview 2 ... we can go ahead and close this issue unless it is still a problem?

[mitchdenny]

Closing ... reopen if necessary.