允许远程连接到不同域中的 SQL Server 的防火墙规则 [英] Firewall rules to allow remote connection to SQL Server in a different domain

问题描述

1. 我最近在一系列运行 Windows Server 2012 的机器上安装了 SQL Server Express 2014.总共七台机器.

除了 1 个之外，其他所有人都住在同一个 local 域 company.local 中.

All but 1 of them live in the same local domain company.local.

一台流氓机器位于不同的公共域public.company.org.

A single rogue machine lives in a different public domain public.company.org.

1. 在单独域中的这台机器上的安装过程是由某人执行的，他不太注意该过程并且几乎选择了所有默认设置(也就是点击-点击-点击安装.) 因此，默认情况下未启用某些功能，例如 SQL Server 身份验证和远程连接.

1. The installation process on this machine on a separate domain was carried out by someone who was not paying much attention to the process and pretty much selected all defaults (a.k.a. a click-click-click installation.) As such, certain features such as SQL Server authentication and remote connectivity were not enabled by default.

我们希望通过 SQL Server 身份验证使用 SSMS 从同一本地域 company.local 中的开发机器远程连接.我们可以轻松开启此功能.

We want to connect remotely, via SQL Server Authentication, using SSMS, from development machines in the same local domain company.local. This we were able to easily turn on.

至于允许远程连接，我们发现自己遇到了更多困难.

As for allowing remote connections, we found ourselves in more difficulties.

1. 我们确保已定义防火墙规则以允许 TCP 端口 1433 和 UDP 端口 1434 上的入站连接.我们证实确实如此.

1. We ensured that firewall rules have are defined to allow inbound connection on ports 1433 for TCP and 1434 for UDP. We verified that that is the case.

我们已将此规则设置为适用于所有配置文件:域、公共、私有.

We have set this rule to apply to all profiles: Domain, Public, Private.

我们确保 SQL Server 配置管理器中允许 SQLEXPRESS 的所有协议，特别是 TCP/IP.

We ensured that all protocols for SQLEXPRESS are allowed in the SQL Server Configuration Manager, specifically TCP/IP.

我们已启用 SQL Server 浏览器.

在配置更改后，我们已多次重新启动所有服务.我们甚至重新启动了机器.

We have restarted all services multiple times after our configuration changes. We have even restarted the machine.

• 我们也踢了箱子.

我们仍然无法使用以下任一方法访问 SQLEXPRESS 实例:

We are still not able to the SQLEXPRESS instance using either:

• machine-name.public.company.com\SQLEXPRESS
• machine-name.public.company.com\SQLEXPRESS,1434
• \SQLEXPRESS
• \SQLEXPRESS,1434
• 使用 SSMS 或 SQLCMD
• 使用 SQL Server 身份验证，通过常规登录或 sa.
• 使用 Windows 身份验证，以及远程计算机上的凭据.

Ping 服务器表明它有响应.并且我们已经验证端口确实是开放的.

Pinging the server shows it is responsive. And we have verified that the ports are indeed open.

据我们所知，我们已经将 6 台本地机器的所有配置/环境设置修改、比较和匹配到不同域上的机器.

As far as we are aware of, we've revised, compared and match all configuration/environment settings from the 6 local machines to the one on the different domain.

在所有级别一起禁用防火墙时，我们能够连接.

When disabling firewall all together at all levels, we are able to connect.

当然，我们不想让门敞开着，所以我们向防火墙添加了入站规则，以允许与 SQL Server (SQLEXPRESS) 服务的各种连接.

Of course we don't want to leave the door open, so we added a inbound rule to the firewall to allow all sorts of connections to the SQL Server (SQLEXPRESS) service.

问题

那么问题是我们的做法是否正确?

QUESTION

So the QUESTION is if what we did is the correct approach?

为什么其他本地机器不需要为 SQL Server 进程本身添加这个防火墙例外???

推荐答案

我已经检查了您的帖子两次，但我找不到关于您是否允许远程连接到该 SQL Express 实例的任何信息.当然，您已允许所有协议，但这还不够.

I've checked your post twice, but I can't find anything about whether you've allowed remote connections to that SQL Express instance. You've allowed all protocols, sure, but that's not enough.

默认情况下，所有到 Express 实例的远程连接都被禁用(也包括 DAC).要检查此设置，请重新启动计算机(或 SQL 实例)并查看 SQL Server 日志.具体来说，您将对以SQL Server 正在侦听..."开头的条目感兴趣——在那里，您将找到实例正在侦听的所有协议、端口和地址.如果您看到的唯一 IP 是 127.0.0.1，那么实例将拒绝所有远程连接，无论防火墙或其他任何东西.

By default, all remote connections to Express instances are disabled (that includes DAC, too). To check this setting, restart the machine (or SQL instance) and look into SQL Server logs. Specifically, you will be interested in entries started with "SQL Server is listening on ..." - there, you'll find all protocols, ports and addresses the instance is listening on. If the only IP you're see there is 127.0.0.1, then instance rejects all remote connections, regardless of firewall or anything else.

我没有 2014 Express 来检查它，但在 2012 中，此设置是通过配置管理器在SQL Server 网络配置"部分中配置的.

I don't have 2014 Express to check it, but in 2012 this setting is configured via Configuration Manager, in the "SQL Server Network Configuration" section.

这篇关于允许远程连接到不同域中的 SQL Server 的防火墙规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！