At times a Database Administrator, or even a developer is required to wear a spy’s hat. This necessity oftentimes is dictated by a need to take a glimpse into a black-box application for reasons varying from a performance issue to an unauthorized access to data or resources, or as
in my most recent case, a closed source custom application that was abandoned by a deserted contractor without source code. It may not be news or unknown to most IT people that SQL Server has always provided means of back-door access to everything connecting to its database. This indispensible tool is SQL Server Profiler. This “gem” is always quietly sitting
in the Start – Programs – SQL Server <product version> – Performance Tools folder (yes, it is for performance analysis mostly, but not limited to) ready to help you! So, to
the action, let’s start it up. Once ready click on
the File – New Trace button, or using Ctrl-N with your keyboard.
The standard connection dialog you have seen
in SSMS comes up where you connect
the standard way: One side note here, you will be able to connect only if your account belongs to
the sysadmin or alter trace fixed server role. Upon a successful connection you must be able to see this initial dialog: At this stage I will give a hint: you will have a wide variety of predefined templates: But to shorten your time to results you would need to opt for using
the TSQL_Grouped template. Now you need to set it up.
In some cases, you will know
the principal’s login name (account) that needs to be monitored
in advance, and
in some (like
in mine), you will not. But it is VERY helpful to monitor just a particular account to minimize
the amount of results returned. So if you know it you can already go to
the Event Section tab, then click
the Column Filters button which would bring a dialog below where you key
in the account being monitored without any mask (or whildcard): If you do not know
the principal name then you will need to poke around and look around for things like a config file where (typically!)
the connection string is fully exposed. That was
the case
in my situation, an application had an app.config (XML) file with
the connection string
in it not encrypted: This made my endeavor very easy. So after I entered
the account to monitor I clicked on Run button and also started my black-box application. Voilà,
in a under a minute of time I had
the SQL statement captured:
