We're using the hosted VSTS PAAS soluition for managing our source, tasks, and are incorporating the hosted build agent. We're able to successfully publish our app services to Azure Gov, but are having issues trying to accomplish two tasks in our build pipeline and suspect both are related.
We use the database projects to build a dacpac and the Execute Azure SQL build task to publish the schema changes to our databases. When targeting Azure Gov, we're unable to connect to the server with the following message:
Unable to connect to master or target server '{servername}'. You must have a user with the same password in master or target server '{servername}'.
I've confirmed that my SQL DB Details are set correctly. I also tried setting the firewall rules both to AutoDetect and IP Range (of 0.0.0.0 - 255.255.255.255) but am still seeing the same error. I still suspect that the VS build agent is not able to set the firewall rule for azure gov tenant even though the step is configured to use an ARM Azure connection type that successfully connects to our subscription.
Update 12/11/2017: Here is a modified log file from the build process:
##[section]Starting: Execute Azure SQL : DacpacTask
Task : Azure SQL Database Deployment Description : Deploy Azure SQL DB using DACPAC or run scripts using SQLCMD Version : 1.1.24 Author : Microsoft Corporation
Help : More Information
[debug]VstsTaskSdk 0.8.2 commit
[debug]Entering D:\a_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.24\DeploySqlAzure.ps1.
[debug]INPUT_CONNECTEDSERVICENAMESELECTOR: 'ConnectedServiceNameARM'
[debug]INPUT_TASKNAMESELECTOR: 'DacpacTask'
[debug]INPUT_DACPACFILE: 'd:\a\1\s\src\Vics\bin\Release\Database.dacpac'
[debug]INPUT_SQLFILE: 'd:\a\1\s'
[debug]INPUT_SQLINLINE (empty)
[debug]INPUT_SERVERNAME: 'tcp:{server}.database.usgovcloudapi.net,1433'
[debug]INPUT_DATABASENAME: '{instance}'
[debug]INPUT_CONNECTEDSERVICENAME: '{ARM ID}'
[debug]INPUT_CONNECTEDSERVICENAMEARM: ''
[debug]INPUT_SQLUSERNAME: '{user}'
[debug]INPUT_SQLPASSWORD: '{pwd}'
[debug]INPUT_PUBLISHPROFILE: 'd:\a\1\s'
[debug]INPUT_ADDITIONALARGUMENTS: '/p:BlockOnPossibleDataLoss=False /p:AllowIncompatiblePlatform=True'
[debug]INPUT_SQLADDITIONALARGUMENTS (empty)
[debug]INPUT_INLINEADDITIONALARGUMENTS (empty)
[debug]INPUT_IPDETECTIONMETHOD: 'AutoDetect'
[debug]INPUT_STARTIPADDRESS (empty)
[debug]INPUT_ENDIPADDRESS (empty)
[debug]INPUT_DELETEFIREWALLRULE: 'true'
[debug] Converted to bool: True
[debug]Loading module from path 'D:\a_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.24\ps_modules\VstsAzureRestHelpers_\VstsAzureRestHelpers_.psm1'.
[debug]$OVERRIDING $global:DebugPreference from 'Continue' to 'SilentlyContinue'.
[debug]Loading resource strings from: D:\a_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.24\ps_modules\VstsAzureRestHelpers_/module.json
[debug]Loaded 7 strings.
[debug]SYSTEM_CULTURE: 'en-US'
[debug]Loading resource strings from: D:\a_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.24\ps_modules\VstsAzureRestHelpers_\Strings\resources.resjson\en-US\resources.resjson
[debug]Loaded 7 strings.
[debug]Exporting function 'Add-AzureSqlDatabaseServerFirewallRule'.
[debug]Exporting function 'Remove-AzureSqlDatabaseServerFirewallRule'.
[debug]Exporting function 'Get-AzStorageKeys'.
[debug]Exporting function 'Get-AzRMStorageKeys'.
[debug]Exporting function 'Get-AzRmVmCustomScriptExtension'.
[debug]Exporting function 'Remove-AzRmVmCustomScriptExtension'.
[debug]Exporting function 'Get-AzStorageAccount'.
[debug]Exporting function 'Get-AzRmStorageAccount'.
[debug]Exporting function 'Get-AzRmResourceGroup'.
[debug]Exporting function 'Get-AzureNetworkInterfaceDetails'.
[debug]Exporting function 'Get-AzurePublicIpAddressDetails'.
[debug]Exporting function 'Get-AzureLoadBalancersDetails'.
[debug]Exporting function 'Get-AzureLoadBalancerDetails'.
[debug]Exporting function 'Get-AzureRMLoadBalancerFrontendIpConfigDetails'.
[debug]Exporting function 'Get-AzureRMLoadBalancerInboundNatRuleConfigDetails'.
[debug]Importing function 'Add-AzureSqlDatabaseServerFirewallRule'.
[debug]Importing function 'Get-AzRmResourceGroup'.
[debug]Importing function 'Get-AzRmStorageAccount'.
[debug]Importing function 'Get-AzRMStorageKeys'.
[debug]Importing function 'Get-AzRmVmCustomScriptExtension'.
[debug]Importing function 'Get-AzStorageAccount'.
[debug]Importing function 'Get-AzStorageKeys'.
[debug]Importing function 'Get-AzureLoadBalancerDetails'.
[debug]Importing function 'Get-AzureLoadBalancersDetails'.
[debug]Importing function 'Get-AzureNetworkInterfaceDetails'.
[debug]Importing function 'Get-AzurePublicIpAddressDetails'.
[debug]Importing function 'Get-AzureRMLoadBalancerFrontendIpConfigDetails'.
[debug]Importing function 'Get-AzureRMLoadBalancerInboundNatRuleConfigDetails'.
[debug]Importing function 'Remove-AzRmVmCustomScriptExtension'.
[debug]Importing function 'Remove-AzureSqlDatabaseServerFirewallRule'.
[debug]Loading resource strings from: D:\a_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.24/Task.json
[debug]Loaded 11 strings.
[debug]SYSTEM_CULTURE: 'en-US'
[debug]Loading resource strings from: D:\a_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.24\Strings\resources.resjson\en-US\resources.resjson
[debug]Loaded 11 strings.
[debug]FilePath= Find-VstsFiles LegacyPattern d:\a\1\s\src\Vics\bin\Release\Database.dacpac
[debug]Entering Find-VstsFiles.
[debug] LiteralDirectory: 'LegacyPattern'
[debug] LegacyPattern: 'd:\a\1\s\src\Vics\bin\Release\Database.dacpac'
[debug]Entering Get-MatchingItems.
[debug] IncludePatterns: 'd:\a\1\s\src\Vics\bin\Release\Database.dacpac'
[debug] ExcludePatterns: ''
[debug] IncludeFiles: 'True'
[debug] IncludeDirectories: 'False'
[debug] Force: 'False'
[debug]Path: d:\a\1\s\src\Vics\bin\Release\Database.dacpac
[debug]Leaving Get-MatchingItems.
[debug]Total found: 1
[debug]Leaving Find-VstsFiles.
packageFile= d:\a\1\s\src\Vics\bin\Release\Database.dacpac
[debug]Server friendly name is tcp:{server}
[debug]ENDPOINT_URL_d77b299c-5a4a-41cd-802e-de906dccbff0: 'https://management.usgovcloudapi.net/'
[debug]ENDPOINT_AUTH_d77b299c-5a4a-41cd-802e-de906dccbff0: '********'
[debug]ENDPOINT_DATA_d77b299c-5a4a-41cd-802e-de906dccbff0: '{"subscriptionId":"{subscriptionId}","subscriptionName":"US Government Azure Enterprise Offer","azureSpnRoleAssignmentId":"","azureSpnPermissions":"","spnObjectId":"","appObjectId":"","creationMode":"Manual","environment":"AzureUSGovernment","environmentUrl":"https://management.usgovcloudapi.net/","galleryUrl":"https://gallery.usgovcloudapi.net/","serviceManagementUrl":"https://management.core.usgovcloudapi.net/","resourceManagerUrl":"https://management.usgovcloudapi.net/","activeDirectoryAuthority":"https://login-us.microsoftonline.com/","environmentAuthorityUrl":"https://login-us.microsoftonline.com/","graphUrl":"https://graph.windows.net/","managementPortalUrl":"https://manage.windowsazure.us/","armManagementPortalUrl":"https://portal.azure.us","activeDirectoryServiceEndpointResourceId":"https://management.core.usgovcloudapi.net/","sqlDatabaseDnsSuffix":".database.usgovcloudapi.net","AzureKeyVaultDnsSuffix":"vault.usgovclouda...
[debug]Reaching SqlServer to check connection by running sqlcmd.exe -S "tcp:{server}.database.usgovcloudapi.net,1433" -U "{user}" -Q "select getdate()"
[debug]
[debug]sqlPackageArguments = /SourceFile:"d:\a\1\s\src\Vics\bin\Release\Database.dacpac" /Action:Publish /TargetServerName:"tcp:{server}.database.usgovcloudapi.net,1433" /TargetDatabaseName:"{instance}" /TargetUser:"{user}" /TargetPassword:"********" /p:BlockOnPossibleDataLoss=False /p:AllowIncompatiblePlatform=True /TargetTimeout:120
[debug]Sql Versions installed on machine factoryvm-az229 as read from registry: 140 130 120 110
[debug]Sql Version Specific Root Dir for version 140 as read from registry: C:\Program Files (x86)\Microsoft SQL Server\140\
[debug]Sql Version Specific Root Dir for version 130 as read from registry: C:\Program Files\Microsoft SQL Server\130\
[debug]Sql Version Specific Root Dir for version 130 as read from registry: C:\Program Files (x86)\Microsoft SQL Server\130\
[debug]Sql Version Specific Root Dir for version 120 as read from registry: C:\Program Files (x86)\Microsoft SQL Server\120\
[debug]Sql Version Specific Root Dir for version 110 as read from registry: C:\Program Files (x86)\Microsoft SQL Server\110\
[debug]Dac Framework (installed with SQL) not found on machine factoryvm-az229
[debug]Dac Framework installed with SQL Version 140 found at C:\Program Files\Microsoft SQL Server\140\DAC\bin\SqlPackage.exe on machine factoryvm-az229
[debug]Getting latest Visual Studio 15 setup instance.
[debug]Entering Invoke-VstsTool.
[debug] FileName: 'D:\a_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.24\vswhere.exe'
[debug] Arguments: '-version [15.0,16.0) -latest -format json'
[debug] RequireExitCodeZero: 'True'
[command]"D:\a_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.24\vswhere.exe" -version [15.0,16.0) -latest -format json
[debug][
[debug] {
[debug] "instanceId": "5fcd044c",
[debug] "installDate": "2017-12-01T18:36:43Z",
[debug] "installationName": "VisualStudio/15.4.5+27004.2010",
[debug] "installationPath": "C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise",
[debug] "installationVersion": "15.4.27004.2010",
[debug] "displayName": "Visual Studio Enterprise 2017",
[debug] "description": "Microsoft DevOps solution for productivity and coordination across teams of any size",
[debug] "enginePath": "C:\Program Files (x86)\Microsoft Visual Studio\Installer\resources\app\ServiceHub\Services\Microsoft.VisualStudio.Setup.Service",
[debug] "channelId": "VisualStudio.15.Release",
[debug] "channelPath": "C:\Users\packer\AppData\Local\Microsoft\VisualStudio\Packages\_Channels\4CB340F5\catalog.json",
[debug] "channelUri": "https://aka.ms/vs/15/release/channel",
[debug] "releaseNotes": "https://go.microsoft.com/fwlink/?LinkId=660692#15.4.5",
[debug] "thirdPartyNotices": "https://go.microsoft.com/fwlink/?LinkId=660708"
[debug] }
[debug]]
[debug]Exit code: 0
[debug]Leaving Invoke-VstsTool.
[debug]Dac Framework installed with Visual Studio found at C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140\SqlPackage.exe on machine factoryvm-az229
[debug]Executing SQLPackage.exe
[debug]Executing : "C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140\SqlPackage.exe" /SourceFile:"d:\a\1\s\src\Vics\bin\Release\Database.dacpac" /Action:Publish /TargetServerName:"tcp:{server}.database.usgovcloudapi.net,1433" /TargetDatabaseName:"{instance}" /TargetUser:"{user}" /TargetPassword:"********" /p:BlockOnPossibleDataLoss=False /p:AllowIncompatiblePlatform=True /TargetTimeout:120
Publishing to database '{instance}' on server 'tcp:{server}.database.usgovcloudapi.net,1433'. Initializing deployment (Start) Initializing deployment (Failed)
[debug]Error record:
[debug]Execute-Command : *** Could not deploy package.
[debug]At D:\a_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.24\DeploySqlAzure.ps1:178 char:9
[debug]+ Execute-Command -FileName $SqlPackagePath -Arguments $scriptA ...
[debug]+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[debug] + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
[debug] + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Execute-Command
[debug]
[debug]Script stack trace:
[debug]at Execute-Command, D:\a_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.24\Utility.ps1: line 235
[debug]at , D:\a_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.24\DeploySqlAzure.ps1: line 178
[debug]at , : line 1
[debug]at , : line 22
[debug]at , : line 18
[debug]at , : line 1
[debug]Exception:
[debug]Microsoft.PowerShell.Commands.WriteErrorException: *** Could not deploy package.
[error]*** Could not deploy package.
[debug]Processed: ##vso[task.logissue type=error]*** Could not deploy package.
[debug]Error record:
[debug]Execute-Command : Unable to connect to master or target server '{instance}'. You must have a user with the same password in master or target server '{instance}'.
[debug]
[debug]At D:\a_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.24\DeploySqlAzure.ps1:178 char:9
[debug]+ Execute-Command -FileName $SqlPackagePath -Arguments $scriptA ...
[debug]+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[debug] + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
[debug] + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Execute-Command
[debug]
[debug]Script stack trace:
[debug]at Execute-Command, D:\a_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.24\Utility.ps1: line 235
[debug]at , D:\a_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.24\DeploySqlAzure.ps1: line 178
[debug]at , : line 1
[debug]at , : line 22
[debug]at , : line 18
[debug]at , : line 1
[debug]Exception:
[debug]Microsoft.PowerShell.Commands.WriteErrorException: Unable to connect to master or target server '{server}'. You must have a user with the same password in master or target server '{instance}'.
[debug]
[error]Unable to connect to master or target server '{instance}'. You must have a user with the same password in master or target server '{instance}'.
[debug]Processed: ##vso[task.logissue type=error]Unable to connect to master or target server '{instance}'. You must have a user with the same password in master or target server '{instance}'.%0D%0A
[debug]No Firewall Rule was added
[debug]Caught exception from task script.
[debug]Error record:
[debug]D:\a_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.24\DeploySqlAzure.ps1 : Azure SQL Dacpac task failed.
[debug]At line:1 char:1
[debug]+ . 'd:\a_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a ...
[debug]+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[debug] + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
[debug] + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,DeploySqlAzure.ps1
[debug]
[debug]Script stack trace:
[debug]at , D:\a_tasks\SqlAzureDacpacDeployment_ce85a08b-a538-4d2b-8589-1d37a9ab970f\1.1.24\DeploySqlAzure.ps1: line 234
[debug]at , : line 1
[debug]at , : line 22
[debug]at , : line 18
[debug]at , : line 1
[debug]Exception:
[debug]Microsoft.PowerShell.Commands.WriteErrorException: Azure SQL Dacpac task failed.
[error]Azure SQL Dacpac task failed.
[debug]Processed: ##vso[task.logissue type=error]Azure SQL Dacpac task failed.
[debug]Processed: ##vso[task.complete result=Failed]
[section]Finishing: Execute Azure SQL : DacpacTask
The second, possibly related issue is trying to run our integration tests which access this database. Is there an alternative to creating a powershell script to connect to the correct tenant and execute New-AzureSqlDatabaseServerFirewallRule - run tests - Remove-AzureSqlDatabaseServerFirewallRule?
Can you manually execute the command generated by the debug logs above to deploy from console? does it succeed?
You can try adding firewall rule from the Azure portal and check if it works for you?