Find TCP/IP Sockets Net-Libraries port number used by SQL Server 2000

Thursday Jan 20th 2005 by Muthusamy Anantha Kumar aka The MAK
Share:

When managing many different SQL Server boxes, finding the port numbers used by all of the SQL Servers can be a tedious job. In this article, MAK discusses several methods for finding the Port numbers used by SQL Server.



When a DBA is managing many different SQL Server boxes, it can be a tedious job to find the port numbers used by all of the SQL Servers. In this article, I would like to discuss several methods for finding the Port numbers used by SQL Server.



Method 1

In method one, we are going to take advantage of the MS-DOS batch file, OSQL utility, and the extended stored procedure "xp_regread".

Method 1 can be used only if the SQL Server service in the listed machines is up and running and the SQL Login used in the Findport.bat has permission to access SQL server and has permission to query the registry of that machine.



Step 1

Create Folder C:\Findport, [Refer to Fig 1.0] and create C:\Findport\servers.txt. List all of the server names and the named instance names in the text file, as shown below.



SQL 
MyComp
MyComp\Blonde

I have published two different articles on how to find all of the SQL Servers on the network. You can use those articles to publish this servers.txt file. Please refer to Scanning the network for SQL Server for a range of IP addresses and Scanning the network for SQL Server.




[Fig 1.0]

Step 2

Create C:\Findport\port.sql as shown below

set nocount on
DECLARE @test varchar(20), @key varchar(100)
if charindex('\',@@servername,0) <>0
begin
set @key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\'
  +@@servicename+'\MSSQLServer\Supersocketnetlib\TCP'
end
else
begin
set @key = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer\Supersocketnetlib\TCP'
end
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
 @key=@key,@value_name='Tcpport',@value=@test OUTPUT
SELECT 'Server Name: '+@@servername + ' Port Number:'+convert(varchar(10),@test)

Download port.sql.

Step 3

REM Type: Batch File
REM Created by: MAK
REM Contact: mak_999@yahoo.com
REM Date created: Jan 1, 2005
REM Execute osql Servers.txt
for /f "tokens=1,2,3" %%i in (C:\Findport\Servers.txt) 
  do OSQL -S%%i -t0 -Usa -Psa -dMASTER -ic:\Findport\PORT.sql

(Please change -Usa and -Psa to your login and password according to your environment or remove -Usa -Psa and add -E to use windows authentication).

Download Findport.bat_

Step 4

Run the batch file as shown below. [Refer Fig 1.1]


[Fig 1.1]

C:\Findport\findport.bat > C:\Findport\Findport.log

Step 5

Open the c:\ Findport\Findport.log file. The result should look like that shown below.

C:\findport>REM Type: Batch File 
C:\findport>REM Created by: MAK 
C:\findport>REM Contact: mak_999@yahoo.com 
C:\findport>REM Date created: Jan 1, 2005 
C:\findport>REM Execute osql Servers.txt 
C:\findport>for /F "tokens=1,2,3" %i in (C:\Findport\Servers.txt) 
  do OSQL -S%i -t0 -Usa -Psa -dMASTER -ic:\Findport\PORT.sql 
C:\findport>OSQL -SSQL -t0 -Usa -Psa -dMASTER -ic:\Findport\PORT.sql 
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 
	------------------------------------------------------------------------------
	-----------------------------------------------------------------------
	--------------- 
 Server Name: SQL Port Number:2433
C:\findport>OSQL -SMyComp -t0 -Usa -Psa -dMASTER -ic:\Findport\PORT.sql 
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 
	------------------------------------------------------------------------------
	-----------------------------------------------------------------------
	--------------- 
 Server Name: MYCOMP Port Number:1433
C:\findport>OSQL -SMyComp\Blonde -t0 -Usa -Psa -dMASTER -ic:\Findport\PORT.sql 
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 
	------------------------------------------------------------------------------
	-----------------------------------------------------------------------
	--------------- 
 Server Name: MYCOMP\BLONDE Port Number:2433


Method 2

In method two, we are going to take advantage of VB-Scripting and WMI methods.

Method2 requires access to read the registry on all of the listed servers. SQL Server



Step 1

Create Folder C:\Findport folder [Refer Fig 1.0] and then create C:\Findport\servers.txt inside it. List all of the server names and the named instance names in the text file, as shown below.



SQL 
MyComp
MyComp\Blonde

I have published two different articles on how to find all of the SQL Servers on the network. You can use those articles to publish this servers.txt file. Please refer to Scanning the network for SQL Server for a range of IP addresses and Scanning the network for SQL Server.



Step 2

Create C:\Findport\port.vbs as shown below

'Objective: To find port used by sql server
'Created by: MAK
'Create Date: Jan 9, 2004

on error resume next
Const HKEY_LOCAL_MACHINE 	= &H80000002
Set iFSO = CreateObject("Scripting.FilesyStemObject")
Set oFSO = CreateObject("Scripting.FilesyStemObject")

InputFile="c:\Findport\servers.txt"
OutputFile="c:\Findport\FindPort.log"

Set ifile = iFSO.OpenTextFile(inputfile)  
Set ofile = ofso.createTextFile(OutputFile, True)

ofile.writeline Now()
ofile.writeline "Find Port process started"

Do until ifile.AtEndOfLine
sComputer	= ifile.readline
sComputer=trim(sComputer)
sComputer2=scomputer
if InStr(1,sComputer,"\",1) <>0 then
len1=instr(1,sComputer,"\",1)-1
sComputer=left(sComputer,len1)
len2=len(sComputer)-instr(1,sComputer,"\",1)
instance=right(sComputer2,len2)
sKey		= "SOFTWARE\MICROSOFT\Microsoft SQL  
Server\"+instance+"\MSSQLServer\Supersocketnetlib\Tcp"
else
sKey		= "SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer\Supersocketnetlib\Tcp"
end if
'msgbox "COmputer" & sComputer
'msgbox "instance" & instance
sMethod		= "GetStringValue"
hTree		= HKEY_LOCAL_MACHINE
sValue		= "TcpPort"
'mystring 	= 
'msgbox "winmgmts:{impersonationLevel=impersonate}//" & sComputer & "/root/default:StdRegProv"
Set oRegistry	=  
GetObject("winmgmts:{impersonationLevel=impersonate}//"&sComputer&"/root/default:StdRegProv")
Set oMethod	= oRegistry.Methods_(sMethod)
Set oInParam	= oMethod.inParameters.SpawnInstance_()
oInParam.hDefKey = hTree
oInParam.sSubKeyName = sKey
oInParam.sValueName = sValue
Set oOutParam = oRegistry.ExecMethod_(sMethod, oInParam)
'WScript.Echo sComputer
'WScript.Echo oOutParam.Properties_("sValue")
ofile.writeline "ServerInstance: "& SComputer2 & " Port: "& Cstr(oOutParam.Properties_("sValue"))
loop


ofile.writeline "Find Port process Completed"
ofile.writeline Now()

Download Findport.vbs_

Step 3

Execute the Findport.vbs as shown below. Refer to fig 1.2.

C:\Findport\Findport.vbs


Fig 1.2

Step 4

The Findport.vbs will complete by showing the following message. Refer to Fig 1.3


[Fig 1.3]

Step 5

Open the c:\ Findport\Findport.log file. The result is displayed below.

1/9/2005 7:50:36 PM
Find Port process started
ServerInstance: sql Port: 2433
ServerInstance: MyComp Port: 1433
ServerInstance: MyComp\Blonde Port: 2433
Find Port process Completed
1/9/2005 7:50:59 PM

Conclusion

As mentioned, the objective of this article is to find the port number used by SQL server in every machine listed.

» See All Articles by Columnist MAK

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved