SQL Server Connectivity Issues-TCP/IP

Original: SQL Server connectivity Issue-TCP/IP

Originating From: http://blogs.msdn.com/b/apgcdsd/archive/2012/02/24/ms-sql-server-tcp-ip.aspx

Basic principles of how TCP/IP works here's not a waste of saliva. Now this network age, who does not know TCP/IP AH. Don't tell me contradicting you have not heard of TCP/IP, so you have to make up your own lessons.

The TCP/IP protocol has two basic things, one is the IP address and the other is the port number. It is very simple to use the TCP/IP protocol on SQL Server. The first thing to do is to configure the TCP/IP protocol for the network card of the machine where SQL Server is located, and to get an address (either statically assigned or dynamically obtained from the DHCP server), then you need to do server-side provisioning with the tools provided by SQL Server.

One, the TCP/IP protocol on Microsoft SQL Server

On a server running SQL Server 2000, configure SQL Server to listen on the TCP/IP protocol by running the server-side Network Configuration tool (run Svrnetcn.exe):

On a server that is running Microsoft SQL Server 2005/2008, configuring the TCP/IP protocol requires the network under the SQL Server Configuration Manager tool Under Configuration. Below is the screenshot of this tool.

In fact, either SQL Server 2000 or SQL Server 2005/2008, the results of the configuration are stored in the registry HKEY_LOCAL_MACHINE \software\microsoft\microsoft SQL Server\ Mssql.<instanceid>\mssqlserver\supersocketnetlib under the various projects. Without a management interface, modifying the registry directly can achieve the same goal.

(Note that if you have only the default instance of SQL Server 2000 on your machine, the registry location will be slightly different, it is hkey_local_machine\software\microsoft\mssqlserver\ Mssqlserver\supersocketnetlib.)

After you configure the network protocol, you need to restart the SQL Server service for the changes to take effect. After you start, you need to check the SQL Server errorlog to verify that the protocols have been turned on properly.

Shared memory starts normally and you can see the following information.

2009-04-12 10:04:27.92 Server Server local connection provider is ready to accept connection on [\\.\pipe\s Qllocal\mssqlserver].

Named pipe starts Normally, you can see the following information.

2009-04-12 10:04:27.92 Server server named pipe provider is ready to accept connection on [\\.\pipe\sql\que RY].

TCP/IP starts normally, and you can see the IP address and port number that the instance of SQL Server is listening on. For example:

2009-04-12 10:04:27.92 Server is listening on [' any ' <ipv4> 1433].

--listens on port 1433 on all IP addresses on the server.

Or:

2008-06-12 15:01:58.150 Server is listening on [172.30.30.80 <ipv4> 1433].

--only listen on port 1433 on the specified IP address (172.30.30.80).

Here is also a small question to answer by the way. Some friends ask, if the IP address of the machine changes, there is no impact on SQL Server, what actions need to be done, etc. The answer is simple, and restarting the SQL Server service is fine. SQL Server automatically listens to the machine's new IP address without reinstalling or configuring SQL Server.

Ii. TCP/IP port number for Microsoft SQL Server 2000 snooping

Now let's talk about an important configuration called port number. In the server-side network Configuration tool or by selecting the TCP/IP protocol in Configuration Manager, click the Properties button to view the port number that SQL Server listens on:

( SQL Server 2000 Server-side Network Configuration tool )

(SQL Server 2005/2008 Configuration Manager)

This shows the TCP/IP port that SQL Server listens to. The default value is 1433. The SQL Server server listens on the port and accepts TCP/IP socket connection requests from the client. You can change this default to a different port number, as long as the port is not occupied by the system or another application. Generally more than 5000 of the port number can be used at will, or use less than 1024 of the operating system or other applications do not use the idle port. Why not use a port between 1024 and 5000? Because by default the operating system assigns the ports in this range to services or applications to use. To avoid potential port collisions, use values above 5000 or below 1024. In fact, regardless of the range, it is only necessary to be idle unused ports. Microsoft has a good technical document that discusses in detail the Windows system uses some port numbers, and the documentation is connected as follows:

Port Requirements for Microsoft Windows server systems

http://support.microsoft.com/?id=832017

Perhaps you would ask, is there a way to view SQL Server listening ports ah? Yes, you can use the netstat command. Running the "Netstat-an" command in a DOS window lists all the port numbers used in the system, and of course the ports that SQL Server listens to. The following is a section on the port numbers for SQL Server extracted from the output of the "Netstat-an" command:

Active Connections

Proto Local address Foreign address state

TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING

TCP 169.254.173.244:1433 169.254.173.244:3952 established

UDP 0.0.0.0:1434 *:*

The 1434 port listed above is a more interesting port, as described in the following section.

SQL Server supports a single machine to install multiple sets of instances, the default instance and the named instance. For the default instance, the default listening IP port is 1433. If you change the listening port of the default instance to a non-1433 port, then the client may not be able to connect to the default instance of SQL Server. At this point, you must use the Client Network tool to specify the IP port that SQL Server listens on or create a SQL Server alias that specifies an IP port. About the Client Network tool is detailed below.

Third, TCP/IP static port, dynamic port, SQL Server Browser and UDP 1434 port concept

First, discuss port 1434. What is the 1434 port for?

We all know that SQL Server supports multiple instances, and the default SQL Server instance uses 1433 ports. For the remaining named instances (Named Instance), the port number that is bound for each startup may be different. It is not possible for each user to remember the port number of SQL. A normal user of a database will only know the name and instance name of the database server. So how do you find the appropriate port number based on the server name and instance name? In the era of SQL Server 2000, the product group developed a set of SQL Server resolution protocols (SSRP) for listening on UDP 1434 ports. When SQL Server 2000 client net-library is connected to an instance of SQL Server 2000, only the network name and the instance name (such as Mysqlsrv\inst1) of the computer on which the instance is running will be required. When an application requests a connection to a SQL Server server, the Client network library sends a network package to the 1434 port of the SQL Server server. All computers running SQL Server 2000 instances are listening on this port. When the server receives a query network packet, it returns a packet containing all the instance listening information running on the server. For each instance, the packet reports the server net-library and the specific network address that the instance is listening on. The client application connects based on the network address that is returned. For example, for a named instance, the returned network packet contains the TCP/IP port that the SQL Server server is listening on, and the client application gets the port number before it can establish a connection with SQL Server.

The design concept itself is quite good. But in 2003, a virus called Slammer used a bug in the SSRP functional component to induce the SQL Server service to emit a large number of network packets on the UDP port, causing network congestion and crippling the database service. This virus is the most harmful virus to date associated with SQL Server. In order to avoid such tragic events happening again, SQL Server 2005 introduced the SQL Server Browser service to replace the original mechanism.

SQL Server Browser listens for UDP ports with the SQL Server Resolution Protocol (SSRP) and accepts unauthenticated requests. To prevent malicious users from exploiting this service to attack SQL Server servers, SQL Server Browser can be set to run in the security context of a low-privileged user, minimizing the chance of malicious attacks. A new user can be added to the sqlserverxxxxsqlbrowser$ local group. And you need to change the logon account by using SQL Server Configuration Manager. The minimum user rights for SQL Server browser are as follows:

• Deny access to the computer over the network
• Deny log on Locally
• Deny log on as a batch job
• Deny logon through Terminal Services
• Log on as a service
• Read and write SQL Server registry keys related to network traffic (ports and pipelines)

With this design, you can isolate the impact of a malicious network attack on the SQL Server service itself, and limit the impact on the entire server if SQL browser is attacked, by restricting the way SQL browser logs on to user permissions.

After you start SQL Server Browser, it starts and uses UDP 1434 ports. The SQL Server Browser reads the registry (all HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL. X\mssqlserver\supersocketnetlib), identify all instances of SQL Server on the computer, and indicate the ports and named pipes they use. When a server has two or more network cards, the SQL Server browser returns the first enabled port it encounters for SQL Server.

When a SQL Server client requests a SQL Server resource, the client data driver uses port 1434 to send a UDP message to the server. The SQL Server Browser will request the TCP/IP port or named pipe name of the instance. The client data driver then sends a request to the server to complete the connection using the port of the desired instance or a named pipe.

When the SQL Server Browser service is not running, you can still connect to SQL Server if you provide the correct port number or named pipe. If the default instance of SQL Server is running on port 1433, you can use TCP/IP to connect to this default instance.

However, if the SQL Server Browser service is not running, the following connection is not valid:

• In cases where all parameters, such as TCP/IP ports or named pipes, are not fully specified, the component attempts to connect to the named instance.
• A component that generates or passes the server/instance information that other components then want to use to reconnect.
• You connect to a named instance without providing a port number or pipe.
• Connect the DAC to a named instance or to the default instance without using TCP/IP 1433 ports.
• Enumerates the servers in SQL Server Management Studio, Enterprise Manager, or Query Analyzer.

If your application accesses SQL Server over the network, to stop or disable the SQL Server Browser service, you must assign a specific port number to each instance and specify that port number in the client application code. However, this method has the following problems:

• The client application code must be updated and maintained to ensure that it is connected to the correct port.
• If other services or applications on the server consume the ports that you select for each instance, it will cause the SQL Server instance to be unavailable.

So SQL Browser is very simple to do with this service, but it is very important for normal client connections. If some clients are not connected to SQL Server, report "SQL Server doesn ' t exist or access denied", you can try to specify the port to see if it can be connected. If this can be connected, it is generally because UDP 1434 is disabled on the network, you need to open this port on the firewall or gateway.

SQL browser itself has few problems. However, because of its relatively simple design, there are only 4 threads in the package receiving SSRP (2 to ipv4,2 to IPV6). Therefore, once these threads are terminated because of an exception, the SQL Browser service may appear to be running normally but there is a problem connecting the named instance. At this point you will see the following error in the Windows event log:

The sqlbrowser processing of requests against a particular IP address has encountered a critical error. Processing of requests on the address has been halted (event ID 14)

Microsoft has released a patch to solve this problem, see:

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

It is also important to note that SQL Browser startup accounts have read and write SQL Server registry entries related to network traffic (ports and pipelines) (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL. X\mssqlserver\supersocketnetlib) of power. If the registry does not read, SQL browser does not give an error, but does not tell the client what port and pipe information it wants, and also causes the client to not connect.

Discuss the static port again. This article begins by mentioning how to modify the ports that SQL Server listens on. If you configure a port that is non-0, then it is a static port. That is, we have specified a static port number that SQL Server will listen on. This is the same for both the default instance and the named instance. Just for the default instance, if the port is not 1433, then the client uses the Network Configuration tool to specify the default connection port or create a SQL alias to connect. For named instances, it is not the same. You can specify any port value. As long as SQL Server listens on that port, the client can connect by querying the port value of the named instance over port 1434.

Also, what happens to SQL Server in the event that the specified port is already occupied? For the default instance, SQL Server simply discards the listening TCP/IP protocol and logs a message that is similar to "Cannot listen on TCP ports." For named instances, it's smarter. The SQL Server named instance automatically selects the next free port to listen on.

What if I set the port for SQL Server listening to 0? This question is more interesting. The fact is, when set to 0 o'clock, either the default instance or the named instance, SQL Server is understood to be a port that needs to be dynamically configured for listening. Dynamic configuration means that they will automatically select a system-free port for listening. But remember, for the default instance, if you choose a port other than 1433, hey, you need to configure the default connection port or use an alias on the client using the Cliconfg.exe tool.

Does dynamically configuring a port mean that SQL Server randomly chooses an unused port after each reboot? That's not it. When restarted, SQL Server will try to listen for the port used before. If SQL Server cannot bind to the port, it is dynamically bound to another idle port.

Four. TCP/IP protocol configuration for clients

In most cases you do not need to configure the client because the TCP/IP protocol is enabled by default. As with Named pipes, you can use the Client Network Utility to configure the TCP/IP protocol. Client applications are connected by SQL Server by loading the data-driven controls of SQL Server. There are 2 main types of client data-driven libraries:

1. MDAC (Microsoft data Access Components)

Run Cliconfg.exe or from the start-to-program-->microsoft SQL Server-to-client network utility to recall.

The left side of the client Network Utility is a disabled protocol, and the right is the protocol that is enabled. If there is no TCP/IP protocol on the right, click the Enable button to enable it. Select the TCP/IP Click Properties button to view the default TCP/IP port for client connections. By default 1433 (see Figure 4). If the server default instance (not a named instance) listens on a port other than 1433, then you need to make a corresponding change to default port here. You can, of course, create a server alias as described in the Named Pipes section specifically specifying the port of the server. For named instances, the Client network library uses UDP 1434 ports to query the Listener protocol information for the server named instance.

You can configure the SQL Server alias on the client to explicitly specify the protocol that is used to connect to SQL Server. Note You can specify a port in the alias, or you can use the Dynamic Query Port feature.

When the default instance is configured to listen on a non-1433 port, we can let the client program find the default instance by configuring the default port or alias.

The configuration information is saved in the registry, under the Hkey_local_machine\software\microsoft\mssqlserver\client\supersocketnetlib subdirectory. can also directly change the value, the same can achieve the effect.

2. SQL Server Native Client

On a machine that does not have SQL Server 2005 or 2008 installed, the SQL Server Native Client is not installed by default. When you install the SQL Server 2008 or SQL Server client tools, Microsoft SQL Server Native Client 10.0 is installed. If the SQL Server 2005 version of SQL Server Native client is also installed on the computer, SQL Server Native client 10.0 will be associated with an earlier version of Microsoft SQL Server Native Client 9.0 is installed in parallel.

If you have SQL Server client tools installed, we can configure the client network protocol through SQL Server Configuration Manager. The configured method is similar to MDAC.

Figure 5-5

If you do not have this tool installed, you may need to modify the registry directly. Information for Microsoft SQL Server Native Client 9.0 is stored in the hkey_local_machine\software\microsoft\mssqlserver\client\ SNI9.0 below, the information for Microsoft SQL Server Native Client 10.0 is stored in the hkey_local_machine\software\microsoft\mssqlserver\client\ SNI10.0 below.

Five. Steps to resolve TCP/IP connectivity issues

Step 1: Verify that SQL Server is really listening on the TCP/IP protocol

In order to verify that SQL Server is indeed listening to the TCP/IP protocol, you can open SQL Server Query Analyzer, and then run the following command:

EXEC master. Xp_readerrorlog

In the result bar, if you see a line similar to the following, SQL Server has been listening to TCP/IP:

2000-08-31 21:47:01.52 Server SQL Server listening on 169.254.173.244:1433.

2000-08-31 21:47:01.52 Server SQL Server listening on 127.0.0.1:1433.

2000-08-31 21:47:01.53 Server SQL Server listening on TCP, Shared Memory.

If you find that SQL Server is not listening to the TCP/IP protocol, use the server-side Network Configuration tool (run Svrnetcn.exe to recall) to confirm that SQL Server listens for TCP/IP protocol configuration.

Step 2: Verify that the TCP/IP port that the server listens to is the same as the default value for client configuration or the value specified in the alias.

Use the Client Network Utility to check the client's Connection Agreement configuration to ensure that TCP/IP is enabled for the client. Of course, the default port for client connections needs to be consistent with the SQL Server listener. In addition, if you have an alias, you need to carefully see if the port you specified is correct. If the client's alias is set incorrectly, it can also cause connectivity problems.

Step 3: Check network connectivity.

To ensure that you are not only able to ping the IP address of the SQL Server server, you can also ping the SQL Server server name. If there is a problem with the ping server name, it indicates that there is a problem with the DNS or WINS server configuration, you can manually add the IP address and server to the Hosts file (the Hosts file in the System32\Drivers\Etc directory) as follows:

169.254.173.244 MySQLServer

If there is a problem with the ping IP address, then you have to check the network configuration including the hardware connection. Use the "Ipconfig/all" command on both the server and the client to check whether the server and client are on the same network.

Step 4: Use the Telnet command to check the port of SQL listening.

To verify the ports that SQL Server listens on, you can use the Telnet command. Assuming that the IP address of SQL Server is 192.168.1.1 and the port is 1234, you can run the following command:

TELNET 192.168.1.1 1234

If Telnet succeeds, the result will be a black screen with only the cursor flashing. If you don't succeed, you'll get a message that goes wrong. You need to continue to troubleshoot problems based on these error messages.

Step 5: Check the SQL Server access rights for the logged-on user.

As with Named pipes, you need to ensure that the client login (login) account has access to SQL Server. Refer to the Named Pipes section for this.

Note that if you use the Windows SYSTEM account instead of login for SQL Server itself, you will need to have access to the server resources, as with Named pipes. If the Windows System account does not authenticate with Windows, you naturally cannot access SQL Server. If you suspect a permissions issue with the Windows System account, you can use SQL Server login such as SA to try to log on.

SQL Server Connectivity Issues-TCP/IP