Chapter 1 Securing Your Server and Network (6): Configuring Firewalls for SQL Server access

Original source: http://blog.csdn.net/dba_huangzj/article/details/38082123, featured catalogue:http://blog.csdn.net/dba_huangzj/article/details/37906349

No person shall, without the consent of the author, be published in the form of "original" or used for commercial purposes, and I am not responsible for any legal liability.

Previous article: http://blog.csdn.net/dba_huangzj/article/details/38063823

Objective:

SQL Server communication is based on TCP and UDP ports, and if you need to access SQL Server from a non-native machine, you need to open the port of the firewall. The Windows Server 2008 default firewall is turned on. Simply put, all ports are blocked.

Realize:

You can configure Windows Firewall by following these steps:

1. Open the Control Panel from the Start menu, or click the Network icon to open it, then select "System and Security", click "Windows Firewall", and then click "Advanced Settings"

SOURCE :http://blog.csdn.net/dba_huangzj/article/details/38082123, Special catalogue:HTTP://BLOG.CSDN.NET/DBA _huangzj/article/details/37906349

2. Right-click Inbound Rules, select New rule, select port in rule type, and then enter the following port in protocols and ports:

• If you are using only the default instance of the SQL Server engine, enter 1433
• If you need to use the default instance caused by Analysis Services, enter 2383
• If you are using service Broker, enter 4022

Click "Next". A complete list of ports can be accessed: http://msdn.microsoft.com/zh-cn/library/cc646023%28v=SQL.110%29.aspx

SOURCE :http://blog.csdn.net/dba_huangzj/article/details/38082123, Special catalogue:HTTP://BLOG.CSDN.NET/DBA _huangzj/article/details/37906349

3. In the "Action" item, select "Allow Connection" and click "Next":

4. In the "Profile" item, if you are in the domain, only the "domain" option is checked, and the other two is used primarily for workstations or notebooks. If the server is connected by a network identified as "public", such as direct access from the Internet, and you want SQL Server to be externally discoverable, you can choose "public", which is, of course, more dangerous. Finally, select Next and enter the name of the rule.

Principle:

SOURCE :http://blog.csdn.net/dba_huangzj/article/details/38082123, Special catalogue:HTTP://BLOG.CSDN.NET/DBA _huangzj/article/details/37906349

The above describes how to open the port for the default instance of SQL Server, such as TCP 1433. A named instance uses a dynamic port, and the port may change every time the SQL Server service restarts. This port is for client-side communication through the SQL Server Browser service listening on UDP 1434 ports. Dynamic ports are not available for firewall configuration because using dynamic ports forces you to open a "range" port.

The best way to do this is to specify a fixed TCP port that is already configured in the firewall to the named instance, disable SQL Server Browser, and then close the UDP 1434 port in the firewall. This section is described in the next article.

Note: Starting with Windows Server 2008/vista, the default dynamic port range has changed, and details can be viewed from here: http://support.microsoft.com/kb/929851

More information:

In order to restrict a specific user or machine access to SQL Server, you can use the firewall's "inbound rules" implementation, you can find the corresponding services in the "inbound rules", such as "SQL Server", right-click "Properties", on the "General page" check "Allow only secure connection",

Then grant specific users or computers access in the remote users and remote computers tabs, respectively:

In addition, you can query a specific port with commands in SQL Server, you can use the following statement to view the ports in the service Broker, the mirrors, and if you need to use these features, open the corresponding ports:

SELECT  name,         Protocol_desc,         port,         state_desc from    sys.tcp_endpoints WHERE   type_desc in (' Service_broker ', ' database_mirroring ');

In addition to graphical operations, you can use the Netsh.exe command to implement a firewall configuration, run this command as an administrator, and open the TCP 1433 port with the following statement:

netsh advfirewall firewall add rule name = "SQL Server" DIR = in protocol = TCP action = Allow LocalPort = 1433,2383 ProFi Le = DOMAIN

Original Source:Http://blog.csdn.net/dba_huangzj/article/details/38082123, featured catalogue:http://blog.csdn.net/dba_huangzj/article/details/37906349

For more information, you can access: (How to use the "netsh firewall" context instead of controlling Windows Vista and window firewall behavior in Windows Server 2008 "netsh Firewalls" context)

http://support.microsoft.com/kb/947709