How to find the port for MS SQL Server 2008?_问答_开发者

I am running MS SQL Server 2008 on my local machine. I know that the default port is 1433 but some how it is not listening at this port. The SQL is an Express edition.

I have already tried the log, SQL Server Management Studio, registry, and extended stored procedure for finding the port. But, I could not find it. Please help 开发者_开发技巧me. Thanks.

Click on Start button in Windows.

Go to All Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager

Click on SQL Native Client 10.0 Configuration -> Client Protocols -> TCP/IP double click ( Right click select Properties ) on TCP/IP.

You will find Default Port 1433.

Depending on connection, the port number may vary.

You could also look with a

netstat -abn

It gives the ports with the corresponding application that keeps them open.

Edit: or TCPView.

Here are 5 methodes i found:

Method 1: SQL Server Configuration Manager
Method 2: Windows Event Viewer
Method 3: SQL Server Error Logs
Method 4: sys.dm_exec_connections DMV
Method 5: Reading registry using xp_instance_regread

Method 4: sys.dm_exec_connections DMV
I think this is almost the easiest way...
DMVs return server state that can be used to monitor SQL Server Instance. We can use sys.dm_exec_connections DMV to identify the port number SQL Server Instance is listening on using below T-SQL code:

SELECT local_tcp_port
FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID
GO

Result Set:
local_tcp_port
61499

(1 row(s) affected)

Method 1: SQL Server Configuration Manager

Step 1. Click Start > All Programs > Microsoft SQL Server 2012 > Configuration Tools > SQL Server Configuration Manager

Step 2. Go to SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for

Step 3. Right Click on TCP/IP and select Properties

How to find the port for MS SQL Server 2008?

Step 4. In TCP/IP Properties dialog box, go to IP Addresses tab and scroll down to IPAll group.

How to find the port for MS SQL Server 2008?

If SQL Server if configured to run on a static port it will be available in TCP Port textbox, and if it is configured on dynamic port then current port will be available in TCP Dynamic Ports textbox. Here my instance is listening on port number 61499.

The other methods you can find here: http://sqlandme.com/2013/05/01/sql-server-finding-tcp-port-number-sql-instance-is-listening-on/

I came across this because I just had problems creating a remote connection and couldn't understand why setting up 1433 port in firewall is not doing the job. I finally have the full picture now, so I thought I should share.

First of all is a must to enable "TCP/IP" using the SQL Server Configuration Manager under Protocols for SQLEXPRESS!

When a named instance is used ("SQLExpress" in this case), this will listen on a dynamic port. To find this dynamic port you have couple of options; to name a few:

checking ERRORLOG of SQL Server located in '{MS SQL Server Path}\{MS SQL Server instance name}\MSSQL\Log' (inside you'll find a line similar to this: "2013-07-25 10:30:36.83 Server Server is listening on [ 'any' <ipv4> 51118]" --> so 51118 is the dynamic port in this case.
checking registry: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\{MSSQL instance name}\MSSQLServer\SuperSocketNetLib\Tcp\IPAll, for my case TcpDynamicPorts=51118.

Edit: {MSSQL instance name} is something like: MSSQL10_50.SQLEXPRESS, not only SQLEXPRESS

Of course, allowing this TCP port in firewall and creating a remote connection by passing in: "x.x.x.x,51118" (where x.x.x.x is the server ip) already solves it at this point.

But then I wanted to connect remotely by passing in the instance name (e.g: x.x.x.x\SQLExpress). This is when SQL Browser service comes into play. This is the unit which resolves the instance name into the 51118 port. SQL Browser service listens on UDP port 1434 (standard & static), so I had to allow this also in server's firewall.

To extend a bit the actual answer: if someone else doesn't like dynamic ports and wants a static port for his SQL Server instance, should try this link.

In the ERROLOG log for a line like below. If you don't see it the SQL Server isn't enabled for remote access, or it is just not via TCP. You can change this via the SQL Server Configuration Manager.

Server is listening on [ 192.128.3.2 <ipv4> 1433].

Try this (requires access to sys.dm_exec_connections):

SELECT DISTINCT 
    local_tcp_port 
FROM sys.dm_exec_connections 
WHERE local_tcp_port IS NOT NULL

I solved the problem by enabling the TCP/IP using the SQL Server Configuration Manager under Protocols for SQLEXPRESS2008, i restarted the service and now the "Server is listening on" shows up in the ERRORLOG file

I use the following script in SSMS

SELECT
     s.host_name
    ,c.local_net_address
    ,c.local_tcp_port
    ,s.login_name
    ,s.program_name
    ,c.session_id
    ,c.connect_time
    ,c.net_transport
    ,c.protocol_type
    ,c.encrypt_option
    ,c.client_net_address
    ,c.client_tcp_port
    ,s.client_interface_name
    ,s.host_process_id
    ,c.num_reads as num_reads_connection
    ,c.num_writes as num_writes_connection
    ,s.cpu_time
    ,s.reads as num_reads_sessions
    ,s.logical_reads as num_logical_reads_sessions
    ,s.writes as num_writes_sessions
    ,c.most_recent_sql_handle
FROM sys.dm_exec_connections AS c
INNER JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id

--filter port number
--WHERE c.local_tcp_port <> 1433

USE master
GO
xp_readerrorlog 0, 1, N'Server is listening on', 'any', NULL, NULL, N'asc' 
GO

[Identify Port used by Named Instance of SQL Server Database Engine by Reading SQL Server Error Logs]

You can use this two commands: tasklist and netstat -oan

Tasklist.exe is like taskmgr.exe but in text mode.

With tasklist.exe or taskmgr.exe you can obtain a PID of sqlservr.exe

With netstat -oan, it shows a connection PID, and you can filter it.

Example:

C:\>tasklist | find /i "sqlservr.exe"
sqlservr.exe  1184 Services    0 3.181.800 KB

C:\>netstat -oan | find /i "1184"
TCP  0.0.0.0:1280  0.0.0.0:0  LISTENING  1184

In this example, the SQLServer port is 1280

Extracted from: http://www.sysadmit.com/2016/03/mssql-ver-puerto-de-una-instancia.html

This may also be done via a port scan, which is the only possible method if you don't have admin access to a remote server.

Using Nmap (http://nmap.org/zenmap/) to do an "Intense TCP scan" will give you results like this for all instances on the server:

[10.0.0.1\DATABASE]    
Instance name: DATABASE
Version: Microsoft SQL Server 2008 R2 RTM    
Product: Microsoft SQL Server 2008 R2    
Service pack level: RTM    
TCP port: 49843    
Named pipe: \\10.0.0.1\pipe\MSSQL$DATABASE\sql\query

Important note: To test with query analyzer or MS SQL Server Management Studio you must form your server name and port differently than you would normally connect to a port, over HTTP for instance, using a comma instead of a colon.

Management Studio Server Name: 10.0.0.1,49843
Connection String: Data Source=10.0.0.1,49843

however

JDBC Connection String: jdbc:microsoft:sqlserver://10.0.0.1:49843;DatabaseName=DATABASE

This works for SQL Server 2005 - 2012. Look for event id = 26022 in the error log under applications. That will show the port number of sql server as well as what ip addresses are allowed to access.

In addition to what is listed above, I had to enable both TCP and UDP ports for SQLExpress to connect remotely. Because I have three different instances on my development machine, I enable 1430-1435 for both TCP and UDP.