DacServices Deployment of dacpac to Azure SQL Database from Windows Powershell fails with error: 0 - An existing connection was forcibly closed by the remote host.

Question

Hi Team,

Trying to deploy dacpac from Windows Powershell using Dacservices with a service principal to Azure sql database.

The service principal has contributor rights on the sql server. In the sql server (dbmanager and loginmanager on the masterDB , db_owner on the target database).The access token is getting generated correctly and is not expired.

The firewall rule on the Azure sql server side allows connections from the local machine.

The deployment fails with the below error :

A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
Initializing deployment (Failed)

The exception details are as below :
Exception : System.Management.Automation.MethodInvocationException: Exception calling "Deploy" with "4" argument(s): "Could not deploy package." ---> Microsoft.SqlServer.Dac.DacServicesException: Could not deploy package. --->
Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentFailedException: Unable to connect to target server 'xxxx.database.windows.net'. Please verify the connection information such as the server name, login credentials, and firewall
rules for the target server. ---> System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An existing connection was
forcibly closed by the remote host.) ---> System.ComponentModel.Win32Exception: An existing connection was forcibly closed by the remote host
--- End of inner exception stack trace ---
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.Common.SqlClient.ReliableSqlConnection.<OpenConnection>b__0()
at Microsoft.Data.Tools.Schema.Common.SqlClient.RetryPolicy.<>c__DisplayClass4.<ExecuteAction>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.Common.SqlClient.ReliableSqlConnection.OpenConnection() at Microsoft.Data.Tools.Schema.Common.SqlClient.ReliableConnectionHelper.OpenConnection(SqlConnectionFactory connectionFactory, Boolean useRetry) at Microsoft.Data.Tools.Schema.Common.SqlClient.ReliableConnectionHelper.OpenConnection(SqlConnectionFactory connectionFactory, Action1 usingConnection, Predicate1 catchException, Boolean useRetry) at Microsoft.Data.Tools.Schema.Common.SqlClient.ReliableConnectionHelper.GetServerAndDatabaseVersion(SqlConnectionFactory connectionFactory, ServerAndDatabaseInfo& info) at Microsoft.Data.Tools.Schema.Sql.SqlDatabaseSchemaProviderFamily.DetermineSqlPlatform(SqlConnectionFactory connectionFactory, CompatibilityLevelVerification verificationMode, String& errorMessage) at Microsoft.Data.Tools.Schema.Sql.SqlDatabaseSchemaProviderFamily.DetermineDatabaseSchemaProvider(SqlConnectionFactory connectionFactory, String& errorMessage) at Microsoft.Data.Tools.Schema.Sql.SqlDatabaseSchemaProviderFamily.DetermineDatabaseSchemaProvider(SqlConnectionFactory connectionFactory) at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentEndpointServer.GetProviderType(DatabaseSchemaProviderFamily connectionSniffer, SqlConnectionFactory connectionFactory, Type& providerType, Boolean& validDataSource, SqlException& exception) --- End of inner exception stack trace --- at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentEndpointServer.OnInit(ErrorManager errors, String targetDBName, Nullable1 platform)
at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment..ctor(SqlDeploymentConstructor constructor)
at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentConstructor.ConstructServiceImplementation()
at Microsoft.SqlServer.Dac.DacServices.CreatePackageToDatabaseDeployment(SqlConnectionFactory connectionFactory, IPackageSource packageSource, String targetDatabaseName, DacDeployOptions options, ErrorManager errorManager)
at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass3.<>c__DisplayClass5.<CreatePlanInitializationOperation>b__1()
at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action)
at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass3.<CreatePlanInitializationOperation>b__0(Object operation, CancellationToken token)
at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken)
at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken, DacLoggingContext loggingContext, Action3 reportPlanOperation, Boolean executePlan) at Microsoft.SqlServer.Dac.DacServices.Deploy(DacPackage package, String targetDatabaseName, Boolean upgradeExisting, DacDeployOptions options, Nullable1 cancellationToken)
at CallSite.Target(Closure , CallSite , Object , Object , Object , Boolean , Object )
--- End of inner exception stack trace ---
at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)

Answer 1

Does your connection string contains

TrustServerCertificate=True

If not, can you try adding it and run your deployment again

Thanks!

Answer 2

Hello @Bajaj, Smita

This is usually related to the encryption level of the connection.

Please try the next setting on the SQL server:

Disable "Force Encryption" setting on SQL Server

Expand SQL Server Network Configuration and right-click on Protocols for , then click Properties.
On the Certificate tab, unselect certificate.
Select No in the ForceEncryption box, then click OK.

Hope this helps with your query,

--
--If the reply is helpful, please Upvote and Accept as answer--