How to find the port for MS SQL Server 2008

Question

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

User · Answer

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

User · Answer

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  lt ipv4 gt  1433

User · Answer

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  lt  gt  1433

User · Answer

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    Step 4  In TCP IP Properties dialog box  go to IP Addresses tab and scroll down to IPAll group     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

User · Answer

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

User · Answer

Click on Start button in Windows  Go to All Programs - gt  Microsoft SQL Server 2008 - gt  Configuration Tools - gt  SQL Server Configuration Manager Click on SQL Native Client 10 0 Configuration - gt  Client Protocols - gt  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

User · Answer

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

User · Answer

You could also look with a  netstat -abn   It gives the ports with the corresponding application that keeps them open   Edit  or TCPView

User · Answer

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

User · Answer

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   gt tasklist   find  i  sqlservr exe  sqlservr exe  1184 Services    0 3 181 800 KB  C   gt 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

User · Answer

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   lt ipv4 gt  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  amp  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

User · Answer

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

User · Answer

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

[sql-server] How to find the port for MS SQL Server 2008?

Examples related to sql-server

Examples related to sql-server-2008