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 folder:http://blog.csdn.net/dba_huangzj/article/details/37906349

I am not responsible for any legal liability whatsoever, without the consent of the author, whether or not it is published in the form of "original" or used for commercial purposes.

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

Objective:

SQL Server communication is based on TCP and udpport, assuming 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:

To configure Windows Firewall, follow these steps:

1. Open the "Control Panel" in the "Start" menu, you can also directly click on the network icon to open, then select "System and Security", click "Windows Firewall", and finally click "Advanced Settings",

SOURCE :http://blog.csdn.net/dba_huangzj/article/details/38082123, Theme folder: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 protocol and port:

• Assuming that only the default instance of the SQL Server engine is used, enter 1433
• Suppose you need to use the default instance caused by Analysis Services, enter 2383
• Suppose you use service Broker, enter 4022

Click "Next". Complete list of ports to access: http://msdn.microsoft.com/zh-cn/library/cc646023%28v=SQL.110%29.aspx

SOURCE :http://blog.csdn.net/dba_huangzj/article/details/38082123, Theme folder:http://blog.csdn.net/ dba_huangzj/article/details/37906349

3. Select "Agree to connect" in the "Action" Item and click "Next":

4. In the "Profile" item, assume that you are in the domain, only the "domain" option is checked, and the other two, mainly for workstations or notebooks. Assuming that the server is connected by a network that is identified as "public", such as direct access from the Internet, and that you want SQL Server to be externally discoverable and able to choose "public", of course, this is more critical. Finally, select Next and enter the name of the rule.

Principle:

SOURCE :http://blog.csdn.net/dba_huangzj/article/details/38082123, Theme folder:http://blog.csdn.net/ dba_huangzj/article/details/37906349

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

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

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

A lot of other information:

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

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

In addition, the ability to query a specific port with commands in SQL Server enables you to view the port in service Broker, mirroring using the following statement, assuming that you need to use these features to open the appropriate port:

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, execute this command as an administrator, and open TCP 1433port 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 folder:http://blog.csdn.net/dba_huangzj/article/details/37906349

A lot of other relevant information is available to access: (How to use the "netsh firewall" context instead of controlling windows Vista and Firewall behavior in Windows Server 2008 "netsh Firewalls" context)

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

Filed under: http://blog.csdn.net/dba_huangzj/article/details/38227187

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