VBScript: Enumerate All SQL Servers on a Domain

So if you have SQL Server installed locally, you’re lucky enough to have access to SQLDeeMO. You can then easily enumerate SQL Servers with the following code. Note: If you do decide to use VBScript and SQLDeeMO, you will need to remove the “ee” in the script below. I didn’t want to put in the actual object name so that people searching Google for sites that do not contain the phrase SQLDeeMO will still find my site.

Set objSQLDMOApp = CreateObject(“SQLDeeMO.Application”)
Set objSQLList = objSQLDeeMOApp.ListAvailableSQLServers()
For i = 1 To objSQLList.Count
MsgBox objSQLList.Item(i)
Next

Set objSQLList = Nothing
Set objSQLDeeMOApp = Nothing

But if you don’t have SQL Server installed locally.. here’s a hack that grabs the name of all Windows Servers in AD and then checks their registry for instances of SQL Server.

‘****************************************************************************
‘ This script created by Chrissy LeMaire ([email protected])
‘ Modifications by Gregory Jones ([email protected]) and Radsky
‘ Website: http://netnerds.net/

‘ This script finds all SQL Servers and their instances that are members of an AD domain
‘ and running any Windows Server version

‘ Run this script with admin privs on any computer within a domain

‘ This script has been tested on Windows Server 2003 and Server 2008.
‘ The newest script REQUIRES SQL Native Client to get the Version.

‘ “What it does”
‘ 1. Gathers all machines in a domain that are running a Windows Server OS (NT, 2000, 2003, 2008, etc)
‘ 2. Pings them to see if they are available
‘ 3. If they do respond to pings, it checks their registry to see if they have the proper SQL keys
‘ 4. If the key does exist, it then enumerates the instances (including default)
‘ 5. And then it goes get the version and architecture

‘ NO WARRANTIES, USE THIS AT YOUR OWN RISK, etc.
‘*****************************************************************************
‘on error resume next

Set objAdRootDSE = GetObject(“LDAP://RootDSE”)
Set objRS = CreateObject(“adodb.recordset”)
set objFS = CreateObject(“Scripting.FileSystemObject”)
Set objOutputText = objFS.CreateTextFile(“sqlServers.txt”)

Set objWMIService = GetObject(“winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2”)
Const HKEY_LOCAL_MACHINE = &H80000002
varConfigNC = objAdRootDSE.Get(“defaultNamingContext”)
strConnstring = “Provider=ADsDSOObject”
strWQL = “SELECT * FROM ‘LDAP://” & varConfigNC & “‘ WHERE objectCategory= ‘Computer’ and OperatingSystem = ‘Windows*Server*’ ”

objRS.Open strWQL, strConnstring
Do until objRS.eof
Set objServer = GetObject(objRS.Fields.Item(0))
strServerName = objServer.CN
Set colItems = objWMIService.ExecQuery(“Select * from Win32_PingStatus Where Address = ‘” & objServer.DNSHostName & “‘”)
For Each objItem in colItems
If objItem.StatusCode = 0 Then ‘The Computer is Pingable
Set objRegistry = GetObject(“winmgmts:{impersonationLevel=impersonate}!\\” & strServerName & “\root\default:StdRegProv”)
strKeyPath = “SOFTWARE\Microsoft\Microsoft SQL Server”
strValueName = “InstalledInstances”
objRegistry.GetMultiStringValue HKEY_LOCAL_MACHINE,strKeyPath, strValueName,arrValues
If IsNull(arrValues) = 0 Then ‘It’s a SQL Server! Enumerate it’s instances
For Each strValue In arrValues
if lcase(strValue) “mssqlserver” then strServerName = strServerName & “\” & strValue
strMsg = strServerName & vbtab & GetSQLServerVersion(strServerName)
objOutputText.WriteLine strMsg
Next
End If
Set objRegistry = Nothing
End If
Set objServer = Nothing
Next
objRS.movenext
Loop
objRS.close
objOutputText.close

Set objOutputText = nothing
Set objWMIService = Nothing
Set objRS = Nothing
Set objAdRootDSE = Nothing

Msgbox “Done!”
‘####
Function GetSQLServerVersion(serverName)
on error resume next
strConn = “Driver={SQL Server};Server=” & serverName & “;Database=master”
strsql = “SELECT @@version”

set rs = createobject(“adodb.recordset”)
rs.Open strsql, strConn, 1, 1

if err.Number 0 then
GetSQLServerVersion = “Port blocked (Likely Desktop / Express)”
err.Clear
else
if not rs.eof and not rs.bof then
strVersion = rs(0).Value
if inStr(strVersion,vbLf) > 0 Then strVersion = left(strVersion,inStr(strVersion,vbLf))
GetSQLServerVersion = strVersion
else
GetSQLServerVersion = “Unknown Version”
end If
end if
rs.close
set rs = nothing
End Function ‘//Function GetSQLServerVersion(serverName)

If you are looking for the version of each SQL Server.. you can either query it with SELECT @@VERSION or it may be easily found in the registry. It’s 10:47pm and I’m still at work so I’m heading out ;)

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in Active Directory, VBScript