Unable to Add Azure DB Firewall Rule to Allow Build Server to Run Tests

Question

We use a Visual Studio Online-hosted build server to automate our build process. As part of this I'm looking into adding unit and integration tests into this process.

These tests require access to our SQL Azure DBs (2 of them, both on the same server), which in turn requires access through the DB server's firewall.

I have a PowerShell script which uses New-AzureRmSqlServerFirewallRule to add IP addresses to the DB server, and these firewall rules are successfully showing up in the Azure portal.

Specifically, the script adds firewall rules for:

• All IPv4 addresses* on the build server (as returned by Get-NetIPAddress)
• Build server's external IP address (as returned by https://api.ipify.org)

In conjunction, it appears that the pre-defined AllowAllAzureIPs and AllowAllWindowsAzureIps rules are automatically added.

However, the tests subsequently fail with the exception:

System.Data.SqlClient.SqlException: System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I'm unsure why the build server is unable to reach the DB server - could it be that the host of the test processes is using yet a different IP address?

Update
As has been pointed out, the exception message mentions "Named Pipes Provider" which suggests that the DB connection is using a named pipe instead of an IP/TCP connection. To test this I changed the local app.config to contain an unknown/random/inaccessible IP and ran the tests locally (they otherwise run successfully locally): I received exactly the same exception message mentioning "Named Pipes Provider". Perhaps at some level the ReliableSqlConnection class resolves to a named pipe but my point is that I can induce this very same exception by changing to an unknown or inaccessible IP address in my DB connection string.

Furthermore, the DB connection string starts with tcp: which, as per this blog post, explicitly tells the connection to use TCP/IP and not named pipes.

I have also modified the firewall rule to permit all IP addresses (0.0.0.0 to 255.255.255.255) but the same exception is still thrown. This suggests that the SQL Azure firewall rule is not the cause of the 'blockage'.

My suspicion therefore turns to network access being blocked (though a whitelist is probably present to permit the build server to reach the code repository). I added a very simple PowerShell script to the start of the build process:

Test-Connection "172.217.18.100" #resolves to www.google.com

This results in

Testing connection to computer '172.217.18.100' failed: Error due to lack of resources

Have the build servers disabled ping/ICMP or is all outgoing traffic blocked?

* The script only considers IPv4 addresses because I haven't had any success in passing IPv6 addresses to New-AzureRmSqlServerFirewallRule.

Answer 1

If you're connecting over TCP, then why is your error message saying Named Pipes?

[...]

(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I'd look into this paradox first.

The firewall test is very simple, allow 0.0.0.0 to 255.255.255.255 or 0.0.0.0/0 and re-test. My money is on the same error message.

Answer 2

We finally solved the issue. The problem had nothing to do with Firewalls. The issue was that the app.config files in our unit test didn't go through the transformation step that our web.config files did. So all the settings were from our local development and therefore wrong.

More about this here:
Connect to external services inside Visual Studio Online build/test task

Answer 3

What connection string are you using? Your error seems to indicate that this is not truly a firewall issue, but rather a connection is being attempted to a server that doesn't exist.

My * incorrect * hypothesis right now is that your connection string contains only the server name, without .database.windows.net suffix which causes the client driver to look for server on local network. The error presented appears to not be a firewall related issue.

( Edited to reflect author feedback. )