I saw a report of a problem building a database with ReadyRoll in Azure SQL Database. This person wanted to use a local Shadow database in LocalDB and target an Azure SQL Database. I hadn’t build that config, so I decided to give it a try.

I ended up with this:

which was making me a little crazy. I’ve had most builds work really well. I tried a number of things, but kept getting a few items in the build. There were login errors or network errors, both of which bothered me since I could manually log in with SSMS from the same machine as my build agent.

I suspected a few things here, one of which was the use of named pipes for the Shadow database and TCP for Azure SQL Database.

Eventually, I decided to fall back with msbuild, ignoring VSTS, and make sure all my parameters were correct. I started here:

C:\Windows\Microsoft.NET\Framework\v4.0.30319\msbuild builds_azure.sqlproj /p:TargetServer=”dkranchapps.database.windows.net” /p:TargetDatabase=”SSBuilds” /p:TargetUsername=”dlmdeploy” /p:TargetPassword=”astrongpassword” /p:ShadowServer=”(Localdb)\ShadowSSBuilds7″ /p:GenerateSqlPackage=True /p:SkipDriftAnalysis=True /p:ShadowUsername=”shadowuser” /p:ShadowPassword=”someotherpassword”

I promptly got a VS build started and then this error:

C:\Program Files (x86)\MSBuild\ReadyRoll\ReadyRoll.Data.Schema.SSDT.targets(513,5): error : An error occurred while attempting to

create a patch script: Login failed for user ‘dlmdeploy’. [E:\Documents\Visual Studio 2015\Projects\builds_azure\builds_azure\buil ds_azure.sqlproj]

Done Building Project “E:\Documents\Visual Studio 2015\Projects\builds_azure\builds_azure\builds_azure.sqlproj” (default targets)

— FAILED.

I verified the password in SSMS, verified the firewall and tried again. Same issue. Then I tried this:

> sqlcmd -S dkranchapps.database.windows.net -U dlmdeploy -P “AStrongPassword”

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user ‘dlmdeploy’..

Hmmm, that’s interesting. Why would this work in SSMS and not SQLCMD? My first thought was some driver setting, maybe ADO v ODBC, but that seems silly.

Eventually I suspected some mismatch in databases, and found this post. It confirmed what I was about to test. I needed the same login/password in the master db as in the particular db I was using.

I used SSMS and connected to master to create a user there.

Once I did this, the build ran fine, both in the command line and in VSTS.

A couple lessons here. First, in Azure, you connect to databases, but since some processes (like MSBuild) might connect to a default, you need the user in your db and in master. If you have multiple databases (as I do), I’d suggest separate accounts for building in each db.

Second, work with the command line first. That’s the key. Once you have things working from there, it’s easy to move to a tool and automate your command line instructions.

Filed under: Blog Tagged: Azure, continuous integration, DevOps, syndicated, vsts