Blog Post

Azure SQL Blog
3 MIN READ

Connection Security Improvements in SqlPackage 161

DrewSkwiersKoballa's avatar
DrewSkwiersKoballa
Icon for Microsoft rankMicrosoft
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

15 Comments

Sort By
  • arungarg's avatar
    arungarg
    MCT
    Jun 04, 2023

    Mark Anderson Please try with

     

    SqlPackage.exe /a:export /ssn:localhost /sdn:AxDBforSTG /tf:C:\Temp\AxDBforSTG.bacpac /p:CommandTimeout=4200 /p:VerifyFullTextDocumentTypesSupported=false /SourceTrustServerCertificate:True

  • PeterRNLI's avatar
    PeterRNLI
    Copper Contributor
    Mar 03, 2023

    This occurs if you are driving sqlpackage.exe using a batch file (as you might do when attempting to extract a DB with validation=False so you can actually work on it) and the account password contains a % character.
    I haven't tested exhaustively and other special characters may cause issues with powershell etc.

  • Mark_Anderson-'s avatar
    Mark_Anderson-
    Copper Contributor
    Feb 21, 2023

    Hi,

    I am getting below error while Exporting Database using last 161.8089.0 SqlPackage and the script used to export the database is added below.

    Script:
    SqlPackage.exe /a:export /ssn:localhost /sdn:AxDB_Test /tf:D:\Test\Test.bacpac /p:CommandTimeout=1200 /p:VerifyFullTextDocumentTypesSupported=false

    Error:
    C:\Temp\sqlpackage-win7-x64-en-161.8089.0>SqlPackage.exe /a:export /ssn:localhost /sdn:AxDB_Test /tf:D:\Test\Test.bacpac /p:CommandTimeout=1200 /p:VerifyFullTextDocumentTypesSupported=false
    Connecting to database 'AxDB_Test' on server 'localhost'.
    *** Changes to connection setting default values were incorporated in a recent release. More information is available at https://aka.ms/dacfx-connection
    *** Error exporting database:Could not connect to database server.
    Changes to connection setting default values were incorporated in a recent release. More information is available at https://aka.ms/dacfx-connection
    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:41.72

     

    can someone suggest the solution for this problem?

  • Bartosz_Uscinowicz's avatar
    Bartosz_Uscinowicz
    Copper Contributor
    Jan 26, 2023

    If manipulations via all 4 parameters are not working, one may try to add
    TrustServerCertificate=True
    To
    TargetConnectionString

  • Mark_Anderson-'s avatar
    Mark_Anderson-
    Copper Contributor
    Jan 17, 2023

    DrewSkwiersKoballa  When i try to Export the Database In version 161 of Sql-Package it's throwing Error.

    If possible can you share the New Export Command to Export the Database Using version 161 of Sql-Package

  • UmeshPandit's avatar
    UmeshPandit
    MCT
    Nov 24, 2022

    Just an add up: Worked with this command added increased time out!

    SqlPackage.exe /Action:Import /TargetServerName:"localhost" /TargetDatabaseName:"Testbackup" /TargetTrustServerCertificate:True /SourceFile:"C:\Temp\testbackup.bacpac" /p:CommandTimeout=6200

  • UmeshPandit's avatar
    UmeshPandit
    MCT
    Nov 24, 2022

    I tried with below command and it working till now, lets see:

     

    SqlPackage.exe /Action:Import /TargetServerName:"localhost" /TargetDatabaseName:"Testbackup" /TargetTrustServerCertificate:True /SourceFile:"C:\Temp\Testbackup.bacpac"

  • yeltsonisaac's avatar
    yeltsonisaac
    Copper Contributor
    Nov 21, 2022

    Appending /TargetEncryptConnection:False at the end of the command worked for me (as described in this blog)