SQL Server network Protocol (i)

SQL Server currently has 3 main protocols: Shared Memory, TCP/IP, and Named Pipe

Sharedmemory:

The fastest and simplest protocol for Shared memory, a client that uses the Sharedmemory protocol can only connect to a SQL Server instance on the same servers. If other protocols are incorrect, you can connect to the local server for troubleshooting via shared memory.

Tcp / ip:

TCP/IP is a widely used communication protocol on the Internet that includes standards for routing network protocols and provides advanced security features.

Namedpipe:

Named Pipe is a protocol developed for local area networks. Named pipes run on the basis of TCP, NetBEUI and other basic protocols, not a basic network transport protocol. When a client connects to a named pipe, it first accesses the server's ipc$ share, and access to the ipc$ share must pass through the Windows authentication protocol. You cannot use Named pipes to access SQL Server if you do not have permission to access the file system of the SQL Servers.

The basic process for a client to use a named pipe connection is as follows:

(1) SQL Server uses the CreateNamedPipe function to create a named pipe and listen to it.

(2) The client uses the CreateFile and WriteFile functions to attempt to connect to the server's named pipe.

If the client and SQL Server are on the same local network with Windows authentication, the named pipe protocol can be used. If the client and SQL Server are network segments, and Windows authentication is not so easy, use the TCP, IP protocol.

SQL Server Protocol selection: A network protocol configuration is enabled for the client to use. The client can also configure the order of the connection protocols so that the connection first attempts to connect to a protocol. The client can set an alias for the SQL Server service, specifying the protocol port for the connection. The client can cache the last connection information.

SQL Server Network configuration:

Open the SQL Server Configuration Manager, and select SQL Servers network configuration to set up the access protocol on the client side.

Execute the command Cliconfg.exe open the Client Network Utility, or you can set the.

The SQL Server network configuration in SQL Server Configuration Manager is saved in the registry location as:

Hkey_local_machine\software\microsoft\microsoftsql Server\mssql10_50.mssqlserver\mssqlserver\supersocketnetlib

SQL Server client protocol configuration:

Open SQL Server Configuration Manager, and select Native Client 10.0 configuration to set the access protocol for clients.

If you do not have the client tools installed, you can open registry modifications:

Hkey_local_machine\software\microsoft\mssqlserver\client

Sqlserveraliases (alias) configuration:

By default, SQL Server uses the shared memory protocol to connect to an on-premises instance of SQL Server, using TCP/IP or named pipes to connect to an instance of SQL Server on another computer. Create aliases when you use TCP/IP, named pipes, or VIA and want to provide a custom connection string when you want to connect using a name other than the server name.

By default, Microsoft Windows XP Service Pack 2 will enable Windows Firewall, which will turn off port 1433 by default. Because Microsoft SQL Server communicates over port 1433, if SQL Server is configured to use TCP/IP to listen for incoming client connections, you must reopen the port.

After you give the alias, the local client connects to the local server instance. See that you are using a TCP connection.

But the other computers in the LAN are connected but not connected

(Note: The server-side firewall is turned off.) The database also allows remote access. If the IP is normal, the server name ping is different and may be a DNS problem. Not really, change the local C:\WINDOWS\system32\drivers\hosts, bind the server name and the IP address. Again telnet check port)

Another way to connect is given additional connection parameters:; SERVER =192.168.1.11; DATABASE = Master, fill in the server name arbitrarily.

Port:

The SQL Server TCP/IP protocol listens to port 1433 by default, and the TCP/IP socket connection request will be monitored and accepted by SQL Servers on that port. The default port number can be changed, and when the TCP client and server are connected, the client must be assigned a dynamic port, which is assigned a range of 1024-5000 by default.

The registry path for setting TCP connection settings and port ranges is as follows:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

Port range (reserved by IANA): 0 to 1023

· Ephemeral port range: 1025 to 5000 registry setting values

· Can be used as a specific port: any unblocked port from 0 to 65535

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

· Available range of blocked ports: 5001 to 65535

The Registry reserves 1433 and 1434 ports:

More information: Overview of services for Windows server systems and network port requirements

SQL Server network Protocol (i)