Aspire SqlServer integration doesn't actually create a database when calling AddDatabase

[jimitndiaye]

Is there an existing issue for this?

• I have searched the existing issues

Describe the bug

I create an SqlServer database as follows:

var builder = DistributedApplication.CreateBuilder(args);

var dbServer = builder.AddSqlServer("sql")
    .WithDataVolume()
    .WithLifetime(ContainerLifetime.Persistent);
var db = dbServer.AddDatabase("db");

builder.AddProject<Projects.Api>("api")
    .WithExternalHttpEndpoints()
    .WithReference(db)
    .WaitFor(db)
    .WithOtlpExporter();
builder.Build().Run();

Run the AppHost and access the api at https://localhost:7016/sql
The containers start as expected with all health checks showing green, but the app fails to connect to the database with an SQL exception saying it wasn't able to login to that specific database: Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'api-db'.

When I manually connect to the provisioned SQL Server container via Sql Server Management Studio I see that the database wasn't actually provisioned - in fact there are no databases other than the built-in ones.
This happens every time.

Expected Behavior

A database called db should be provisioned on the Sql Server instance and the Api

Steps To Reproduce

https://github.com/jimitndiaye/Aspire-Sql

Exceptions (if any)

Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot open database "db" requested by the login. The login failed.
Login failed for user 'sa'.
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, 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, Func3 accessTokenCallback)
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 Program.<>c.<<

$>b__0_1>d.MoveNext() in C:\repos\Scratch\Aspire-Sql\Api\Program.cs:line 41
--- End of stack trace from previous location ---
at Microsoft.AspNetCore.Http.RequestDelegateFactory.g__ExecuteAwaited|133_0(Task`1 task, HttpContext httpContext)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)
ClientConnectionId:b89aa583-4479-4ccd-befe-177524527384
Error Number:4060,State:1,Class:11

.NET Version info

9.0.100

Anything else?

Aspire Version: 9.0.0

[cx-seibel]

int sqlServerPort = 1433;
IResourceBuilder<SqlServerServerResource> sql = builder
    .AddSqlServer(name: "sql-server", port: sqlServerPort)
    .WithLifetime(ContainerLifetime.Persistent)
    .WithDataVolume()    
    .WithContainerRuntimeArgs("--name", "sql-server", "-p", "1433:1433")
    //.WithEndpoint(port: 1433, targetPort: 1433, ) does not work
    ;

IResourceBuilder<SqlServerDatabaseResource> db = sql
    .AddDatabase(name: "db", databaseName: "db")
    ;

You have to bind the port manually. Which should be out of the box. So I think it's a bug.
Providing sqlServerPort does solve the issue.

[petermorlion]

@cx-seibel I can't say adding that line fixes it. I have a simple enough Program.cs:

using Aspire.Hosting;

var builder = DistributedApplication.CreateBuilder(args);

var sql = builder.AddSqlServer("sql")
                 .WithLifetime(ContainerLifetime.Persistent)
                 .WithContainerRuntimeArgs("--name", "sql", "-p", "1433:1433"); // no difference with or without this

var db = sql.AddDatabase("database");

builder.AddProject<Projects.My_Project>("my-project")
    .WithReference(db)
    .WaitFor(db);

builder.Build().Run();

I can connect with SQL Server Management Studio, but don't see any databases:

[davidfowl]

Aspire doesn't create the database but this is a point of lots of confusion. We'll take another look at our database integrations and see if we can/should do this now.

[jimitndiaye]

Aspire doesn't create the database but this is a point of lots of confusion. We'll take another look at our database integrations and see if we can/should do this now.

I mean if you create a brand new SQL server container then call "AddDatabase" the expectation is that it would actually add a database, rather than just create a connection string to one. If it shouldn't create the database then maybe it should be called something different?

I haven't used the Postgresql integration but does that operate differently?

[davidfowl]

This isn't SQL Server specific, and yes its confusing and known. None of the database are auto created. This shows up as a big issue when you're not using existing tech that can create the db (like ef migrations)

[petermorlion]

Ah yes, that is confusing. But documented indirectly here. Maybe a future version could have an option to create the database? Would this be doable/desired? I'd be willing to look into creating a PR if so.

[davidfowl]

The database docs still need more work (see dotnet/docs-aspire#2020).

Yes, we have all of the primitives in place now to make this a reality. Just need to iterate on the design a bit.

[jimitndiaye]

In addition to updating the docs to indicate that the database needs to be manually created, the health checks for the database resource itself currently falsely stay green even if the connection string does not in fact work.

[Thovenaar]

int sqlServerPort = 1433;
IResourceBuilder<SqlServerServerResource> sql = builder
    .AddSqlServer(name: "sql-server", port: sqlServerPort)
    .WithLifetime(ContainerLifetime.Persistent)
    .WithDataVolume()    
    .WithContainerRuntimeArgs("--name", "sql-server", "-p", "1433:1433")
    //.WithEndpoint(port: 1433, targetPort: 1433, ) does not work
    ;

IResourceBuilder<SqlServerDatabaseResource> db = sql
    .AddDatabase(name: "db", databaseName: "db")
    ;

You have to bind the port manually. Which should be out of the box. So I think it's a bug. Providing sqlServerPort does solve the issue.

This worked for me. I had to remove the "--name", "sql-server" part though. It seems to conflict with the naming convention of .WithLifetime(Persistent).

Keeping both lines in would throw an exception mentioning that the name was already in use by another container.

[davidfowl]

This will be in 9.2. During development an empty database will be created.