» VBScript: Enumerate All SQL Servers on a Domain

9Jan/0714

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.

SQLDeeMO VBScript Action

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.

Enumerate All SQL Servers and Instance Names on a Domain

'****************************************************************************

' This script created by Chrissy LeMaire (clemaire@gmail.com)

' Modifications by Gregory Jones (greg@fuzsh.com) 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

Posted by: Chrissy Filed under: Active Directory, VBScript Leave a comment

Comments (14) Trackbacks (0) ( subscribe to comments on this post )

JM
February 20th, 2007 - 13:44

Very kewl!

Cheers for posting it up!
JM

( REPLY )
Jerry
October 2nd, 2007 - 19:34

Looks great. Where do I put the domain name?

( REPLY )
- Chrissy
  June 29th, 2010 - 03:56
  
  Jerry, it automatically determins that using
  Set objAdRootDSE = GetObject(“LDAP://RootDSE”)
  
  ( REPLY )
Greg
May 15th, 2008 - 10:19

Here’s a slight modification – This script will query the SQL server for it’s version. Also, creates an error file and an output file (this is all hack, nuthin’ fancy
‘****************************************************************************
‘ This script created by Chrissy LeMaire (clemaire@gmail.com)
‘ Website: http://netnerds.net/
‘
‘ Modified by Gregory Jones (greg@fuzsh.com)
‘
‘ This script finds all SQL Servers and their instances and their version 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 only been tested on Windows Server 2003
‘
‘ “What it does”
‘ 1. Gathers all machines in a domain that are running a Windows Server OS (NT, 2000, 2003)
‘ 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)
‘
‘ 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 objLogFile = objFS.CreateTextFile(“sql_servers.txt”)
Set objERRORLog = objFS.CreateTextFile(“sql_servers_errors.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

strMsg = strServerName & ” is running the following SQL Instance(s): ”
For Each strValue In arrValues
If strValue = “MSSQLSERVER” Then strValue = “Default Instance”
strMsg = strServerName & vbtab & strValue & vbtab & GetSQLServerVersion(strServerName)
Next

objLogFile.WriteLine strMsg
wscript.echo strMsg
End If
Set objRegistry = Nothing
End If
Set objServer = Nothing
Next
objRS.movenext
Loop
objRS.close

Set objWMIService = Nothing
Set objRS = Nothing
Set objAdRootDSE = Nothing

Function GetSQLServerVersion(serverName)

Set cxn = Wscript.CreateObject(“ADODB.Connection”)
strConn = “Driver={SQL Server};Server=” & strServerName & “;Database=master”
sqlstr = “SELECT @@version”
cxn.ConnectionString = strConn
cxn.Open
Set rs=cxn.Execute(sqlstr)

rs.MoveFirst

If ERR 0 Then

objERRORLog.WriteLine Err.Description
GetSQLServerVersion = Err.Description
ERR.Clear
Else
GetSQLServerVersion = rs(0).Value

End If

cxn.Close

End Function ‘//Function GetSQLServerVersion(serverName)

( REPLY )
- Chrissy
  June 29th, 2010 - 04:03
  
  Thanks, Greg! I’ve updated my script and gave you cred.
  
  ( REPLY )
Radski
August 7th, 2008 - 03:03

Another ugly hack to make this work with 2005 if you have the native client installed and to ditch the dodgy logic for default instance from the last post

‘========================================================================
ON ERROR RESUME NEXT

Set objAdRootDSE = GetObject(“LDAP://RootDSE”)
Set objRS = CreateObject(“adodb.recordset”)
set objFS = CreateObject(“Scripting.FileSystemObject”)
Set objLogFile = objFS.CreateTextFile(“sql_servers.txt”)
Set objERRORLog = objFS.CreateTextFile(“sql_servers_errors.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
strMsg = strServerName & vbtab & strValue & vbtab & GetSQLServerVersion(strServerName)
objLogFile.WriteLine strMsg
wscript.echo strMsg
Next

End If
Set objRegistry = Nothing
End If
Set objServer = Nothing
Next
objRS.movenext
Loop

objRS.close

Set objWMIService = Nothing
Set objRS = Nothing
Set objAdRootDSE = Nothing

‘####
Function GetSQLServerVersion(serverName)

Set cxn = Wscript.CreateObject(“ADODB.Connection”)
strConn = “Driver={SQL Server};Server=” & strServerName & “;Database=master”
sqlstr = “SELECT @@version”
cxn.ConnectionString = strConn
Err.Clear
cxn.Open

If Err.number 0 Then
Set cxn = Wscript.CreateObject(“ADODB.Connection”)
strConn = “Driver={SQL Native Client};Server=” & strServerName & “;Database=master”
sqlstr = “SELECT @@version”
cxn.ConnectionString = strConn
cxn.Open
End If

Set rs=cxn.Execute(sqlstr)
Err.Clear
rs.MoveFirst

If Err.number 0 Then
objERRORLog.WriteLine Err.Description
GetSQLServerVersion = Err.Description
ERR.Clear
Else
GetSQLServerVersion = rs(0).Value
End If

cxn.Close

End Function ‘//Function GetSQLServerVersion(serverName)

( REPLY )
- Chrissy
  June 29th, 2010 - 04:02
  
  Thanks, yo! I’ve updated my script to use some of yours.
  
  ( REPLY )
Andrew
January 20th, 2009 - 22:08

Radski – thanks for your effort, however I can’t get the error handling going??

If Err.number 0 Then
Set cxn = Wscript.CreateObject(“ADODB.Connection”)
strConn = “Driver={SQL Native Client};Server=” & strServerName & “;Database=master”
sqlstr = “SELECT @@version”
cxn.ConnectionString = strConn
cxn.Open
End If

Set rs=cxn.Execute(sqlstr)
Err.Clear
rs.MoveFirst

If Err.number 0 Then
objERRORLog.WriteLine Err.Description
GetSQLServerVersion = Err.Description
ERR.Clear
Else
GetSQLServerVersion = rs(0).Value
End If

cxn.Close

End Function ‘//Function GetSQLServerVersion(serverName)

( REPLY )
- Sarah
  January 25th, 2010 - 06:18
  
  Hi
  
  I am getting this exact same error when I try this script against my domain. Any suggestions would be most welcome.
  
  BR
  Sarah
  
  ( REPLY )
  - Chrissy
    June 29th, 2010 - 04:01
    
    There are two immediate problems that I see.
    
    It should be Err.Number <> 0 and strServerName should be serverName. I’ve updated the script in this post, though, and so you can just use that.
    
    ( REPLY )
d-doug
March 31st, 2009 - 15:05

Keep in mind that the WMI StdReg provider will create a profile for whatever user ID you’re using on the TARGET machines. ordinarily not a big deal but something to know if you have overly paranoid coworkers (lololol)

( REPLY )
Mark
June 7th, 2009 - 22:38

Great post Helped me a lot, as i had to do some DB validations from Wix installer. Great work

( REPLY )
Magnus
July 2nd, 2009 - 05:17

Charm. You has that too.

( REPLY )
- Chrissy
  June 29th, 2010 - 04:01
  
  Thanks, Magnus!
  
  ( REPLY )