Entity Framework Core migrations fail in GitHub Actions

I have a .NET 9 project that uses clean architecture approach to structure the solution. Within the project I am using Entity Framework Core to interact with the Azure SQL Server database. I am using code first approach with migrations to update the database. I am using Github actions to deploy the infrastructure and solution. All works fine except:

The issue:
When I try to update the database by creating a migrations bundle and running that bundle I get the following error in the GitHub pipeline:

Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user ''. 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.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, 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.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.OpenDbConnection(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.<>c__DisplayClass18_0.<Exists>b__0(DateTime giveUp)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.<>c__DisplayClass12_0`2.<Execute>b__0(DbContext _, TState s)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func`2 operation, Func`2 verifySucceeded)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists(Boolean retryOnNotExists)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists()
   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.Migrations.Design.MigrationsBundle.ExecuteInternal(String[] args)
   at Microsoft.EntityFrameworkCore.Migrations.Design.MigrationsBundle.<>c__DisplayClass6_0.<Configure>b__0(String[] args)
   at Microsoft.DotNet.Cli.CommandLine.CommandLineApplication.Execute(String[] args)
   at Microsoft.EntityFrameworkCore.Migrations.Design.MigrationsBundle.Execute(String context, Assembly assembly, Assembly startupAssembly, String[] args)
ClientConnectionId:1e9f14eb-5fd2-469a-922e-1aa64e2b3d58
Error Number:18456,State:1,Class:14
ClientConnectionId before routing:cbdfcc7f-f866-4dc7-9d63-80cba1f605cc
Routing Destination:e28e49f48a27.tr17032.uksouth1-a.worker.database.windows.net,11020
Login failed for user ''.
Error: Process completed with exit code 1.

Below is the relevent code:
Add EF Core to DI Container
The below extension method gets called in the Program.cs class.

public static class ConfigureDatabase
{
    public static IServiceCollection AddSqlServerDatabase(this IServiceCollection services, IConfiguration configuration)
    {
        var connectionString = configuration.GetConnectionString("Default");

        services.AddDbContext<BlogPlatformDBContext>(options => options.UseSqlServer(connectionString));

        return services;
    }
}    

DbContext.cs

public sealed class BlogPlatformDBContext : DbContext
{
    public BlogPlatformDBContext(DbContextOptions<BlogPlatformDBContext> dbContextOptions)
    : base(dbContextOptions)
    {
    }

    public DbSet<Post> Posts { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());

        base.OnModelCreating(modelBuilder);

    }
}    

SQL Server Bicep:

param sqlServerName string
param serverLocation string
@secure()
param sqlAdministratorLogin string
@secure()
param sqlAdministratorPassword string

resource sqlServer 'Microsoft.Sql/servers@2022-05-01-preview' = {
  name: sqlServerName
  location: serverLocation
  properties: {
    administratorLogin: sqlAdministratorLogin
    administratorLoginPassword: sqlAdministratorPassword
    minimalTlsVersion: '1.2'
  }
}

YAML to generate and run EF migrations bundle

- name: Generate EF Migrations Bundle
  run: |
      dotnet tool install --global dotnet-ef
      dotnet-ef migrations bundle --project src/KeenDev.BlogPlatform.Infrastructure/KeenDev.BlogPlatform.Infrastructure.csproj --startup-project src/KeenDev.BlogPlatform.API/KeenDev.BlogPlatform.API.csproj --output efbundle

- name: Run EF Migrations Bundle
  run: ./efbundle --connection ${{ steps.deploy-resources.outputs.sqlServerConnectionString }}    

Points to note

• I am able to connect to the database from local SQL Server Management Studio using same UserId and Password used in the connection string.
• I can see the connection string is being passed correctly in the GitHub log console.
• Azure SQL Server is NOT using Microsoft Entra ID authentication. Although the Entra ID Admin is a service prinicpal.

After enabling Azure SQL Server Auditing logs and looking at the AzureDiagnostics logs table I was able to find out the error code = 18456 and error state 122. Which when i looked at the Microsoft docs here says 'Failure due to empty user name or password'. The issue was with the connection string being directly passed to the shell. Passing the connection string via environment varible worked.

Did some research as to why it was failing when passing connection string directly via secrets or steps. It looks like if you have special cahracters in connection string the the shell (e.g., Bash) interprets special characters like @, $, &, etc. in ways that can break the connection string.

SOLUTION:

Replaced this:

- name: Run EF Migrations Bundle
  run: ./efbundle --connection ${{ steps.deploy-resources.outputs.sqlServerConnectionString }}    

With this:

- name: Run EF Migrations Bundle
  env:
      SQLSERVER_CONNECTIONSTRING: ${{ secrets.SQLSERVER_CONNECTIONSTRING }}
  run: ./efbundle --connection "$SQLSERVER_CONNECTIONSTRING"