SSDT failing to publish: "Unable to connect to master or target server"

im1dermike picture im1dermike · Oct 13, 2017 · Viewed 18.3k times · Source

I'm attempting to use SSDT to publish to a SQL Server database in Azure. When I do so, I am seeing the following error:

Unable to connect to master or target server 'DATABASE_NAME'. You must have a user with the same password in master or target server 'DATABASE_NAME'.

Ignoring the fact that it's listing a database name as the server name in the error, I'm not sure how to resolve it. When I specify the target database, I can successfully Test Connection. I can also connect using the same creds to the database through SSMS.

After researching the error, it seems like it is often that the firewall for the database in Azure does not include the IP address of the machine you're publishing from. It not only contains my IP, but I added another firewall rule to allow every IP (0.0.0.0-255.255.255.255) to eliminate the firewall as a potential cause of the problem.

Any ideas?

Answer

Alberto Morillo picture Alberto Morillo · Oct 13, 2017

This is a known issue. This happens due to the new compatibility level 140 for newly created SQL Azure databases. The issue has been documented here and updating SSDT from here to the latest version may solve the issue.

Alternatively, you can change the compatibility level of your database to 130.

ALTER DATABASE database_name   
SET COMPATIBILITY_LEVEL =  130;

Hope this helps.