Enumerate SQL Servers for a Windows Domain

In a very large environment, getting a list and keeping track of SQL installs may be come problematic.  I wanted a way to track down all SQL installs, for licensing, management and security.  When you install SQL, it creates a Service Principal Name (SPN) in Active Directory.

I took a script which created a list of SQL installations by looking at SPNs, and then added the information I wanted to collect.  DomainSQLServerReport.vbs does the following: 1) Gets list of SQL SPN servers from AD.  2) Ping the list.  3) If there is a reply then try to connect to the SQL server using integrated authentication to query version and SQL instances.

If you are a Domain Admin, and if integrated authentication is enabled, you will get a lot of data this way.  You will also find your AD to be littered by unused SPNs, which you can delete (see, for example instructions here).

If you run this from a desktop with Excel installed, you will have a nicely formatted log file.  If no Excel is installed, you will have a tab delimited log.

Leave a Reply