sql-server-data-tools localdb dac sql-server-2016-localdb

DacServices.Deploy to SQL Server LocalDB 2016 failing - unable to connect

This call to DacServices.Deploy has been working great for SQL Server LocalDB 2014, but fails when SQL Server LocalDB 2016 is installed:

string dacConnectionString = $"Server=(localdb)\\mssqllocaldb; Integrated Security=true; database={DatabaseName}";
var dacServices = new DacServices(dacConnectionString);
dacServices.Message += (sender, args) => Console.WriteLine($"{args.Message.Prefix}: {args.Message.Message}"); // Log dacpac deploy messages
dacServices.Deploy(LoadDacPac(), DatabaseName, true, new DacDeployOptions()
                                                     {
                                                         BlockOnPossibleDataLoss = false
                                                     });

The exception thrown by DacServices.Deploy for LocalDB 2016 is:

Microsoft.SqlServer.Dac.DacServicesException was unhandled by user code
  HResult=-2146233088
  Message=Could not deploy package.
  Source=Microsoft.SqlServer.Dac
  StackTrace:
       at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
       at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken)
       at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken, DacLoggingContext loggingContext)
       at Microsoft.SqlServer.Dac.DacServices.Deploy(DacPackage package, String targetDatabaseName, Boolean upgradeExisting, DacDeployOptions options, Nullable`1 cancellationToken)
       at Tv.Base.Test.Database.TestSqlLocalDb.CreateOrUpdateDatabaseIfNeeded(Boolean force) in D:\BuildAgent-02\work\6ec37398501798d0\src\Base.Test.Database\TestSqlLocalDb.cs:line 173
       at Tv.Services.Inventory.DataAccess.Tests.InventoryDatabaseFixture..ctor() in C:\src\tv\services\inventory\test\DataAccess.Tests\InventoryDatabaseFixture.cs:line 40
  InnerException: 
       HResult=-2146233088
       Message=Unable to connect to target server.
       Source=Microsoft.Data.Tools.Schema.Sql
       StackTrace:
            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__DisplayClass3.<>c__DisplayClass5.<CreatePlanInitializationOperation>b__1()
            at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action)
            at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass3.<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.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)

The "unable to connect" error seems incorrect/may be obscuring the real error, both because the specified connection string allows me to connect to the database using SqlConnection, and because I can deploy this dacpac to SQL Server LocalDB 2016 using the command-line:

sqlpackage "/Action:publish" "/SourceFile:MyDatabase.dacpac" "/TargetConnectionString:Server=(localdb)\mssqllocaldb;Database=MyDatabase;Integrated Security=true"

More info on my setup:

> sqllocaldb info mssqllocaldb
Name:               MSSQLLocalDB
Version:            13.0.1601.5
Shared name:
Owner:              DOMAIN\user
Auto-create:        Yes
State:              Running
Last start time:    7/1/2016 5:09:43 PM
Instance pipe name: np:\\.\pipe\LOCALDB#C1DD8548\tsql\query

> sqllocaldb v
Microsoft SQL Server 2014 (12.0.2000.8)
Microsoft SQL Server 2016 (13.0.1601.5)

The Microsoft.SqlServer.Dac assembly being used is from this NuGet package: https://www.nuget.org/packages/Microsoft.SqlServer.Dac

Solution

The fix for this was indeed to update the version of the Microsoft.SqlServer.Dac assemblies that we were using - I'd discovered that I should try that shortly before seeing @kevin-cunnane 's suggestion.

There were a few factors that made this less than obvious, which is why it's on SO:

Dac's error message "Unable to connect to target server" doesn't indicate a version incompatibility in any way. However from poking around the internet (eg DACPAC won't deploy because 'can't connect to server'?) it seems this error message can mean a version incompatibility in addition to incorrect connection string, firewall issue, etc.
There are several NuGet packages posted that contain the Microsoft.SqlServer.Dac and related assemblies. A few of them are not maintained by Microsoft, including the one I was using (Microsoft.SqlServer.Dac). The official Microsoft release was not available on NuGet.org until June 2016, and it doesn't have the most obvious NuGet id (Microsoft.SqlServer.DacFx.x64). So running update-package Microsoft.SqlServer.Dac did not have the desired effect.
The "official" NuGet package isn't listed anywhere on the MSDN + DAC pages - you'd think it would be mentioned here: https://msdn.microsoft.com/en-us/library/dn702988%28v=sql.120%29.aspx - but it's not.
Visual Studio 2016 installs SQL LocalDB 2016, and it does include the correct Dac assemblies (C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\Microsoft.SqlServer.Dac.dll), but they're not installed in the GAC or otherwise easily findable.

The fix that worked was

# Remove the old NuGet dependencies
uninstall-package Microsoft.SqlServer.Dac

# Install the new Dac NuGet package
Install-Package Microsoft.SqlServer.DacFx.x64

Requests for the Dac team, if you happen to see this:

Please link to the correct NuGet package from the MSDN documentation
Please improve the error message to indicate that newer client software is needed
Please request the other NuGet package maintainers to note the presence of the official NuGet package, or provide an upgrade that references the official NuGet package, b/c the presence of multiple packages is likely to cause angst.

(Btw, despite the difficulties here, Dac/SSDT is AWESOME . I haven't seen any comparable dev tooling for any competitive relational databases.)