Shonky way to enumerate SQL Instances and TCP Ports on a Server

A lot of places I work either turn off SQL Browser and/or block UDP Port 1434 (Google Slammer virus !) and so some times you are face with situation that you can’t connect to a named instance as you don’t have the port.  As this is stored in the registry, I knocked up quick batch file below to query a given server . Tested against SQL 2000/5/8 from an XP client.

Save as (say) GetTCPPort.cmd and then run by

GETTCPPort.cmd Computername

where Servername is the ComputerName of the given server (ie without instnace). Once retrieved you can connect using below

tcp:computername,portnumber

ie.

tcp:lops2001,1545

Obviously can also try focing named pipes connection by doing npConfused smileervername\instance

There is a cleaner SMO / Powershell option to do this that I will write / post when the sun is not shining

@echo off

SET SQLSERVER=%COMPUTERNAME%
IF NOT !%1==! SET SQLSERVER=%1

@echo *** Getting Ports for %SQLSERVER% ***

reg query “\\%SQLSERVER%\HKLM\SOFTWARE\Microsoft\Microsoft SQL Server”  /s /v TcpPort
IF %ERRORLEVEL%==1 goto notfound

@echo.
@echo *** Getting Listen On for %SQLSERVER% ***
reg query “\\%SQLSERVER%\HKLM\SOFTWARE\Microsoft\Microsoft SQL Server” /s /v ProtocolList
goto end

:: ie not found , try other key
:notfound
@echo Not found in Microsoft SQL Server, checking MSSQLServer
reg query “\\%SQLSERVER%\HKLM\SOFTWARE\Microsoft\MSSQLServer” /s /v TcpPort

@echo.
@echo *** Getting Listen On for %SQLSERVER% ***
reg query “\\%SQLSERVER%\HKLM\SOFTWARE\Microsoft\MSSQLServer” /s /v ProtocolList

:end

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s