To get the query text, the sys.dm_exec_sql_text DMV can be used with the complete script below: We can optimize the use of this DMV, as it returns only the SQL handle for that session and not the actual query text. This DMV returns all connected sessions with the TCP port, source IP and other useful information. If the SQL Server instance is configured to listen on multiple TCP ports, you can identify the TCP port used by each SQL Session using the sys.dm_exec_connections DMV. Another option is to create an alias on the client’s machine that specifies the port number for that SQL Server instance. You can also customize the connection string that the client uses by specifying the ServerName,TCP_Port_Number rather than using the server name only. In order to allow clients to connect to the SQL Server instance with the new TCP port, you need to enable and start the SQL Server Browser Service, which helps the clients in getting the TCP port for that SQL Server instance. Once you finish with all IP addresses, click OK.Ĭhanging the TCP protocol that the SQL Server Instance is listening on requires restarting the SQL Server service to take effect, which can be done from the SQL Server Services node of the SQL Server Configuration Manager console as described previously.Ĭhanging the default TCP port of the SQL Server instance will prevent clients from connecting to the SQL Server instance without specifying that new port. In the TCP Port box under each IPn node, type the TCP port number that you want this IP address to listen on. First, you need to make sure that the TCP Dynamic Ports box is empty, preventing the SQL Server instance from using different TCP port on each server restart. At the details area in the right, right-click on the TCP/IP protocol and choose Properties:įrom the TCP/IP Properties window, choose the IP Addresses tab, where you can see a node for each IP address on that server, in addition to the IP 127.0.0.1 loopback adaptor. To configure the SQL Server instance with a specific TCP port, open the SQL Server Configuration Manager and expand the SQL Server Network Configuration node to view the Protocols for. It is better to configure the SQL Server named instance to listen on a specific static port rather than using dynamic ports, in order to configure the firewall to allow communication to the SQL Server on that specific TCP port. SQL Server Bowser Service helps the client identifying that used TCP port when he tries to connect. This protocol connects computers with different hardware and operating systems specs and allows it to communicate together, as it includes network traffic routing standards with advanced security protection.īy default, the SQL Server instance listens on TCP port 1433, where the named instances located in the same server will be configured for dynamic TCP ports, selecting an available TCP port each time the SQL Server service restarted. The most commonly used network protocol in SQL Server is the TCP/IP protocol. Shared Memory protocol can be used to troubleshoot other network protocols if these protocols are not configured correctly. SQL Server Shared Memory protocol is the simplest protocol, as it has no configurable settings to be tuned in order to use it. SQL Server Shared Memory protocol is used by clients to connect to the SQL Server instance that is running on the same machine. From the details area in the right, right-click on the SQL Server () service for the configured SQL Server instance and choose Restart. From the SQL Server Configuration Manager, click on the SQL Server Services. At the details area in the right, right-click on the protocol you are interested in and click on Enable or Disable as below:Įnabling or disabling the SQL Server network protocols requires restarting the SQL Server service to take effect, which can be done from the same console. To enable or disable a specific network protocol, open the SQL Server Configuration Manager and expand the SQL Server Network Configuration node to view the Protocols for. All these network protocols are installed by default when installing the SQL Server instance, but you need to enable one or more network protocols that the clients will use to communicate with the SQL Server. There are three main network protocols that you can configure in SQL Server.
SQL Server Network Configuration can be managed using the SQL Server Configuration Manager tool.
It also provides the means to encrypt the communication between the SQL Server instance and the client applications and hide the SQL Server instance from being browsed. SQL Server Network Configuration involves enabling the protocols that manage the connection to the SQL Server and configuring the available options for these network protocols.
0 kommentar(er)
