Find those rogue SQL Servers in your Enterprise with PowerShell

Find those rogue SQL Servers in your Enterprise with PowerShell

Rate This
  • Comments 3

I found a great way to check and see if a server has SQL Server installed on it. It uses “WMI”, or Windows Management Instrumentation and PowerShell. It simply interrogates the Services on a system to see if SQL Server is there, whether it is running or not.

All it is doing is using the gwmi (get-wmi) command-let and asks for all services that have MSSQL-something in the name, and then filters further out to only include the services that have the word “transaction” in the description field of the service. That shows the database engine only, which is usually what you care about. It stores all that information in a variable, which is actually a collection of items. From that collection, I simply loop through them, showing only the name and whether the service is running or not. You could save out this information to Microsoft Excel, a text file, an e-mail or whatever. I don’t show that here, but it wouldn’t be challenging to do that.

Here’s the complete script:

 

   1: # Find SQL Server Instances 
   2: $SQLServices = gwmi -query "select * from win32_service where Name LIKE 'MSSQL%' and Description LIKE '%transaction%'"
   3:  forEach ($SQLService in $SQLServices) {write-host  $SQLService.Name $SQLService.State} 

 

You can connect to another server and check using a “driver file” or list if you like, or in the new version of PowerShell (not yet out) you can use remoting. I just run this on each system, after I make sure that PowerShell is installed there.

 

As always, this warning applies to any script you find anywhere, including here.

Leave a Comment
  • Please add 3 and 8 and type the answer here:
  • Post
  • A reader named "Ben" wrote me and said that he looked at my script in PowerShell that finds

  • Thanks for posting this.  I do have a question, though... (and please correct me if I'm wrong). The script doesn't appear to "find" (i.e. automatically discover) rogue servers.  It would appear that you have to know the name of each server to run the script on.

    Do you happen to know of a script that will do the discovery process automatically?

  • Well, this finds the SQL Server Instances - not Windows Servers. To find the Windows Servers and make a list of those first, you could use Active Directory and PowerShell to locate those systems on your domain: technet.microsoft.com/.../ee617195.aspx

Page 1 of 1 (3 items)