Alan's Blog

"Yeah. I wrote a script that will do that."

Enumerate SQL Servers for a Windows Domain

Posted on March 30th, 2012

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.

Filed under Alan's Favorites, Security, VbScript, Windows Administration |

Leave a Reply

You must be logged in to post a comment.

Please Note:

All the scripts are saved as .txt files. Newer files have a "View Script" button which will let you save or open a script in notepad. For earlier posts, the easiest way to download with IE is to right click on the link and use "Save Target As". Rename file from Name_ext.txt to Name.ext.

To see a full post after searching, please click on the title.

PowerShell Scripts were written with Version 3 or 4.

https connections are supported.

Categories

Bad Behavior has blocked 167 access attempts in the last 7 days.