Blog Post

Azure SQL Blog

3 MIN READ

Connection Security Improvements in SqlPackage 161

Microsoft

Nov 09, 2022

In version 161 of SqlPackage and DacFx some default connection settings have changed to improve the default security of database connections. In this article we will focus on these changes and what modifications you may need to make to commands you use with SqlPackage.

Starting with version 161 of DacFx and SqlPackage, database connections are encrypted by default and server certificates must be signed by a recognized certificate authority. As a result, you may need to adjust additional options even if you have successfully connected to a server with previous versions of SqlPackage or DacFx.

Connection encryption by default

The follow are some command line parameters that can be leveraged to alter a database connection:

/SourceEncryptConnection (default True)
/SourceTrustServerCertificate (default False)
/TargetEncryptConnection (default True)
/TargetTrustServerCertificate (default False)

Information about all the parameters and properties available to use with SqlPackage are detailed in the documentation: https://aka.ms/sqlpackage-ref

If your SqlPackage 161 commands are failing to connect, the server may not have encryption enabled or the configured certificate may not be issued from a trusted certificate authority (such as a self-signed certificate). Using the command line parameters listed above, you can change the SqlPackage command to either connect without encryption or to trust the server certificate. The best practice is to ensure that a trusted encrypted connection to the server can be established.

Connect without encryption: /SourceEncryptConnection:False or /TargetEncryptConnection:False
Trust server certificate: /SourceTrustServerCertificate:True or /TargetTrustServerCertificate:True

New warning messages

Warning or error messages have been added to SqlPackage related to these changes. You may see any of the following warning messages when connecting to a SQL instance, indicating that command line parameters may require changes to connect to the server:

The settings for connection encryption or server certificate trust may lead to connection failure if the server is not properly configured.

The connection string provided contains encryption settings which may lead to connection failure if the server is not properly configured.

Examples

An example SqlPackage Import command to a development server on localhost that will use encryption (default) and trust the server’s certificate, potentially accepting a self-signed or unrecognized certificate:

SqlPackage /Action:Import /TargetServerName:"localhost" /TargetDatabaseName:"AdventureWorksLT" /TargetUser:"your_username" /TargetPassword:"your_password" /TargetTrustServerCertificate:True /SourceFile:"C:\AdventureWorksLT.bacpac"

An example SqlPackage Publish command to a development server on localhost that will not use encryption:

SqlPackage /Action:Publish /TargetServerName:"localhost" /TargetDatabaseName:"AdventureWorksLT" /TargetUser:"your_username" /TargetPassword:"your_password" /TargetEncryptConnection:False /SourceFile:"C:\AdventureWorksLT.dacpac"

The updated secure defaults also apply to SqlPackage commands that have connection string input. The example command below would connect with encryption and not trust the server certificate:

SqlPackage /Action:Publish /TargetConnectionString: Server=localhost;Database=AdventureWorksLT;User Id=your_username;Password=your_password;” /SourceFile:”C:\AdventureWorksLT.dacpac”

More information

These changes are the result of updates at the driver level in Microsoft.Data.SqlClient. Recent releases of Microsoft.Data.SqlClient have offered increased security in the connection options. Read more about these changes in the release notes for Microsoft.Data.SqlClient.

If you have previously been connecting to a SQL Server that does not have encrypted connections enable and would like to enable encryption, more information on the steps to do so is available in the SQL Server documentation.

Updated Nov 18, 2022

Version 2.0

azure sql

sql server

DrewSkwiersKoballa

Microsoft

Joined June 17, 2020

View Profile

Azure SQL Blog

Follow this blog board to get notified when there's new activity

15 Comments

racosgon
Copper Contributor
Nov 19, 2022
I have the same problem as michaelt05, any suggestions?
I am deploying .dapac with Azure DevOps, to a Virtual Machine in Azure, the server of Sql Server developer 2019
DrewSkwiersKoballa
Microsoft
Nov 18, 2022
michaelt05 For further troubleshooting assistance I think you’ll get better help at https://github.com/microsoft/DacFx
DrewSkwiersKoballa
Microsoft
Nov 18, 2022
UmeshPandit I apologize for the confusion, I had a syntax error in the post. The argument parameter /SourceTrustServerCertificate:True would use a colon instead of an equals sign.

michaelt05

Copper Contributor

Nov 18, 2022

Not sure how to solve this error

2022-11-18T17:37:44.0988600Z ##[section]Starting: SQL DB Deploy
2022-11-18T17:37:44.1275271Z ==============================================================================
2022-11-18T17:37:44.1276412Z Task         : SQL Server database deploy
2022-11-18T17:37:44.1277423Z Description  : Deploy a SQL Server database using DACPAC or SQL scripts
2022-11-18T17:37:44.1278258Z Version      : 0.198.0
2022-11-18T17:37:44.1278915Z Author       : Microsoft Corporation
2022-11-18T17:37:44.1280125Z Help         : https://docs.microsoft.com/azure/devops/pipelines/tasks/deploy/sql-dacpac-deployment-on-machine-group
2022-11-18T17:37:44.1281650Z ==============================================================================
2022-11-18T17:37:52.3600701Z *** Could not deploy package.
2022-11-18T17:37:52.3605929Z Changes to connection setting default values were incorporated in a recent release.  More information is available at https://aka.ms/dacfx-connection
2022-11-18T17:37:52.3609312Z Unable to connect to target server 'WIN-SERVER1-LAR'. Please verify the connection information such as the server name, login credentials, and firewall rules for the target server.
2022-11-18T17:37:52.3613093Z A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
2022-11-18T17:37:52.3616194Z The certificate chain was issued by an authority that is not trusted
2022-11-18T17:37:52.7425545Z ##[error]Publishing to database 'MarketplaceDev' on server 'WIN-SERVER1-LAR'. 
Initializing deployment (Start) 
Initializing deployment (Failed) 
*** Changes to connection setting default values were incorporated in a recent release.  More information is available at https://aka.ms/dacfx-connection 
*** The settings for connection encryption or server certificate trust may lead to connection failure if the server is not properly configured. 
Time elapsed 0:00:02.71 

2022-11-18T17:37:52.7816299Z ##[section]Finishing: SQL DB Deploy

UmeshPandit
MCT
Nov 17, 2022
Error: C:\Temp\sqlpackage-win7-x64-en-16.1.6374.0>SqlPackage.exe /a:import /sf:C:\Temp\Testbackup.bacpac /tsn:localhost /tdn:Testbackup /p:CommandTimeout=1200
Importing to database 'Testbackup' on server 'localhost'.
Creating deployment plan
Initializing deployment
*** Changes to connection setting default values were incorporated in a recent release. More information is available at https://aka.ms/dacfx-connection
*** Error importing database:Could not import package.
Changes to connection setting default values were incorporated in a recent release. More information is available at https://aka.ms/dacfx-connection
Unable to connect to target server 'localhost'. Please verify the connection information such as the server name, login credentials, and firewall rules for the target server.
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
The certificate chain was issued by an authority that is not trusted.
*** The settings for connection encryption or server certificate trust may lead to connection failure if the server is not properly configured.
Time elapsed 0:00:03.82

I followed your blog however its failed:

C:\Temp\sqlpackage-win7-x64-en-16.1.6374.0>SqlPackage.exe /a:import /sf:C:\Temp\Testbackup.bacpac /SourceTrustServerCertificate=True /tsn:localhost /tdn:Testbackup /p:CommandTimeout=1200
*** Unrecognized command line argument 'SourceTrustServerCertificate=True'.