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

sql azure azure-sql-database sql-server-data-tools
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.

Related questions

Optimal way to concatenate/aggregate strings

I'm finding a way to aggregate strings from different rows into a single row. I'm looking to do this in many different places, so having a function to facilitate this would be nice. I've tried solutions using COALESCE and FOR …

Read More
Error when connect database continuously

When I am querying from database in continuous looping, after some time I get an error : An exception has been raised that is likely due to a transient failure. If you are connecting to a SQL Azure database consider using …

Read More
SQL Azure - copy table between databases

I am trying to run following SQL: INSERT INTO Suppliers ( [SupplierID], [CompanyName]) Select [SupplierID], [CompanyName] From [AlexDB]..Suppliers and got an error "reference to database and/or server name in is not supported in this version of sql server" Any …

Read More