Team Studio Visual Studio build failed - database project for Azure SQL

Question

I have a Team Services build (was Visual Studio Online) with one MSBuild step that is configured to build and deploy a database project using a publish profile. I seem to be unable to authenticate it. When I queued the Team Services assembly definition, I can build the database project and create .dacpac. However, it's time to publish, and this error occurs:

C: \ a \ 1 \ s \ Source \ ShopDatabase \ bin \ Output \ MyDatabase.publish.sql (0,0): Error Deploy72002: Unable to connect to the main or target server 'mydb'. You must have a user with the same password on the main or target server "mydb".

We are sure that the user exists in mydb and master db in Azure.

Target: Azure SQL Database

DB Project Target Platform: Microsoft Azure SQL Database

When I run the publish profile directly from Visual Studio, it works. But this is not the case in the Team Services build definition. I tried them as arguments to MSbuild:

/ t: Build; Publish / p: SqlPublishProfilePath = "myproject.Dev.publish.xml" / p: Password = "mypassword"

and this:

/ t: Build; Publish / p: SqlPublishProfilePath = "myproject.Dev.publish.xml" / p: TargetConnectionString = "Data source = myproject.database.windows.net; Persist Security Info = True; User ID = MyUser; Password = my password;" / p: VisualStudioVersion = 14.0 / p: Username = "myuser" / p: Password = "mypassword"

and this:

/ t: Build; Publish / p: SqlPublishProfilePath = "myproject.Dev.publish.xml" / p: TargetConnectionString = "Data source = myproject.database.windows.net; Persist Security Info = True; User ID = MyUser; Password = my password;" / p: VisualStudioVersion = 14.0 / p: TargetUserName = "myuser" / p: TargetPassword = "mypassword"

But it will not work. Please help me T_T has been browsing the network for 6 hours now.

Answer 1

This is a bad error message that probably mask the real problem: you need to open a firewall for deployment in Azure SQL DB. It works from Visual Studio because you have IP range enabled. The steps in this guide to creating and deploying from VSO , in particular the post on deploying from VSO here, should help. It specifically describes how to open a firewall as part of a deployment.

Answer 2

The obvious answer (sometimes in retrospect!), But for future googlers an error You must have a user with the same password in master or target server

also happens if you point your deployment to an instance that does not exist. i.e.: Server not found.

Answer 3

Fwiw, created a script from a visual studio. Running as an administrator made him stop being a soul. What a waste of my life now ...

Answer 4

We recently had this problem and resolved it by removing the @ server name from the end of the server administrator login icon in the SQL Database Information section of the SQL Deploy VSTS Task. Some online guides say you need it, but recently it has changed, and it is no longer required.