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

Question

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

Priedieu

in what way does it fail? – Tabriz

Sorry - I'd accidentally submitted the question before I'd completed writing it - adding a new tag caused that. – Priedieu

Cool, what does "sqllocaldb info mssqllocaldb" and "sqllocaldb v" do? – Tabriz

In the connection string is "{DatabaseName}" just for stack overflow? if you print the connection string is it correct? – Tabriz

@EdElliott - Yes, the connection string is correct. This code works as expected for localdb 2014, but fails for localdb 2016. – Priedieu

Any errors in the errorlog for the sql instance? – Tabriz

Also have you tried restarting :) (I mean dropping and re-creating the localdb instance) – Tabriz

DacServices msdn documentation does not have the option to view for SQL 2016, maybe it just doesn't support it yet. – Thorn

What version of the DLLs are you using? You'll need the v13 DLLs (now available as nuget: nuget.org/packages/Microsoft.SqlServer.DacFx.x64 ) to target SQL Server 2016 servers. It's likely you're still binding to the v12 DLLs. – Illuse

Answer 1

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:

1. 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.
2. 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.
3. 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.
4. 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.)

Priedieu

Thanks Crimbo, we'll look into updating the doc. One question - the unofficial Dac nuget is something used by many people. Would removing this not impact/break them? It's a reason we haven't pushed to get this done. We're trying to balance getting official support out there with avoiding negative impact for existing users. – Illuse

You're right Kevin, taking it down would be too disruptive. I'll remove that suggestion. How about other options like asking the author to deploy a new nuget package that is empty, has the correct version # (13.0) but has a dependency on the correct nuget package? Or at a minimum a note on their NuGet page. – Priedieu

We can certainly ask about that - we have already contacted them and they're happy to help us get the official version well-supported – Illuse

I gotta agree, SSDT totally changed our development processes for the better – Kernel

Thanks Crimbo!!! I faced the same issue with azure sql db as well, apparently changing the dacpac version in our management software resolved the issue. First I raised ticket with azure team where I was redirected to sql team then I was asked to deploy with ssms,,, Finally your answer did the magic. Lot of effort and time wasted in fixing this issue. It will be really helpful if the error message is fixed. – Samuelson

@KevinCunnane - a few years later and searching nuget for "ssdt" doesn't show any of these related packages. part of that is nuget's poor search and sort, but part of that is the naming choice for the package and the lack of the "SSDT" tag. Its a huge disservice that its so hard to find this (and the report viewer) package. – Unconquerable