Checking which applications have a TCP connection to SQL Server (DTAP)
Posted by jpluimers on 2012/05/15
When in a DTAP environment, you cannot always have complete clean boundaries. Issues in production don’t reproduce in acceptance, you cannot develop in production, etc.
So sometimes you have to simulate or connect to Test or Acceptance Database Servers from a Develop workstation.
There it can get hairy to keep track of which applications connect to which database server.
That’s where the below batch file comes in handy: it scans your systems on connections to common TCP ports used by SQL server, then for each connection give you some process details (or – if you add a commandline parameter – all details that TLINK can get).
The batch file uses the built in tools tasklist, netstat, find and sc (the latter to show information on the local running SQL Services).
It also uses TLIST, which can be a bit awkward to get.
A few tricks used:
- setlocal/endlocal
- removing double quotes from a command line argument – careful with delayed expansion here!
(tasklist with /FV CSV can emit CSV output that has quotes) - netstat outputs zero (0) as local port when the connection is not ESTABLISHED any more
- using the caret (^) to escape the pipe (|) and double quotes (“) in for loops
- splitting the output of commands in for loops using usebackq, delims, tokens (both numbered and *) and back-quotes (`)
- tlist without comments shows one line of information per process;
tlist with a PID on the command line shows detailed process information (filtered with find) - various versions of .NET use various versions of the CLR (clr.dll, mscor*.dll)
This is the batch file:
@echo off
:main
setlocal
set details=%*
set foundsql=false
for %%p in (ms-sql-s 1433 ms-sql-m 1434) do call :netstat %%p
endlocal
goto :exit
:netstat
if %foundsql%==true goto :exit
echo trying port %1
netstat -o | find "%1"
for /f "usebackq tokens=2,3,4,5,7 delims=: " %%a in (`netstat -o ^| find ^"%1^"`) do (
call :parse_token %%a %%b %%c %%d %%e
)
goto :exit
:parse_token
set pid=%5
if !!==!%pid%! goto :exit
set foundsql=true
echo.
echo PID %pid%
echo Local port=%2, machine=%1
echo Remote port=%4, machine=%3
:: PID 0 is special: TCP connection that is not ESTABLISHED any more
if %pid%==0 goto :exit
set foundpid=false
for %%p in (%pids%) do (
if %%p==%pid% set foundpid=true
)
if %foundpid%==true goto :exit
set pids=%pid% %pids%
tasklist /V /FI "PID eq %pid%"
:: get the "non-verbose" process info from tlist, and decide in :show_process if we want detailed information
for /f "usebackq tokens=1,* delims=, " %%a in (`tlist`) do (
if %pid%==%%a call :show_process %%a %%b
)
:check_service
set showedsvc=false
rem NH=no header
for /f "usebackq tokens=* delims=, " %%a in (`tasklist /NH /FO CSV /SVC /FI ^"PID eq %pid%^"`) do (
call :show_service %%a
)
goto :exit
:show_process
echo %*
if !%details%!==!! goto :noDetails
tlist %pid%
goto :exit
:noDetails
tlist %pid% | find "CWD"
tlist %pid% | find "CmdLine"
:: .net 4.x and up
tlist %pid% | find "mscoree.dll"
:: .net 1.x till 3.x
tlist %pid% | find "clr.dll"
goto :exit
:show_service
if !%3!==!"N/A"! goto :exit
if %showedsvc%==true goto :details
set showedsvc=true
tasklist /SVC /FI "PID eq %~2"
:details
sc queryex %~3
goto :exit
:exit
–jeroen
The Wiert Corner - irregular stream of stuff 
Leave a comment