Connectionstring via CLI for database update

Question

I'm learning about multi-tenancy, where each tenant has its own database.

The idea is by using the finbuckle NuGet package, it gets the connection string from the appsettings.development.json.

but for creating migrations and updating the database, the connection string has to be passed as an argument.

My database is via Docker with the command:

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=myPassword" -p 1433:1433 --name sqlTest--hostname sqlTest -d mcr.microsoft.com/mssql/server:2022-latest

and using Azure Data Studio, I successfully connected to the database.

whenever I want to use the command:

dotnet ef database update -- --connection-string "Server=localhost,1433; Database=test; User Id=SA; Password=myPassword"

I get the error:

System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.
   at Microsoft.Data.Common.DbConnectionOptions.GetKeyValuePair(String connectionString, Int32 currentPosition, StringBuilder buffer, Boolean useOdbcRules, String& keyname, String& keyvalue)
   at Microsoft.Data.Common.DbConnectionOptions.ParseInternal(Dictionary`2 parsetable, String connectionString, Boolean buildChain, Dictionary`2 synonyms, Boolean firstKey)
   at Microsoft.Data.Common.DbConnectionOptions..ctor(String connectionString, Dictionary`2 synonyms)
   at Microsoft.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
   at Microsoft.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key)
   at Microsoft.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
   at Microsoft.Data.SqlClient.SqlConnection..ctor(String connectionString)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.CreateDbConnection()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.get_DbConnection()
   at Microsoft.EntityFrameworkCore.Diagnostics.RelationalLoggerExtensions.MigrateUsingConnection(IDiagnosticsLogger`1 diagnostics, IMigrator migrator, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Format of the initialization string does not conform to specification starting at index 0.

I already tried multiple connection string formats, like putting the IP instead of localhost and adding the port to the server name. But I always end up with the same error, and I'm not sure where to go from here.

Answer 1

I have played with your commands and I assume you are using Windows OS as I do. I found a few things in your attempt and here is my answer.

1. In your connection string as this answer mentions you need to use 127.0.0.1\sqlTest as server name.
2. In your dotnet command, you are using -- --connection-string which I think is a typo or something is not correct, check this answer Command line connection string for EF core database update.
3. Finally I got an issue with A connection was successfully established with the server, but then an error occurred during the login process. (Error Number: 233) and solved it by adding TrustServerCertificate=True to the connection string.
4. I used another password like Qwer!234 instead of myPassword otherwise in docker the MSSQL server will return an error like: ERROR: Unable to set a 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

So here are all the steps I did to make it work (my project with EF is named WebApplication1):

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Qwer!234" -p 1433:1433 --name sqlTest --hostname sqlTest -d mcr.microsoft.com/mssql/server:2022-latest

dotnet ef migrations add InitialCreate --project .\WebApplication1.csproj

dotnet ef database update --connection "Server=127.0.0.1\sqlTest,1433; User Id=sa; Password=Qwer!234; Database=test; TrustServerCertificate=True"

And connection string to Azure Data Studio is, the first time without a Database name because it has not been created yet:

Server=127.0.0.1\sqlTest,1433; User Id=sa; Password=Qwer!234;

But when a database is created you can use

Server=127.0.0.1\sqlTest,1433; User Id=sa; Password=Qwer!234; Database=test

Here is the screenshot of the results:

And Azure Data Studio: