Error importing bacpac using Sql Server Management Studio 2016 - could not load file or assembly Microsoft.SqlServer.Dac

Question

I made .bacpac file from Azure portal and save it to Desktop. Then in Management Studio 2016 Candidate right clicking on Databases and choosing Import data tier application i am targeting my file then next... and when it goes to process i am getting error:

Could not load file or assembly 'Microsoft.SqlServer.Dac, Version = 13.0.0.0, Culture = neutral, PublicKeyToken = b03f5f7f11d50a3a' or one of its dependencies. You can not find the file specified. (Microsoft.SqlServer.Management.Dac.DacWizard)

I was searching over google but i couldn't find any working solution on it. Could you help?

Check also this picture about configuration:

SQL configuration

Cataclysmic

I'm curious as to how SSMS was installed without the Data-Tier Application Framework. Did you uninstall anything or did something go wrong during setup? – Jackson

Might be worth trying to install the latest SSDT bits for SQL 2016. That would include all of the necessary objects to work w/ DAC files. – Aristaeus

Answer 1

This is due to a bug in SSMS that should be fixed in a future release. The issue is that if you have Lightswitch for VS2015 installed, the binding paths used by SSMS to lookup DLLs will include a reference to an earlier 110 version of the DacFx DLLs before the 130 version. This results in the above error since by default Visual Studio returns the first matching DLL.

Solution:

1. Open "C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\ssms.pkgundef" in a text file viewer. You will need to run this viewer as Administrator in order to edit it

2. Add the following line to ensure that in the future, the 110 binding path is ignored and save the file:

   [$RootKey$\BindingPaths\{272EA915-68EE-4AFD-99A4-1286126565EC}]

3. Next we need to remove it from the registry as it's already been added once. Open Regedit.exe and navigate to "HKEY_CURRENT_USER\SOFTWARE\Microsoft\SQL Server Management Studio\13.0_Config\BindingPaths"

4. Delete the folder {272EA915-68EE-4AFD-99A4-1286126565EC} from under this key.

Note: This will only happen on machines with VS2015 installed and the Lightswitch package installed in this. The cause is that VS2015 binding paths are included in Isolated Shell applications like SSMS unless the pkgundef file excludes them. A fix will be included in an upcoming release.

Disclosure: I work on the SQL Server tools team.

Wares

This fix did not work for me. I also (after trying this fix), installed the April preview. Didn't work; same error. Then I re-deleted the "272EA915..." folder from the BindingPaths registry key (which reappeared when I installed the April preview). Same error. Can I have you contact me directly or could we discuss this in a SO chat? – Dogger

I ended up getting this working on a fresh VM, so it's clearly some incompatibility issue with other stuff I have installed. I found an MSDN forum post with a similar solution: social.msdn.microsoft.com/Forums/en-US/… – Dogger

Hi Dave - the issue is that when answering this i didn't notice StackOverflow converted backslash followed by a { to be an escape sequence. It lost the \ after BindingPaths. I've updated the answer - adding the updated entry to the pkgundef will work for you. – Wares

Thanks Kevin, good catch. That ended up working for me. I appreciate the followup. – Dogger

This is not fixing my version (Microsoft SQL Server Management Studio 13.0.800.111) I can't find that key in the registry. I have {D0D30252-767F-4BFA-B7A5-5E470C50C0C1} & {687b26ef-c096-4f2d-9f8c-aaafada321ac} – Avoid

I have the same problem here, do not have the registry key, I have VS2017 and SQLServer management studio 13.0.15000.23 and can't do export data-tier application – Cheyney

Answer 2

You can download the latest version of the Data-Tier Application Framework (which includes Microsoft.SqlServer.Dac) from here: https://www.microsoft.com/en-us/download/details.aspx?id=51672

Note that you should also install the dependencies SqlDom and SqlSysClrTypes per the System Requirements section of the download page:

Dependencies Microsoft SQL Server Data-Tier Application Framework requires Microsoft SQL Server System CLR Types, and Microsoft SQL Server Transact-SQL ScriptDom (SQLSysCLRTypes.msi and SQLDOM.msi) both of which are available below. If you are on an x64 machine, you will need to install both the x64 and x86 versions of the MSIs above.

1. SQLSysCLRTypes.msi x64 - http://go.microsoft.com/fwlink/?LinkID=746597&clcid=0x409
2. SQLSysCLRTypes.msi x86 - http://go.microsoft.com/fwlink/?LinkID=746609&clcid=0x409
3. SQLDOM.msi x64 - http://go.microsoft.com/fwlink/?LinkID=746593&clcid=0x409
4. SQLDOM.msi x86 - http://go.microsoft.com/fwlink/?LinkID=746605&clcid=0x409

Jackson

i install this 3,6 mb package but i got same error message. Where are those dependencies? – Cataclysmic

did everything but still the same, is there something i can do more to get it works? – Cataclysmic

i also attached picture in my post when trying to update – Cataclysmic