Azure DevOps DACPAC Task Issues “Cannot open server ‘servername’ requested by the login.”
We recently had an issue deploying a DACPAC file to an Azure PaaS SQL Server. The issue we were having is that we implemented the Azure Firewall on the database we were planning on deploying to, we had locked down the IP addresses that could connect to specific known IP addresses. This had a knock on effect to our DevOps release, the next time we ran a release we hit the below error due to a Microsoft DevOps build server trying to connect to the database but failing as we didn’t allow it.
[error]*** Could not deploy package.
[error]Unable to connect to target server ‘servername.database.windows.net’. Please verify the connection information such as the server name, login credentials, and firewall rules for the target server. Cannot open server ‘servername’ requested by the login. Client with IP address ‘X.X.X.X’ is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.
[error]The Azure SQL DACPAC task failed. SqlPackage.exe exited with code 1.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme
We looked into the IP address ranges for the Microsoft Build Agents but there was so many that it wasn’t feasible to add each range to the SQL Firewall rules, also the IP’s get updated dynamically so would be difficult to keep on top of.
To resolve this Azure DevOps issue we thought outside of the box, we created a script that checked the IP address of the build agent that was going to deploy the DACPAC and added the IP to the Azure SQL firewall rule prior to deployment. If you simply put the below script in your repo and call it before your DACPAC task in your release, you should be able to deploy with no issues!
[CmdletBinding(DefaultParameterSetName = ‘None’)]
param
(
[String] $ServerName = “bicloud-test”,
[String] $ResourceGroupName = “rg-ana-nonprod-bipt001”,
[String] $AzureFirewallName = “ManagedHostDevOps”
)
$ErrorActionPreference = ‘Stop’
function New-AzureSQLServerFirewallRule {
$agentIP = (New-Object net.webclient).downloadstring(“http://checkip.dyndns.com”) -replace “[^\d\.]”
New-AZSqlServerFirewallRule -StartIPAddress $agentIp -EndIPAddress $agentIp -FirewallRuleName $AzureFirewallName -ServerName $ServerName -ResourceGroupName $ResourceGroupName
}
function Update-AzureSQLServerFirewallRule {
$agentIP = (New-Object net.webclient).downloadstring(“http://checkip.dyndns.com”) -replace “[^\d\.]”
Set-AZSqlServerFirewallRule -StartIPAddress $agentIp -EndIPAddress $agentIp -FirewallRuleName $AzureFirewallName -ServerName $ServerName -ResourceGroupName $ResourceGroupName
}
If ((Get-AzSqlServerFirewallRule -ServerName $ServerName -FirewallRuleName $AzureFirewallName -ResourceGroupName $ResourceGroupName -ErrorAction SilentlyContinue) -eq $null)
{
New-AzureSQLServerFirewallRule
}
else
{
Update-AzureSQLServerFirewallRule
}
For more information please get in touch with us today.
