DACPAC won't deploy because 'can't connect to server'?

Question

I'm trying to deploy a DACPAC to LocalDB 2012 but it's just not having it:

Stack trace is as follows:

==================================

Could not deploy package. (Microsoft.SqlServer.Dac)

------------------------------
Program Location:

   at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, Action`2 reportStatus, CancellationToken cancellationToken)
   at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken)
   at Microsoft.SqlServer.Dac.DacServices.Deploy(DacPackage package, String targetDatabaseName, Boolean upgradeExisting, DacDeployOptions options, Nullable`1 cancellationToken)
   at Microsoft.SqlServer.Management.Dac.DacWizard.DeployModel.Install()
   at Microsoft.SqlServer.Management.Dac.DacWizard.DeployModel.RunAction()
   at Microsoft.SqlServer.Management.Dac.DacWizard.ExecuteDacPage.backgroundWorker1_DoWork(Object sender, DoWorkEventArgs e)
   at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
   at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

===================================

Unable to connect to target server. (Microsoft.Data.Tools.Schema.Sql)

------------------------------
Program Location:

   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentEndpointServer.OnInit(ErrorManager errors, String targetDBName)
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment..ctor(SqlDeploymentConstructor constructor)
   at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentConstructor.ConstructServiceImplementation()
   at Microsoft.SqlServer.Dac.DacServices.CreatePackageToDatabaseDeployment(String connectionString, IPackageSource packageSource, String targetDatabaseName, DacDeployOptions options, ErrorManager errorManager)
   at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass4.<CreatePlanInitializationOperation>b__0(Object operation, CancellationToken token)
   at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)

Now since I'm attempting to deploy this through SSMS, I know I can connect to the server. I've checked the solution in Visual Studio and the target version is for SQL Server 2012 and the connection string appears to be correct.

Interestingly, doing a deploy through Visual Studio works, but then I can't upgrade the database to a data tier application, I get this message:

===================================

Database source is not a supported version of SQL Server (localdb)\MYINSTANCE: . (Microsoft.SqlServer.Dac)
------------------------------
Program Location:

   at Microsoft.SqlServer.Dac.DacServices.GetDatabaseSchemaProvider(String connectionString)
   at Microsoft.SqlServer.Dac.DacServices.InternalRegister(String targetDatabaseName, String applicationName, Version applicationVersion, String applicationDescription, ModelStorageType modelStorageType)
   at Microsoft.SqlServer.Dac.DacServices.<>c__DisplayClass29.<Register>b__28(Object operation, CancellationToken token)
   at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, Action`2 reportStatus, CancellationToken cancellationToken)
   at Microsoft.SqlServer.Dac.DacServices.Register(String targetDatabaseName, DacSchemaModelStorageType modelStorageType, String applicationName, Version applicationVersion, String applicationDescription)
   at Microsoft.SqlServer.Management.Dac.DacWizard.RegisterModel.RunAction()
   at Microsoft.SqlServer.Management.Dac.DacWizard.ExecuteDacPage.backgroundWorker1_DoWork(Object sender, DoWorkEventArgs e)
   at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
   at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

What am I missing?

Desiccant

Answer 1

Updating to the latest version of SSMS should fix this. It's available here: https://msdn.microsoft.com/en-us/library/mt238290.aspx

Otherwise, please confirm that the version of LocalDB you're using is LocalDB 2012. To do this you can run this query against the server:

select @@version

• 11.0.xxxx = SQL Server 2012
• 12.0.xxxx = SQL Server 2014
• 13.0.xxxx = SQL Server 2016

The library that handles dacpac deployment is called the data-tier application framework, or DacFX. The version of DacFX used by SSMS 2012 is installed in:

C:\program files (x86)\Microsoft Sql Server\110\DAC\bin

Note the "110" in the path, indicating that the version of DacFX supports up to SQL Server 2012. SSMS 2014 looks for DacFX in a similar path, but in the 120 folder, and that version of DacFX supports up to SQL Server 2014, and so on.

When DacFX is asked to deploy to a server version that's newer than what is supports, it will give the "Cannot connect" error message. (The error message has been improved in newer versions of DacFX, by the way).

At one point in time, Visual Studio used the version of DacFX installed in the above path, but around the time that SQL Server 2014 support was released in the SQL Server tools, a change was made and Visual Studio got its own copy of DacFX under

C:\program files (x86)\Microsoft Visual Studio XX.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120

So this is how you can get different behavior between different versions of Visual Studio and SSMS.

Hydrogen

Turns out I'd made a mistake here, v11 is SQL 2012 rather than v12. Oops :). I uninstalled LocalDB 2014 (which I have no idea when I installed that), repaired LocalDB 2012 and everything works great now. – Desiccant

I had this same problem connecting to SQL 2016 using DacFX 120. Changing to use DacFX 130 (which happened to be installed beneath Visual Studio as per the answer) resolved the problem. Thanks! – Bluepoint

Answer 2

I had the same problem and it was fixed by changing the executable folder from "C:\program files (x86)\Microsoft Sql Server\110\DAC\bin" to "C:\program files (x86)\Microsoft Sql Server\120\DAC\bin"

Thanks, Chandana

Personify