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: https://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 ;)