Sql Server Data Tools Data Comparison fails when connecting to Azure

Question

When using the Sql Server Data Tools Data Comparision tools a few of us here are unable to do comparisons when the source is an Azure database.

The error we get is below:

---------------------------
Microsoft Visual Studio
---------------------------
Data information could not be retrieved because of the following error:

Value cannot be null.

Parameter name: conn

Value cannot be null.

Parameter name: conn

The connection test works fine. I've tried creating a new connection. As a side note if I do data compare with a non-Azure source things work fine.

SQL Server Data tools version is 12.0.50512.0

We can access the server using SSMS without any problems.

Answer 1

It turned out to be a permissions issue but I was able to diagnose it using the details available at https://social.msdn.microsoft.com/Forums/sqlserver/en-US/740e3ed8-bb05-48f7-8ea6-721eca071198/publish-to-azure-db-v12-failing-value-cannot-be-null-parameter-name-conn?forum=ssdt

Gathering an Event Log for SSDT

• Open a new command prompt as Administrator.
• Run the following command
  • logman create trace -n DacFxDebug -p "Microsoft-SQLServerDataTools" 0x800 -o "%LOCALAPPDATA%\DacFxDebug.etl" -ets
  • logman create trace -n SSDTDebug -p "Microsoft-SQLServerDataToolsVS" 0x800 -o "%LOCALAPPDATA%\SSDTDebug.etl" -ets
• Run whatever the target/issue scenario is in SSDT. Go back to the command prompt and run the following commands
  • logman stop DacFxDebug -ets
  • logman stop SSDTDebug -ets
• The resulting ETL files will be located at %LOCALAPPDATA%\SSDTDebug.etl & %LOCALAPPDATA%\DacFxDebug.etl and can be navigated to using Windows Explorer.

Answer 2

There is no such limitation. Ref - https://msdn.microsoft.com/en-us/hh272693(v=vs.103).aspx

Check whether the Firewall Rule is open for this connection. If not, then add the current client IP to allowed IP addresses of that SQL Azure DB

Answer 3

I find that if I have already compared a local DB before hand (in the same session) then try to compare an Azure DB. I find there is some strange lock preventing login on the Azure SQL DB.

Shut down Visual Studio and reopen and it should connect ok.