无法添加Azure DB防火墙规则以允许Build Server运行测试 [英] Unable to Add Azure DB Firewall Rule to Allow Build Server to Run Tests

问题描述

我们使用Visual Studio Online托管的构建服务器来自动化构建过程.作为此过程的一部分，我正在考虑在此过程中添加单元测试和集成测试.

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.

这些测试需要访问我们的SQL Azure数据库(其中两个，都在同一服务器上)，而这又需要通过数据库服务器的防火墙进行访问.

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.

我有一个PowerShell脚本，该脚本使用New-AzureRmSqlServerFirewallRule将IP地址添加到数据库服务器，并且这些防火墙规则已成功显示在Azure门户中.

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:

• 构建服务器上的所有IPv4地址*(由Get-NetIPAddress返回)
• 构建服务器的外部IP地址(由 https://api.ipify.org 返回) li>

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

结合起来，似乎会自动添加预定义的 AllowAllAzureIPs 和 AllowAllWindowsAzureIps 规则.

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:与网络相关或 建立与以下对象的连接时发生特定于实例的错误 SQL Server.服务器未找到或无法访问.核实 实例名称正确并且已将SQL Server配置为 允许远程连接. (提供者:命名管道提供者，错误:40- 无法打开与SQL Server的连接)

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)

我不确定为什么构建服务器无法访问数据库服务器-可能是测试进程的主机使用了另一个IP地址吗?

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?

更新
正如已经指出的那样，异常消息中提到了命名管道提供者"，这表明数据库连接正在使用命名管道而不是IP/TCP连接.为了对此进行测试，我将本地app.config更改为包含未知/随机/不可访问的IP，并在本地运行测试(否则它们将在本地成功运行):我收到了完全相同的异常消息，其中提到了命名管道提供程序".也许在某种程度上 ReliableSqlConnection 类解析为一个命名管道，但我的观点是，通过将数据库连接字符串中的IP地址更改为未知或不可访问，可以引发同样的异常.

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.

此外，数据库连接字符串以tcp:开头，根据

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.

我还修改了防火墙规则，以允许所有IP地址(0.0.0.0到255.255.255.255)，但仍然抛出相同的异常.这表明SQL Azure防火墙规则不是造成阻塞"的原因.

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

因此，我怀疑是网络访问被阻止(尽管可能存在白名单以允许构建服务器访问代码存储库).我在构建过程的开始添加了一个非常简单的PowerShell脚本:

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

这将导致

测试与计算机'172.217.18.100'的连接失败:由于缺少资源而导致错误

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

构建服务器已禁用ping/ICMP还是所有传出流量都被阻止了?

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

*该脚本仅考虑IPv4地址，因为我没有成功将IPv6地址传递给New-AzureRmSqlServerFirewallRule.

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

推荐答案

我们终于解决了该问题.该问题与防火墙无关.问题在于，单元测试中的app.config文件没有经过web.config文件所完成的转换步骤.因此所有设置均来自我们的本地开发，因此是错误的.

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.

有关此的更多信息:
连接到Visual内部的外部服务Studio Online的构建/测试任务

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

这篇关于无法添加Azure DB防火墙规则以允许Build Server运行测试的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！