Chapter 1 Securing Your Server and Network (6): configure the firewall for SQL Server access, chaptersecuring

Chapter 1 Securing Your Server and Network (6): configure the firewall for SQL Server access, chaptersecuring
Source: Workshop

Without the consent of the author, no one shall be published in the form of "original" or used for commercial purposes. I am not responsible for any legal liability.

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

 

Preface:

 

The communication between SQL Server is based on TCP and UDP ports. If you need to access SQL Server from a non-local machine, you need to open the firewall port. In Windows Server 2008, the default firewall is enabled. Simply put, all ports are blocked.

 

Implementation:

 

Follow these steps to configure Windows Firewall:

1. open Control Panel in the Start menu, or click the Network icon to open it. Then, select system and security, and click Windows Firewall ], finally, click Advanced Settings ],

 

 

Source: Workshop

2. Right-click inbound rules, select new rule, select port in rule type, and enter the following port in protocol and Port:

• If you only use 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 use Service Broker, enter 4022

Click Next ]. Complete port list accessible: http://msdn.microsoft.com/zh-cn/library/cc646023%28v= SQL .110%29.aspx

Source: Workshop

3. Select allow connection in operation and click Next ]:

4. In configuration file, if you are in a domain, only select the domain option. The other two are mainly used for workstations or laptops. If the Server is connected through a network marked as "public", such as direct access from the Internet, and you want SQL Server to be discovered externally, you can choose "public". Of course, this is dangerous. Click Next and enter the rule name.

Principle:

Source: Workshop

The preceding describes how to open the port of the default SQL Server instance, such as TCP 1433. The named instance uses a dynamic port, and the port may change after each SQL Server Service restart. This port uses the SQLServer Browser Service to listen to UDP port 1434 for client communication. Dynamic ports are not suitable for firewall configuration, because using dynamic ports will force you to open a "range" port.

The best way is to specify a fixed TCP port that has been configured in the firewall to the named instance, disable SQL Server Browser, and then disable UDP 1434 in the firewall. This section will be introduced in the next article.

Note: The default dynamic port range has changed since Windows Server 2008/Vista, where you can view details: http://support.microsoft.com/kb/929851

 

For more information:

 

To restrict specific users or machines to access SQL Server, you can use the firewall's inbound rules to find the corresponding service in inbound rules, for example, [SQL Server], right-click and click [properties], and select [allow only secure connections] on the [general page ],

 

Then, grant access to a specific user or computer in the remote user and remote computer tabs:

 

In addition, you can use commands in SQL Server to query specific ports. You can use the following statement to view the ports in the Service Broker and image. To use these functions, you must 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 also use the netsh.exe command to implement firewall configuration, run this command as an administrator, and open TCP port 1433 with the following statement:

netsh advfirewall firewall add rule name = "SQL Server" dir = in protocol = tcp action = allow localport = 1433,2383 profile = DOMAIN

Source: Workshop

For more information, visit: (how to use the "netsh by firewall" context, instead of controlling the Windows Firewall behavior "netsh firewall" context in Windows Vista and Windows Server 2008)

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

Detailed firewall configuration for SQL Server Remote connection Server

We recommend that you enable port 1433 in the firewall.
 
After you click "run" in SQL server 2008, a message is displayed indicating to configure the firewall for remote debugging. To enable DCOM, TCP and UD are added to the firewall.

Is it win7? Win2012? Just execute as Administrator