Verifying the Configuration of SQL Server Ports

Created by Unknown User (adimopoulos) on Feb 25, 2014

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

\n

\n \n \n \n \n \n \n \n \n \n \n \n \n

Related Documentation	Version of up.time affected	Affected Platforms
Configuring SQL Server Ports \n\t SQL Server Locks	All	All

\n

\n \n

Verifying the configuration of a SQL Server port is a two-step process:

\n \n

Finding the TCP/IP port
Verifying the configuration of the port

\n \n

Finding the TCP/IP Port

\n \n

To find the TCP/IP port of a SQL Server instance:

\n \n

Start SQL Query Analyzer, and then connect to the SQL Server instance.
Run the following Transact-SQL statement in SQL Query Analyzer: \n
```
\nUse master
\nGo
\nXp_readerrorlog
\n
```
\n
In the Results pane, locate the following text: \n
```
\nSQL server listening on X.X.X.X: Y
\n
```
\n
Where X.X.X.X is the IP address of the SQL Server instance and Y is the TCP/IP port on which SQL Server is listening.
\n
For example, if SQL server is listening on 10.150.158.246: 1433, 10.150.158.246 is the IP address of the SQL Server and 1433 is the TCP/IP port on which the SQL Server instance is listening.
\n

\n \nVerifying the Configuration of the Port

\n

To verify the port configuration of a SQL Server instance:

\n

Start the Windows Registry Editor.
In Registry Editor, locate the following registry key: \n
```
\nHKEY_LOCAL_MACHINESOFTWAREMicrosoft
\nMicrosoft SQL Server
\nMSSQLServerSuperSocketNetLibTcp
\n
```
\n
The TCPDynamicPorts and TCPPort values appear as follows, depending on your port allocation method:
\n
- Static Port Allocation \n
  If you configure an instance of SQL Server to use a static port, and you have not yet restarted the SQL Server instance, the registry values are set as follows:
  \n
  TCPDynamicPorts = Last port used
  \n
  TCPPort = New static port that you set by using the Server Network Utility.
  \n
  If you configure an instance of SQL Server to use a static port, and you restart the SQL Server instance, the registry values are set as:
  \n
  TCPDynamicPorts = Blank
  \n
  TCPPort = New static port that you set by using the Server Network Utility.
  \n
- Dynamic Port Allocation \n
  If you configure an instance of SQL Server to use dynamic port allocation, and you have not yet restarted the SQL Server instance, the registry values are set as follows:
  \n
  TCPDynamicPorts = Blank
  \n
  TCPPort = 0
  \n
  However, if you configure an instance of SQL Server to use dynamic port allocation, and you restart the SQL Server instance, the registry values are set as follows:
  \n
  TCPDynamicPorts = Current port used
  \n
  TCPPort = Current port used
  \n
\n

\n

No labels