Condividi tramite

Error: Could not deploy package. Unable to connect to target server.

  • Articolo

In this post we would like to explain one of the interesting issue that we encountered while deploying a DACPAC from sqlpackage.exe.

Symptoms

Cannot Deploy DACPAC Extracted from SQL 2012 Server from .NET custom code or from SQLPackage.exe command to SQL 2014

image

C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin>SqlPackage.exe /Action:Publish /SourceFile:”C:\temp\AgentLink2_11.0.6020.dacpac” /tsn:”RAGHAVSDC” /TargetDatabaseName:TestACM

Publishing to database ‘TestACM’ on server ‘RAGHAVSDC’.
The dac history table will not be updated.
Initializing deployment (Start)
Initializing deployment (Failed)
*** Could not deploy package.
Unable to connect to target server.

Cause

We don’t have a DAC Folder at location C:\Program Files (x86)\Microsoft SQL Server\120\ in the system but have the folder C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin (We can successfully publish to SQL 2012 but not SQL 2014)

Resolution

To reproduce the issue, please find a DAC folder at location C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin

Open a CMD with administrator privileges and navigate to this path and run the sqlpackage.exe to publish it to a SQL 2014/2016 server and we will get the same exact error

“*** Could not deploy package.

Unable to connect to target server.”

The above looks like to be a connectivity error at our first glance but this is not the case here. We tested the connectivity for this on multiple machines and didn’t find an issue with it. The solution to the problem is we need to install the DAC Framework https://www.microsoft.com/en-in/download/details.aspx?id=42293 and once installed, we will be able to see the DAC Folder at C:\Program Files (x86)\Microsoft SQL Server\120\DAC\

We then can try to publish the DACPAC from sqlpackage.exe from the 120 Location and it gets published successfully.

image
More Information:

In the above scenario, we noticed that we can only publish the DACPAC for the version the DACPAC file was created for.

If we have taken a DACPAC for SQL 2012, then we can publish is to any higher version of SQL but it needs to be published from the 120 folder (C:\Program Files (x86)\Microsoft SQL Server\120\DAC\Bin) if we want to publish to SQL 2014. If we are trying to publish the DACPAC taken from SQL 2012 to 2016 then we need to publish the package from the 130 Folder (C:\Program Files (x86)\Microsoft SQL Server\130\DAC\Bin)

DACPAC is a feature of our Data Tier application which will allow us to backup the schema of our database. In simple terms, it is only database schema (definition without the data) which can be used on higher versions of SQL Server. SQLPackage.exe is a utility which allows us to automate database development and projects in our environment.

SQLPackage.exe: https://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx

Data Tier Applications: https://msdn.microsoft.com/en-us/library/ee210546.aspx

Design and Implementation for DACPAC: https://technet.microsoft.com/en-us/library/ee210546(v=sql.110).aspx

DAC Framework download: https://www.microsoft.com/en-in/download/details.aspx?id=42293

Written by – Ujjwal Patel, Support Engineer.
Reviewed by – Raghavendra, , Sr. Support Engineer.

Comments

  • Anonymous
    June 06, 2017
    Thank you for this! This is such a misleading error. We had the same issue. We upgraded to SQL 2016 from 2012 and suddenly our visual studio database project deployment scripts to the 2016 SQL server instance was broken. We could deploy on 2012 but not 2016. Turns out we did not have the SQL 2016 DACPAC on the build server. Once we installed the 2016 DAC Framework, this issue was resolved.
    • Anonymous
      June 06, 2017
      Glad this helped you :)