Recently, we got into a debate on how could we get all possible connections from various (unidentified) standalone applications, to several SQL Servers.
Although a solution would be to constantly look the SSMS Activity monitor and collect all relevant information, the time a connection was occurring, it appeared less appealing, time passing, as there would possibly be connections during off-hours, that we could not audit.
Drilling down the case, we noticed that applications were served via Microsoft’s App-V, an application virtualization and application streaming solution. App-V allows applications to be deployed (“streamed”) in real-time to any client from a virtual application server. It removes the need for traditional local installation of the applications, although a standalone deployment method is also supported. With a streaming-based implementation, the App-V client needs to be installed on the client machines and application data that is stored on the virtual application server is installed (streamed) to the client cache on demand when it is first used, or pre-installed in a local cache.
The solution we went with, was to use netstat as a core connection-capture module. As it also can export an output file, we decided to put a customised netstat command into Windows Scheduler to run every custom time, and append its collection to the output file. So, what we wanted to do, is to execute it over a client with all the applications served from the App-V, in order to test them all, across the entire SQL farm.
We wanted to capture all IPs that could connect to a specific number of ports, and filter by the ESTABLISHED connections only.
The command we created is the below:
netstat -an | findstr ":52222 :59663 :51288 :61350 :49315 :1433" | findstr "ESTABLISHED" >> c:\output.txt
By scheduling it to run in an every-2-minute schedule, we managed to collect a huge list of connections, where we could simply filter out the duplicates, and end up with the list we wanted to get.