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 ([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 PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in Active Directory, VBScript
17 comments on “VBScript: Enumerate All SQL Servers on a Domain
  1. JM says:

    Very kewl!

    Cheers for posting it up!
    JM

  2. Jerry says:

    Looks great. Where do I put the domain name?

  3. Greg says:

    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 ([email protected])
    ‘ Website: http://netnerds.net/

    ‘ Modified by Gregory Jones ([email protected])

    ‘ 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)

  4. Radski says:

    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)

  5. Dale says:

    Thank you very much for this post, it was exactly what I needed!

    Dale

  6. Andrew says:

    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)

    • Sarah says:

      Hi

      I am getting this exact same error when I try this script against my domain. Any suggestions would be most welcome.

      BR
      Sarah

      • Chrissy says:

        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.

  7. d-doug says:

    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)

  8. Mark says:

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

  9. Manjot says:

    Hi,
    We have been using your script to discover all SQL servers in our domain. Ours is a big organization now and has multiple domains. When a SQL server is moved from its original domain say ABC to DEF, then the script stops discovering it. That is, the server is logically still in ABC domain but physically in DEF's data centre.
    How can we discover in those kind of situations?
    Thanks in advance

    • Eva says:

      Any luck finding out how to do that? I have multiple domains and standalone servers, I want to find sql on all of the servers?

  10. aaron says:

    Hello,
    I know many people have a new way to do this, but I have the same issue as Eva, and we don't have powershell on all servers.

    What are the options and why does the version come back as N/A on some systems that it does find SQL installed on?

Leave a Reply

Your email address will not be published. Required fields are marked *

*