SQLServer network protocol (I), sqlserver network protocol

SQLServer network protocol (I), sqlserver network protocol

Currently, SQLserver has three main Protocols: Shared Memory, TCP/IP, and Named Pipe.

 

SharedMemory:

Shared Memory is the fastest and simplest protocol. Clients using the SharedMemory Protocol can only connect to SQLserver instances on the same server. If other protocols are incorrect, you can use Shared Memory to connect to the local server for troubleshooting.

 

TCP/IP:

TCP/IP is a widely used communication protocol on the Internet. It includes the standard of the routing network protocol and provides advanced security functions.

 

NamedPipe:

Named Pipe is a protocol developed for LAN. The named pipe runs on TCP, NETBEUI, and other basic protocols. It is not a grassroots network transfer protocol. When the client connects to the named pipe, it first accesses the server's IPC $ share, and the access to IPC $ share must pass the Windows authentication protocol. If you do not have the permission to access the file system of the SQLserver server, you cannot use the named pipe to access SQLserver.

The basic process of connecting a client to a named pipe is as follows:

(1) The SQLserver server uses the createNamedPipe function to create and listen to the named pipe.

(2) The client uses the createFile and writeFile functions to try to connect to the named pipe of the server.

 

 

If the client and SQLserver are in the same local network and can pass Windows authentication, you can use the Named Pipe protocol. If the client and SQLserver are CIDR blocks and Windows authentication is not so easy, TCP and IP protocols are used.

 

Select SQLserver Protocol: select a network protocol for the server network protocol configuration. The client can also configure the order of the connection protocol so that the connection tries to connect to a protocol first. The client can set the service alias to specify the protocol port for connection. The client can cache the last connection information.

 

 

SQLserver Network Configuration:

 

Open "SQLserver Configuration Manager" and select "SQLserver network configuration" to set the server access protocol.

Execute the command cliconfg.exe to open "client network utility", you can also set.

The "SQLserver network configuration" in the SQLserver Configuration Manager is saved in the registry:

HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ MicrosoftSQL Server \ MSSQL10_50.MSSQLSERVER \ MSSQLServer \ SuperSocketNetLib

 

SQL Server Client Protocol Configuration:

 

Open "SQLserver Configuration Manager" and select "SQL Native Client 10.0 configuration" to set the access protocol for the Client.

 

If the client tool is not installed, open the registry and modify it:

HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ MSSQLServer \ Client

 

SQLserveraliases (alias) Configuration:

 

By default, SQL Server uses the "Shared Memory" protocol to connect to the local instance of SQL Server, and uses "TCP/IP" or "named pipe" to connect to the SQL Server instance on other computers. Create an alias in the following cases: when using TCP/IP, named pipe, or VIA, and you want to provide a custom connection string, or when you want to use a name other than the server name for connection.

By default, Microsoft Windows XP Service Pack 2 enables Windows Firewall, which disables port 1433 by default. Microsoft SQL Server communicates with each other through port 1433. Therefore, if you configure SQL Server to use TCP/IP listener to pass in the client connection, you must re-open the port.

 

 

After the alias is given, the local client can connect to the local server instance. The TCP connection is used.

 

 

However, other computers in the LAN cannot be connected.

(Note: The server firewall has been disabled. The database also allows remote access. If the ip address is normal and the server name is pinged differently, DNS problems may occur. No. Change the local C: \ WINDOWS \ system32 \ drivers \ hosts and bind the server name and IP address. Telnet to check the port)

 

Another connection method is to specify additional connection parameters:; SERVER = 192.168.1.11; DATABASE = master, and the SERVER name can be entered as needed.

 

 

 

Port:

 

SQLserver TCP/IP listens to port 1433 by default. SQLserver server listens to and accepts client TCP/IP socket connection requests on this port. You can change the default port number. When the TCP client and the server are connected, the client must allocate a dynamic port. By default, the dynamic port range is 1024-5000.

 

The Registry path for setting TCP connection settings and port range is as follows:

HKEY_LOCAL_MACHINE \ SYSTEM \ CurrentControlSet \ services \ Tcpip \ Parameters

 

Port range (reserved by IANA): 0 to 1023

· Temporary port range: 1025 to 5000 Registry Setting Value

· Available as a specific port: Any unblocked port from 0 to 65535

· Available range of reserved ports: 1025 to 5000 and 49152 to 65535

· Available range of Blocked ports: 5001 to 65535

 

The Registry reserves ports 1433 and 1434:

 

For more information, see Windows Server overview and network port requirements.