BACPAC import failure fix
It is a common task to move databases between a Microsoft-managed environment and a self-service machine. From Azure SQL we can only take a BACPAC export, which needs to be imported/converted into Microsoft SQL Server format. With the recent changes and improvements of security, you may be facing an error message when trying to move the database backups. Let’s have a look at the BACPAC import and export failure fix to address the following error message:
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 tool to process the BACPAC file is called SqlPackage. You should install the latest DAC version first for to have the benefit of fixed and performance improvements.
Example of a BACPAC export from your MSSQL DB:
.\SqlPackage.exe /Action:export /ssn:SourceServerName /sdn:AXDB_source /tf:F:\Backup\AXDB.bacpac /p:CommandTimeout=1200 /p:VerifyFullTextDocumentTypesSupported=false /SourceTrustServerCertificate:true
Example of a BACPAC import into your MSSQL DB:
.\SqlPackage.exe /Action:Import /sf:C:\DynamicsTools\Sandbox.bacpac /tsn:TargetServerName /tdn:AXDBname /p:CommandTimeout=0 /TargetTrustServerCertificate:true
As you can see, now you have to enforce trusting the server certificates to pass the error message above.