EF Core migrations on a new SQL Server

Question

I'm trying to build a Migration console app, that is able to start from scratch, i.e. on a freshly installed SQL Server, including a dedicated user with credentials, which should be the db_owner.

I have it working on PostgreSQL, but need to get a similar solution for SQL Server.

Before I call context.Database.Migrate() , I'm calling CheckDatabaseCreated(context.Database, configuration) which basically does this:

1. Tries to connect with given connectionstring

2. If it fails it replaces user, and password with SA and SA password and connects to master.

3. Creates the login, if it does not exist.

4. Creates the database, if it does not exist.

5. Connects to the newly created database - still as SA.

6. Creates the user with the login, and adds db_owner role.

7. Finally it tries again to connect using the original connectionstring - this is where it fails.

Using HeidiSQL, I can see that the database is created, but I can only connect using SA credentials.

    private static void CheckDatabaseCreated(DatabaseFacade contextDatabase, IConfiguration configuration)
    {
        bool canConnect;
        try
        {
            canConnect = contextDatabase.CanConnect();
            Console.WriteLine("Database connected succesfully.");
        }
        catch (Exception e)
        {
            Console.WriteLine($"Unable to connect to database: {e.Message}");
            canConnect = false;
        }
        if (!canConnect)
        {
            var builder = new SqlConnectionStringBuilder(configuration["ConnectionString"]);
            var originalUser = builder.UserID;
            var originalPassword = builder.Password;
            var originalDatabase = builder.InitialCatalog;
            builder.UserID = _masterUsername;
            builder.Password = _masterPassword;
            builder.InitialCatalog = "master";
            var login = $"{originalUser}Login";

            SqlConnection conn = new SqlConnection(builder.ConnectionString);
            try
            {
                conn.Open();
                // Check if login exists
                SqlCommand command = new SqlCommand($"SELECT COUNT(*) FROM master.sys.server_principals WHERE name = '{login}'", conn);
                object result = command.ExecuteScalar();
                result = (result == DBNull.Value) ? null : result;
                if (Convert.ToInt32(result) < 1)
                {
                    Console.WriteLine("Login does not exist - creating.");
                    command = new SqlCommand($"CREATE LOGIN [{login}] WITH PASSWORD = N'{originalPassword}', CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF", conn);
                    command.ExecuteNonQuery();
                }
                // Check if database exists
                command = new SqlCommand($"SELECT COUNT(*) FROM master.sys.databases WHERE name = '{originalDatabase}'", conn);
                result = command.ExecuteScalar();
                result = (result == DBNull.Value) ? null : result;
                if (Convert.ToInt32(result) < 1)
                {
                    Console.WriteLine("Database does not exist - creating.");
                    command = new SqlCommand($"CREATE DATABASE \"{originalDatabase}\" ", conn);
                    command.ExecuteNonQuery();
                }
                conn.Close();
                // Now connect to the (newly created) database - still as sa.
                builder.InitialCatalog = originalDatabase;
                conn = new SqlConnection(builder.ConnectionString);
                try
                {
                    conn.Open();
                    command = new SqlCommand($"CREATE USER [{originalUser}] FOR LOGIN [{login}]", conn);
                    command.ExecuteNonQuery();

                    command = new SqlCommand($"EXEC sp_addrolemember 'db_owner', '{originalUser}'", conn);
                    command.ExecuteNonQuery();

                    conn.Close();
                }
                catch (Exception e)
                {
                    Console.WriteLine($"Unable to connect to {originalDatabase} database: {e.Message}");
                }
                // Finally try to connect as the user created above.
                builder = new SqlConnectionStringBuilder(configuration["ConnectionString"]);
                conn = new SqlConnection(builder.ConnectionString);
                try
                {
                    conn.Open();
                }
                catch (Exception e)
                {
                    // This is where it fails.
                    Console.WriteLine($"Unable to connect to database: {e.Message}");
                }
            }
            catch (Exception e)
            {
                Console.WriteLine($"Unable to connect to database: {e.Message}");
            }
        }
    }

Answer 1

The User ID in a SQL Server connection string refers to a Login, or a Contained Database User.

So your problem is here:

   var login = $"{originalUser}Login";

This login is not the one referenced in your connection string. There's no reason the Login and Database User need different names. So jut make them the same:

   var login = originalUser;