SQL Server component, volume mount and data persistence between sessions

[enrico23]

Hi everyone 🙂,
Before NET Aspire became mainstream last year, I used to combine Docker files, Docker Compose files, Fluent Docker, and Bash scripts to spin up all the dependencies required by my applications. And I thought about replacing my bespoke automated workloads with Aspire 🚀.

I was trying to replace one of my workloads using the following example: https://github.com/dotnet/aspire-samples/tree/main/samples/DatabaseContainers and adapted some of the scripts in the example for my requirements.

My requirements:

• run database initialization scripts (create database and a database user if they don't exist)
• persist data between sessions

Following a few articles online I changed the script to use a persistent SA password, to specify a port for the container (this to avoid conflict with other containers), and pass specific environment variables (these are used in the database initialization script).

References:

• https://learn.microsoft.com/en-us/dotnet/aspire/fundamentals/persist-data-volumes
• https://github.com/dotnet/aspire-samples/blob/main/samples/DatabaseContainers/DatabaseContainers.AppHost/Program.cs

This is how I adapted the program class on the Aspire App Host:

// Aspire App Host - Program class
using Microsoft.Extensions.Configuration;

var builder = DistributedApplication.CreateBuilder(args);
var configuration = builder.Configuration;

var databaseName = "testDb";
var mssqlUser = configuration.GetValue<string>("MSSQL__USER") ?? throw new AggregateException("A value for MSSQL_USER is required");
var mssqlPassword = configuration.GetValue<string>("MSSQL_PASSWORD")  ?? throw new AggregateException("A value for MSSQL__PASSWORD is required");

var sqlPassword = builder.AddParameter("sql-password", secret: true);
var db =
    builder.AddSqlServer(name: "sqlserver", port: 14343, password: sqlPassword)
        // pass required values for the database initialization script
        .WithEnvironment(context => context.EnvironmentVariables.Add("MSSQL_DB", databaseName))
        .WithEnvironment(context => context.EnvironmentVariables.Add("MSSQL_USER", mssqlUser))
        .WithEnvironment(context => context.EnvironmentVariables.Add("MSSQL_PASSWORD", mssqlPassword))
        // Mount the init scripts directory into the container.
        .WithBindMount("./sqlserverconfig", "/usr/config")
        // Mount the SQL scripts directory into the container so that the init scripts run.
        .WithBindMount("./mssql/data", "/docker-entrypoint-initdb.d")
        // Run the custom entrypoint script on startup.
        .WithEntrypoint("/usr/config/entrypoint.sh")
        // persists data between sessions in volume
        .WithDataVolume()
        // Add the default database to the application model so that it can be referenced by other resources.
        .AddDatabase(databaseName);
        
  // other code removed for brevity

This approach doesn't work 😢

Data persistence works, but I am not able to run the scripts anymore.

By looking at the logs on the SQL Server instance, I can see that the scripts timed out.

The problem can be possibly:

Login failed for user 'sa'. Reason: Password did not match that for the login provided.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'sa'

Did anyone have a similar issue?

[enrico23]

I think that I solved it by tweaking my initialization.

I changed it from:

 .WithDataVolume()

to:

.WithDataVolume(name: dataVolumeName, isReadOnly: false)

🤔 why it works this way

[roman-lemko]

For anyone stumbling upon this problem in the future.

It's likely that your old password was stored in your data volume.
So the reason it worked when you added a named data volume is that you created a whole new data volume with new password 🙂

One more way to solve this is to find the data volume that was created by Aspire for your SQL Server and delete it. Warning: this will, of course, wipe all your local test data, so proceed with caution.

More info:
microsoft/mssql-docker#301

[roman-lemko]

Ok, so I spent quite a while trying to figure this one out. And it's ...weird.

While removing the data volume worked for me -- it only worked once.
I had to either disable .WithDataVolume() or remove the data volume for every run, which is far from perfect.

Now, the way Aspire makes sure the password remains the same across app starts is that it stores a user secret in dotnet user-secrets with the sql password. You can see it by running dotnet user-secrets list in the Aspire.AppHost project folder.
IN THEORY Aspire should check if the secret is there and use that password to set an environment variable for all the services to be able to use it. HOVEWER in my specific case it was overwriting the secret on every single run with a new random password. 🥲

I tried setting the secret manually as per docs, removing volumes, making the secrets file readonly and renaming volumes to different things.

But the only thing that eventually helped was removing the user secret for Aspire to set it again. Then drop the volume in Docker. After that it stopped overwriting the secret and started using the one it set for all consequent runs.

dotnet user-secrets remove

Hope this helps somebody solve this weird issue.

[lopezbertoni]

Same issue. I updated the aspire workload and suddenly the database connection stopped working. I had to delete the container and the volumes for this to work again. I'm using Docker Desktop by the way.

[jrunestone]

I have this problem too but running in a devcontainer. When I rebuild the devcontainer the SQL container starts reporting Login failed because a new random password was generated by Aspire. What I can't understand is, where is this old password stored? When I rebuild the devcontainer everything is wiped (nothing should be stored on my local machine...).

If I manually edit the secrets.json in the usersecrets store to the old password before I rebuilt, it starts working.

[davidfowl]

What I can't understand is, where is this old password stored?

If you are using a volume it's stored in the volume.

[jrunestone]

Ok what I couldn't figure out is why the volume wasn't wiped too when rebuilding, but I guess docker-in-docker is just a client and the volumes are actually stored on the host

[davidfowl]

That's right.