6

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.

awj
  • 7,482
  • 10
  • 66
  • 120
  • I was under the impression that it launches a build agent on a separate vm? – 4c74356b41 Dec 15 '16 at 13:39
  • The build agent runs the PowerShell script, so any IP address that it picks up should be those of the build server, right? – awj Dec 15 '16 at 13:41
  • well, can you just allow IP through firewall and wait 5 minutes so they get applied? – 4c74356b41 Dec 15 '16 at 13:43
  • When you add a firewall rule through the Azure Powershell cmdlets they appear instantly in the Azure portal. No 5 minute wait needed. Unless you know something I don't - is there a delay before new rules are implemented? – awj Dec 15 '16 at 14:39
  • well, i know that when you delete a rule actual access isn't being prevented for a few more minutes. unless you flush permission on the sql server. – 4c74356b41 Dec 15 '16 at 15:35
  • I've tried moving the firewall-rules script to the start of the process - the tests aren't run until 13 or 14 minutes later; same result. – awj Dec 15 '16 at 16:00
  • First - build agent is not necessarily running on the same host as build server. Second - can it be that outgoing connections are disabled at the agent itself? – mikalai Dec 15 '16 at 16:37
  • Your first point is valid - and whether or not this is the actual truth is what I'm hoping someone can tell me. As for the second point, like I said in the OP, I can check in the Azure portal and see that the firewall rule has been added, so that connection is certainly not blocked. – awj Dec 15 '16 at 16:43
  • Are you certain that the IP you're opening in the firewall is the one where you're running tests? (Have you ruled out a test-side connectivity issue?) – Dan Rediske Dec 15 '16 at 22:32
  • @DanRediske-MSFT - No, and that's what I replied to mikalai that I'm hoping to find out. I'm adding rules for all IPv4 addresses detected on the build server but I don't have any more information regarding the location where the tests are being run, whether on the build server or elsewhere. I was hoping that this SO post would prompt someone who knows about such things to illuminate this topic, but thus far I've only had two people asking _me_ that question. – awj Dec 16 '16 at 08:00
  • I actually meant *outbound* network permissions, defined at the build agent - which probably you cannot control. I'd execute some connectivity tests to several addresses and ports, just in case. There's a article on similar topic - http://tech.trailmax.info/2016/10/database-integration-tests-in-visual-studio-team-services-and-cake-build/ but author uses local db. – mikalai Dec 16 '16 at 08:49
  • Just curious - can "provider: Named Pipes Provider" be a reason? I've been using it for local connections only, having TCP provider by default. – mikalai Dec 18 '16 at 19:15
  • What (and where) are you suggesting I change? – awj Dec 18 '16 at 19:19
  • That's usually configured in "Client Protocols" section of Sql server configuration manager. – mikalai Dec 18 '16 at 19:23

3 Answers3

1

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

Community
  • 1
  • 1
Remy
  • 12,555
  • 14
  • 64
  • 104
  • Are we saying that a specific firewall rule is not required? Does this mean any VSTS hosts can access Azure SQL servers? Or is it only for Azure instances that have been associated to that VSTS instance? – Kevin Jun 17 '17 at 02:50
  • No, we were looking in the wrong corner. VSTS hosts can access Azure SQL. – Remy Jun 19 '17 at 07:39
0

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.

Firewall error example

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. )

Dan Rediske
  • 852
  • 4
  • 14
  • Thanks for the suggestion but I'm afraid your working hypothesis appears to be wrong: the connection string starts with `connectionString="Server=tcp:MY_DATABASE_NAME.database.windows.net,1433;...` – awj Dec 17 '16 at 17:52
  • Could you post your entire connection string along with the provider name? – Luca Cappa Jan 12 '17 at 20:05
0

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.

Community
  • 1
  • 1
evilSnobu
  • 24,582
  • 8
  • 41
  • 71