This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.
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.
