Failed to add Azure Firewall firewall rule to allow server to run tests

Question

We use the Visual Studio build assembly server to automate the build process. As part of this, I am studying adding unit tests and integrating into this process.

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

I have a PowerShell script that uses the New-AzureRmSqlServerFirewallRule to add IP addresses to the database server, and these firewall rules are successfully displayed in the Azure portal.

In particular, the script adds firewall rules for:

• All IPv4 * addresses on the build server (as specified by Get-NetIPAddress )
• Create an external server IP (returns https://api.ipify.org )

In conjunction with this, the predefined rules AllowAllAzureIPs and AllowAllWindowsAzureIps are automatically added.

However, the tests subsequently fail with the exception:

System.Data.SqlClient.SqlException: System.Data.SqlClient.SqlException: network-related or a specific instance failed to establish a connection to SQL Server. The server was not found or was not available. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: named pipe provider, error: 40 - Could not open connection to SQL Server)

I'm not sure why the build server cannot contact the database server - maybe the test process host uses another IP address?

Update
As already indicated, the exception message mentions a “name provider”, which assumes that the database connection uses the named pipe instead of the IP / TCP connection. To check this, I changed the local app.config file to contain an unknown / random / inaccessible IP address and ran the tests locally (otherwise they ran successfully locally): I got the exact same exception message that mentioned "Provider named pipe names. " Perhaps at some level, the ReliableSqlConnection class allows a named pipe, but I want to say that I can throw the same exception by going to an unknown or inaccessible IP address in my DB connection string.

In addition, the DB connection string begins with tcp: which, according to this blog post , explicitly tells the connection to use TCP / IP and unnamed pipes.

I also changed the firewall rule to allow all IP addresses (from 0.0.0 to 255.255.255.255), but the same exception still throws. This suggests that the Azure SQL Firewall rule does not cause a "block".

Therefore, my suspicion turns into blocking access to the network (although there is probably a white list so that the build server can get into 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 

The result is

Failed to check the connection to the computer "172.217.18.100": error due to lack of resources

Did build servers disable ping / ICMP or is all outgoing traffic blocked?

* The script only considers IPv4 addresses, since I was not able to succeed in transferring IPv6 addresses to New-AzureRmSqlServerFirewallRule .

Answer 1

Finally we solved the problem. The problem had nothing to do with firewalls. The problem was that the app.config files in our unit test did not go through the transformation phase that our web.config files did. So, all the settings were from our local development and, therefore, incorrect.

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

Answer 2

What connection string are you using? Your error probably indicates that this is not a firewall problem, but rather an attempt to connect to a server that does not exist.

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

(Edited to reflect author reviews.)

Answer 3

If you connect via TCP, why does your error message say Named Pipes ?

[...]

(provider: name and name provider> , error: 40 - Could not open connection to SQL Server)

First I will look at this paradox.

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