The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,880 other followers

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:

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

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: