sql-action does not appear to be working with SQL Server setup on an VM

[shs-jmic]

We are working on migrating SQL Project build and deployment pipelines to GitHub Actions. We are encountering issues with running deployment workflow using the sql-action (using Authentication='Active Directory Password') when deploying to SQL Server on a virtual machine that is in Azure. Please note we are able to successfully deploy to Azure SQL Server and Azure SQL managed instance with same user and authentication.

Only SQL authentication is working fine with SQL Server on VM.

The error I get when I use azure/sql-action@v2.3

`-------------------------------------------
Run azure/sql-action@v2.3

"C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\SqlPackage.exe" /Action:Publish "/TargetConnectionString:***" /SourceFile:./DeployArtifacts/.database.dacpac /Profile:./DeployArtifacts/.TOKEN.publish.xml
Publishing to database '' on server 'tcp:,1433'.
The dac history table will not be updated.
An unexpected failure occurred: Login failed for user ''.
Unhandled Exception: System.Data.SqlClient.SqlException: Login failed for user ''.
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.Data.Tools.Schema.Sql.Dac.ConnectionManager.<>c__DisplayClass1.b__0()
at Microsoft.Data.Tools.Schema.Common.SqlClient.RetryPolicy.<>c__DisplayClass4.b__3(RetryState retryState)
at Microsoft.Data.Tools.Schema.Common.SqlClient.RetryPolicy.ExecuteAction[R](Func2 func, Nullable1 token)
at Microsoft.Data.Tools.Schema.Common.SqlClient.RetryPolicy.ExecuteAction(Action1 action, Nullable1 token)
at Microsoft.Data.Tools.Schema.Common.SqlClient.RetryPolicy.ExecuteAction(Action action, Nullable1 token)
at Microsoft.Data.Tools.Schema.Sql.Dac.ConnectionManager.Create(String connectionString, Boolean cacheConnections)
at Microsoft.Data.Tools.Schema.Sql.Dac.DacRegistrationManager.DacInstancesManager.GetDacRegistrationData(String connectionString, String databaseName)
at Microsoft.Data.Tools.Schema.Sql.Dac.DacRegistrationManager.StartDeploy()
at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken, DacLoggingContext loggingContext)
at Microsoft.SqlServer.Dac.DacServices.Deploy(DacPackage package, String targetDatabaseName, Boolean upgradeExisting, DacDeployOptions options, Nullable1 cancellationToken)
at Microsoft.Data.Tools.Schema.CommandLineTool.DacServiceUtil.<>c__DisplayClass8.b__0(DacServices service)
at Microsoft.Data.Tools.Schema.CommandLineTool.DacServiceUtil.ExecuteDeployOperation(String connectionString, String filePath, MessageWrapper messageWrapper, Boolean sourceIsPackage, Boolean targetIsPackage, Func1 generateScriptFromPackage, Func2 generateScriptFromDatabase)
at Microsoft.Data.Tools.Schema.CommandLineTool.DacServiceUtil.DoDeployAction(DeployArguments parsedArgs, Action1 writeError, Action2 writeMessage, Action1 writeWarning, CancellationToken cancellationToken)
at Microsoft.Data.Tools.Schema.CommandLineTool.Program.DoDeployActions(CommandLineArguments parsedArgs)
at Microsoft.Data.Tools.Schema.CommandLineTool.Program.PerformAction(CommandLineArguments parsedArgs)
at Microsoft.Data.Tools.Schema.CommandLineTool.Program.Run(String[] args)
at Microsoft.Data.Tools.Schema.CommandLineTool.Program.Main(String[] args)
Error: The process 'C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\SqlPackage.exe' failed with exit code 3762504530
-------------------------------------------`

Just for testing purpose I also tried running on azure/sql-action@v1.3 but got different error

Error: Failed to add firewall rule. Unable to detect client IP Address. Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user '<username>'..
Connection string format being used to connect to the sql server on the virtual machine
Server=tcp:<>,1433;Initial Catalog=<>;Persist Security Info=False;User ID=<>;Password=<>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Authentication='Active Directory Password';

Am I doing anything wrong? Any help is appreciated.

Thanks and Regards
Sreenath