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

Question

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?

Solution 1

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.

Solution 2

Initializing deployment (Start)
Initializing deployment (Failed)
*** Could not deploy package.
Unable to connect to master or target server 'DbName'. You must have a user with the same password in master or target server 'DbName'.

Issue occurred while deploying build through VisualStudio-2015 and it support to publish database on the database servers whose version up to only 2016.

Solution: For SQL server 2017 we need to Publish Database with Visual Studia 2017 only. Need to upgrade SSDT.

Solution 3

My situation was slightly different in that I had exported an Azure database (with compat level 140) from SQL Azure and then tried to import it into a local SQL Server 2017 installation - using the latest SSMS 2017 that I just installed yesterday - and still got this message.

Turned out that although I did have latest SSMS installed, I was actually opening SSMS 2016 by mistake! So be sure to select and pin the correct version to avoid it happening again. Just typing 'SSMS' into the Windows Start menu may not show both.

So if you use SSMS to import you only need the latest version, and no separate tools.

---

Footnote: Even after opening the correct SSMS version I got another error - something about contained databases.

An Azure database is a 'contained' database (or at least mine was) - meaning that its user logins are embedded in the database. This isn't enabled by default apparently in standard SQL Server 2017.

After running this in the local SQL in master I was able to import it successfully.

sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO

Solution 4

Faced same issue when trying to deploy a DB from local SQL Server to Azure SQL DB via SSMS.

Tried to alter source DB's compatibility level to 130, still got same error.

Tried to add same user logins to master DB, no help.

Eventually, started looking for other approaches. Succeeded by using Data Migration Assistant, as instructed in https://docs.microsoft.com/en-us/azure/sql-database/sql-database-cloud-migrate.

Solution 5

When this happened to me it was due to the version of tools I was using. I thought it strange that the most recent SqlPackage.exe I found in C:\Program Files (x86)\Microsoft SQL Server didn't work and publishing from visual studio did, so I found the most recent under the visual studio directories:

C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150\SqlPackage.exe

View more solutions

Comments

[Gwasshoppa]

In the document that Alberto points to above it also mentions that it could be the firewall settings on the Azure Server

[Gaspa79]

Yup, this did it for me as well. Thanks a lot!

[Reza]

@potsi How did you solved your issue? would you please add the solution here