Every now and then I come across this error in SSDT, normally when trying to publish and the odd thing is that the description never matches the actual cause (if you desperate for the cause it is because you can’t connect). The thing I like about the description is the way it tries to tell you what is wrong and what server you are connecting to but it fails at both and instead tells you about an unrelated error and a database name instead of a server name.

What we have is:

"Unable to connect to master or target server '{0}'. You must have a user with the same password in master or target server '{0}'.\"

Twice it tries to tell you the server name but both time it actually tells you the database name. I thought I would dig into it a little with reflector to try and see where the error is coming from and whether or not it would ever show the servername. So in reflector I found the error and found where it was used. If we look in, what is surely to be everyone’s favourite ssdt dll Microsoft.Data.Tools.Schema.Sql.dll, we can see that the error message is used in SqlDeploymentEndpointServer.OnInit and we have something like:

catch (ExtensibilityException exception) { Tracer.TraceException(TraceEventType.Verbose, TraceId.CoreServices, exception, "Error loading DSP families"); throw new DeploymentFailedException(string.Format(CultureInfo.CurrentCulture, DeploymentResources.InvalidServerEndpoint, new object[] { targetDBName }), exception); }

they are indeed passing targetDBName into the InvalidServerEndpoint error message so yes indeed the error message will only ever show the database name.

I had a quick look at what can cause this and it is something to do with opening the SqlConnection which is wrapped in lots of retry logic that is different for Azure compared to other types of SQL - lots of interesting stuff maybe for another post but basically SSDT wasn’t able to open a connection - check the server name, ports, database etc (i.e. maybe your default database is not available to that user), connect via ssms then when it works there come back to ssdt.

Footnote, I did think about raising a connect but then couldn’t be bothered, if anyone does i’ll be happy to vote for it!

Comments:

Xavier

July 5, 2017 - 07:36

Thanks!

I though I was going mad with this issue. Unfortunately, I still cannot publish my custom function from VS to MSSQL… but I least know my connection string is correct, so… thanks.

Adam

October 10, 2017 - 12:58

I’m having this exact issue

I’m having this exact issue when attempting to deploy a DACPAC using DacServices to an Azure SQL database. It works in one data centre but doesn’t work in another. I’m sure that all of my command parameters are correct and that the calling machine is white-listed in the SQL Server’s firewall. Is there a workaround for this?

Ed Elliott

October 17, 2017 - 07:55

Hi Adam, the problem is to do

Hi Adam, the problem is to do with not being able to create a connection to the database - so I would check firewall settings, your connection string (parameters) and that you have the latest DacFx.

I would also check you can connect to sql from the box you are dpeloying from using ssms or sqlcmd - if you can’t do that then DacFx won’t ever work.