Sql Server Data Tools Data Comparison fails when connecting to Azure

4
votes

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.

3
azureazure-sql-databasesql-server-data-tools
can you share the version of SQL Server Data Tools with us? you can see this under HELP>About Microsoft Visual Studio. I just tried this and did not run into any problems. Would love to help. - Boboyum
added. Also mention that we can use SSMS fine. - Shane Courtrille
SSDT version 12.0.50512.0 supports Data Compare with Azure SQL Database. Can you please share more detail about your SQL Database? What is the Pricing Tier of the database and schema complexity? e.g. number of tables and views. size of database. - Eric Kang
Had similar problem with publishing .sqlproject to Azure V12 database. One of the environments' users had no access to master database. Thank you! - Alex Sorokoletov

3 Answers

4
votes

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.
1
votes

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

1
votes

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.