What would cause deploying a dacpac to fail on one server and not another?

Question

There are a lot of questions out there about this issue. Here is one - SSDT failing to publish: "Unable to connect to master or target server". I'm getting the same error:

2020-03-03T13:51:28.5079081Z *** Could not deploy package.
2020-03-03T13:51:28.5080013Z Unable to connect to master or target server 'databasename'. You must have a user with the same password in master or target server 'databasename'.
2020-03-03T13:51:28.6466638Z ##[error]Publishing to database 'databasename' on server 'server'. 
Initializing deployment (Start) 
Initializing deployment (Failed) 

I'm deploying through Azure DevOps. It works fine when I deploy to our two lower environments (which run on the same development server), but this morning was our first production deployment. SQL Server 2016 is running on both servers. There is a difference in the versions though. The development server is running Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) while production is running Microsoft SQL Server 2016 (RTM-CU5) (KB4013105) - 13.0.2197.0 (X64). Both have compatibility level set to 130.

The YAML for the release step in develop looks like this:

steps:
- task: SqlDacpacDeploymentOnMachineGroup@0
  displayName: 'Deploy using : dacpac'
  inputs:
    DacpacFile: '$(System.DefaultWorkingDirectory)/database/Database.dacpac'
    TargetMethod: publishProfile
    PublishProfile: '$(System.DefaultWorkingDirectory)/Database/develop.publish.xml'

The only difference between the development step and the production step is that the publish profile is different. And the only differences between the two publish profile XML files are the server name and database name.

Here is the develop profile:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="14.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>develop</TargetDatabaseName>
    <DeployScriptFileName>develop.sql</DeployScriptFileName>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <TargetConnectionString>validconnectionstring</TargetConnectionString>
    <BlockOnPossibleDataLoss>False</BlockOnPossibleDataLoss>
    <GenerateSmartDefaults>True</GenerateSmartDefaults>
  </PropertyGroup>
  <ItemGroup>
    <SqlCmdVariable Include="dbEnvironment">
      <Value>develop</Value>
    </SqlCmdVariable>
    <SqlCmdVariable Include="migratedLegacyDatabase">
      <Value>Migration-Develop</Value>
    </SqlCmdVariable>
    <SqlCmdVariable Include="migrationSource">
      <Value>false</Value>
    </SqlCmdVariable>
  </ItemGroup>
</Project>

The same profile was used when we used Jenkins, but the process was a little different. When I use the credentials in the production profile I am able to connect successfully to the database.

Feel like I must be missing something obvious, but I'm not sure what it is. I know this isn't an easily reproducible issue, but if anyone has any ideas I'm all ears.

Answer 1

As what you said, this is hard to reproduced. I just share some possibility reasons that I know, and you could do check by yourself.

1) There has firewall rules for the server, so that the agent fails to pass.

2) Not sure here which agent you are using, but try with self-agent or run with deployment group job in pipeline.

3) (This would be the most easy to missed, and I ever met)

Check the SQL username/password, and ensure it is correct.

4) The server role does not correct.

The user account used in publish profile could have sysadmin privileges. Also since the sysadmin users on server are mapped with user "dbo" in database so that this error leaded.

To solve that, try with use another user which has db_owner permission on the database.

5) Enable Mixed Mode Authenication.

Hope these could give helps.