No Machines found - Instance scan complete (Success: 0; Failure: 0)

Mar 19, 2014 at 11:39 AM
Hi Kendal
I have 2 machines I am running an inventory on - S1 and S2

I can run the SQL inventory locally on each machine with no issues.
When I try to run an inventory for S2 from S1 and vice-versa, I get this:

2014-03-19 13:34:28.2585 ? Starting Script: C:\Documents\WindowsPowerShell\Get-SqlServerInventoryToClixml.ps1
2014-03-19 13:34:28.3155 ? Beginning SQL Server Inventory
2014-03-19 13:34:28.3205 ? -LoggingPreference: Verbose
2014-03-19 13:34:28.3245 ? -ComputerName: S1
2014-03-19 13:34:28.3275 ? -MaxConcurrencyThrottle: 2
2014-03-19 13:34:28.3315 ? -PrivateOnly: False
2014-03-19 13:34:28.3345 ? -ParentProgressId: -1
2014-03-19 13:34:28.3385 ? -IncludeDatabaseObjectPermissions: False
2014-03-19 13:34:28.3415 ? -IncludeDatabaseObjectInformation: False
2014-03-19 13:34:28.3445 ? -IncludeDatabaseSystemObjects: False
2014-03-19 13:34:28.3835 ? Beginning network scan
2014-03-19 13:34:28.3875 ? -ComputerName: S1
2014-03-19 13:34:28.3915 ? -PrivateOnly: False
2014-03-19 13:34:28.3945 ? -MaxConcurrencyThrottle: 2
2014-03-19 13:34:28.4005 ? -ResolveAliases: True
2014-03-19 13:34:28.4075 ? Resolving IP address for S1
2014-03-19 13:34:28.6535 ? Testing PING connectivity to 1 addresses
2014-03-19 13:34:28.7295 $ Testing PING connectivity to S1.DOMAIN.COM (111.111.111.111) [1 of 1]
2014-03-19 13:34:30.8508 $ PING response from S1.DOMAIN.COM (111.111.111.111): True
2014-03-19 13:34:31.1378 $ PING connectivity test complete
2014-03-19 13:34:31.1928 ? Testing WMI connectivity to 1 addresses
2014-03-19 13:34:31.2648 $ Testing WMI connectivity to S1.DOMAIN.COM (111.111.111.111) [1 of 1]
2014-03-19 13:34:31.5318 $ WMI response from S1.DOMAIN.COM (111.111.111.111): True
2014-03-19 13:34:31.8519 $ WMI connectivity test complete
2014-03-19 13:34:31.9159 ? Network scan complete
2014-03-19 13:34:31.9209 ? -IP Addresses Scanned: 1
2014-03-19 13:34:31.9249 ? -PING Replies: 1
2014-03-19 13:34:31.9329 ? -WMI Replies: 1
2014-03-19 13:34:31.9429 ? Beginning SQL Service discovery scan
2014-03-19 13:34:32.1799 ? Scanning S1.DOMAIN.COM at IP address 111.111.111.111 for SQL Services [Device 1 of 1]
2014-03-19 13:34:32.7080 ? Found SQL Server default instance S1.DOMAIN.COM at IP address 111.111.111.111
2014-03-19 13:34:32.7120 ? Found SQL Server Agent default instance S1.DOMAIN.COM at IP address 111.111.111.111
2014-03-19 13:34:33.0250 ? SQL Server service discovery complete
2014-03-19 13:34:33.0320 ? -SQL Server Instance Count: 1
2014-03-19 13:34:33.0370 ? -SQL Server Agent Instance Count: 1
2014-03-19 13:34:33.0550 ? Beginning scan of 0 instance(s)
2014-03-19 13:34:33.6621 ? Instance scan complete (Success: 0; Failure: 0)
2014-03-19 13:34:33.7111 ? Writing Inventory to disk
2014-03-19 13:34:33.7151 + No machines found!
2014-03-19 13:34:33.7281 ? End Script: C:\Documents\WindowsPowerShell\Get-SqlServerInventoryToClixml.ps1

I've removed server names and IP's

Any ideas?

Thanks
Coordinator
Apr 15, 2014 at 3:44 PM
I had someone else tell me about a similar problem. In their case the SMO ManagedComputer object was not returning the state of each service. Can you try running this (fill in the right server name, of course) snippet in a PowerShell ISE window and see if the service state is returned?
# Load SMO Assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | ForEach-Object {
 if ($_.GetName().Version.Major -ge 10) {
  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | Out-Null
   [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | Out-Null
 }
}

$error.Clear()
$ManagedComputer = New-Object -TypeName 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' -ArgumentList 'S1.DOMAIN.COM'
$ManagedComputer.Services | Format-Table -Property DisplayName, StartMode, ServiceState, ProcessId
Remove-Variable -Name ManagedComputer
$Error
Apr 22, 2014 at 1:42 PM
Thanks Kendal

I tested it and it returned the following:
The following exception was thrown when trying to enumerate the collection: "SQL Server WMI provider is not available on S1.DOMAIN.COM.".
At line:11 char:1
+  <<<< $ManagedComputer.Services | Format-Table -Property DisplayName, StartMode, ServiceState, ProcessId
    + CategoryInfo          : NotSpecified: (:) [], ExtendedTypeSystemException
    + FullyQualifiedErrorId : ExceptionInGetEnumerator
Apr 29, 2014 at 2:12 PM
Hi,

You need replace S1.Domain.COM with the name of the remote computer (ie Server1.YourDomain.com). If it still doesn't work, just use the server name and leave out the YourDomain.com part. You should get a list of the SQLServer Services and there states on the machine.
Aug 14, 2014 at 2:59 PM
Edited Aug 14, 2014 at 3:02 PM
I have this same issue, only on some of my machines. Running the above snippet locally returns status of all SQL Server services, but running it remotely gives the following:
The following exception occurred while trying to enumerate the collection: "SQL Server WMI provider is not available on xxxxxxxxxx.".
At line:11 char:1
+ $ManagedComputer.Services | Format-Table -Property DisplayName, StartMode, Servi ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], ExtendedTypeSystemException
    + FullyQualifiedErrorId : ExceptionInGetEnumerator
This was tested with firewall disabled. Basically occurs on ALL SQL Server 2012 instances in my network, but works normally (as expected) on 2008R2 and 2014.

Edit: I ran it successfully on a machine with (1) 2012 and (1) 2014 instance and it discovered both instances. But every machine in my network that ONLY has 2012 instances it fails to find the WMI provider.
Sep 15, 2014 at 1:25 PM
kendalvandyke wrote:
I had someone else tell me about a similar problem. In their case the SMO ManagedComputer object was not returning the state of each service. Can you try running this (fill in the right server name, of course) snippet in a PowerShell ISE window and see if the service state is returned?
# Load SMO Assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | ForEach-Object {
 if ($_.GetName().Version.Major -ge 10) {
  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | Out-Null
   [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | Out-Null
 }
}

$error.Clear()
$ManagedComputer = New-Object -TypeName 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' -ArgumentList 'S1.DOMAIN.COM'
$ManagedComputer.Services | Format-Table -Property DisplayName, StartMode, ServiceState, ProcessId
Remove-Variable -Name ManagedComputer
$Error
Hi Kendal,
I've just had a similar problem and it looks like in my case it was caused by the SQL 2014 SMO library being installed on the server even though there were no SQL 2014 instances.

It seems the Services property of the ManagedComputer object was not returning anything. When I forced the load of the SQL 2012 SMO library I was able to complete the scan.

Thanks
Paul.
Dec 30, 2014 at 9:02 AM
I've got the same problem - as soon as the SQL 2014 SMO is used I can't query SQL 2012 services ("Der SQL Server-WMI-Anbieter ist in 'hostname' nicht verfügbar." - "SQL Server WMI provider is not available on"). It seems to work as soon as I install the SQL Server 2014 components on a SQL 2012 maschine. Just the SMO from the feature pack is not enough :(

Installing the SQL 2012 SMO components on my querying maschine, it works:
Add-Type -Path "C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
Add-Type -Path "C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.SmoExtended.dll"
Add-Type -Path "C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.SqlWmiManagement.dll"
$ManagedComputer = New-Object -TypeName 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' -ArgumentList $hostname
$ManagedComputer.Services
But with the SQL 2012 SMO Types I can't query SQL 2014 instances...
Dec 30, 2014 at 9:39 AM
Dec 20, 2015 at 3:34 AM
Hi Guys,

I have the same problem. Have found any solution for this : "SQL Server WMI provider is not available on xxxxxx"?

Thanks.
Coordinator
Dec 22, 2015 at 9:09 PM
chirayu547 wrote:
Hi Guys,

I have the same problem. Have found any solution for this : "SQL Server WMI provider is not available on xxxxxx"?

Thanks.
Have you verified that the version of SMO on the machine where you're running the scripts matches the version of SQL Server that you're trying to gather information from?

--Kendal
Dec 23, 2015 at 6:40 AM
We have rewritten the whole logic and are using directly WMI - not SMO. This way the scan works with all versions.
Feb 15, 2016 at 3:36 PM
I am having this issue. I have a powershell script that runs from a central location. It connects to each of our SQL Server instances and collects information. It uses SMO and WMI the to get the port number of each SQL Server instance. If SQL 2014 client tools are installed on the central server, it fails to get the 2012 port numbers. I get the error "SQL Server WMI provider is not available".

Does anyone know when/if Microsoft plans to fix this?

Jensthebrain, can you explain how you rewrote the logic to use WMI directly instead of SMO? I'd like to know if I could change my powershell script to do that or if when running it from a central location to get info about each instance, I need to use SMO.

Thanks!
Mar 2, 2016 at 11:33 AM
This is an example of the alternative way:
$namespaces = Get-WMIObject -ComputerName $hostName -NameSpace root\Microsoft\SQLServer -Class "__NAMESPACE" |
    Where Name -Like "ComputerManagement*"
Foreach ($namespace IN $namespaces)
        {
        $services = Get-WmiObject -ComputerName $hostname -Namespace "$($namespace.__NAMESPACE)\$($namespace.Name)" -Class SqlService |
        Select __SERVER, ServiceName, DisplayName, StartName,
         @{l='TypeDescr';e={switch($_.SQLServiceType){1 {'Database Engine'} 2 {'SQL Agent'} 3 {'Full Text Search'} 4 {'SSIS'} 5 {'SSAS'} 6 {'SSRS'} 7 {'SQL Browser'} 8 {'Unknown'} 9 {'FullTextFilter Daemon Launcher'}}}},
          @{l='status';e={switch($_.state){ 1 {'Stopped'} 2 {'Start Pending'}  3 {'Stop Pending' } 4 {'Running'}}}},
          @{l='startmodus';e={switch($_.startmode){ 1 {'unknown'} 2 {'Automatic'}  3 {'Manual' } 4 {'Disabled'}}}}
        }
Jun 20, 2016 at 1:04 PM
Edited Jun 20, 2016 at 1:12 PM
Hi Kendal.

First of all I would like to thank you for sharing this great solution with us! It's realy usefull and contains toons of usefull information and great overview of environment.
Like others I'm having same issue too when trying to document SQL cluster - "No machines found!" while documenting standalone setups works great.

My environment;
  • Cluster with three nodes runing Windows Server 2012
  • Three SQL 2012 FCI instances (two database engines and one SSAS)
  • Windows 7 computer collecting data
  • SMO on computer collecting data
    Image
  • All servers and computer for collecting data are in same domain
When I run
.\Get-SqlServerInventoryToClixml.ps1 -ComputerName GENINGCSQL01,GENINGCSQL02,GENINGCSQL03 -MaxConcurrencyThrottle 8 -IncludeDatabaseObjectPermissions -IncludeDatabaseSystemObjects -LoggingPreference Verbose -DirectoryPath "C:\Users\dejand\Documents\SQL PowerDoc"
I get this result in log
2016-06-20 14:05:36.3290 ? Starting Script: C:\Users\foo\Documents\WindowsPowerShell\Get-SqlServerInventoryToClixml.ps1
2016-06-20 14:05:36.3446 ? Beginning SQL Server Inventory
2016-06-20 14:05:36.3446 ?  -LoggingPreference: Verbose
2016-06-20 14:05:36.3446 ?  -ComputerName: SERVERSQL01,SERVERSQL02,SERVERSQL03
2016-06-20 14:05:36.3602 ?  -MaxConcurrencyThrottle: 8
2016-06-20 14:05:36.3602 ?  -PrivateOnly: False
2016-06-20 14:05:36.3602 ?  -ParentProgressId: -1
2016-06-20 14:05:36.3602 ?  -IncludeDatabaseObjectPermissions: True
2016-06-20 14:05:36.3758 ?  -IncludeDatabaseObjectInformation: False
2016-06-20 14:05:36.3758 ?  -IncludeDatabaseSystemObjects: True
2016-06-20 14:05:36.3758 ? Beginning network scan
2016-06-20 14:05:36.3914 ?  -ComputerName: SERVERSQL01,SERVERSQL02,SERVERSQL03
2016-06-20 14:05:36.3914 ?  -PrivateOnly: False
2016-06-20 14:05:36.3914 ?  -MaxConcurrencyThrottle: 8
2016-06-20 14:05:36.4070 ?  -ResolveAliases: True
2016-06-20 14:05:36.4070 ? Resolving IP address for SERVERSQL01
2016-06-20 14:05:36.4070 ? Resolving IP address for SERVERSQL02
2016-06-20 14:05:36.4226 ? Resolving IP address for SERVERSQL03
2016-06-20 14:05:36.4538 ? Testing PING connectivity to 3 addresses
2016-06-20 14:05:36.4694 $ Testing PING connectivity to SERVERSQL03.MYDOMAIN.CORP (192.168.1.170) [1 of 3]
2016-06-20 14:05:36.4850 $ Testing PING connectivity to SERVERSQL02.MYDOMAIN.CORP (192.168.1.168) [2 of 3]
2016-06-20 14:05:36.5318 $ Testing PING connectivity to SERVERSQL01.MYDOMAIN.CORP (192.168.1.167) [3 of 3]
2016-06-20 14:05:38.7158 $ PING response from SERVERSQL03.MYDOMAIN.CORP (192.168.1.170): True
2016-06-20 14:05:38.7158 $ PING response from SERVERSQL02.MYDOMAIN.CORP (192.168.1.168): True
2016-06-20 14:05:38.7158 $ PING response from SERVERSQL01.MYDOMAIN.CORP (192.168.1.167): True
2016-06-20 14:05:38.9966 $ PING connectivity test complete
2016-06-20 14:05:38.9966 ? Testing WMI connectivity to 3 addresses
2016-06-20 14:05:39.0122 $ Testing WMI connectivity to SERVERSQL03.MYDOMAIN.CORP (192.168.1.170) [1 of 3]
2016-06-20 14:05:39.0278 $ Testing WMI connectivity to SERVERSQL02.MYDOMAIN.CORP (192.168.1.168) [2 of 3]
2016-06-20 14:05:39.0278 $ Testing WMI connectivity to SERVERSQL01.MYDOMAIN.CORP (192.168.1.167) [3 of 3]
2016-06-20 14:05:39.2930 $ WMI response from SERVERSQL03.MYDOMAIN.CORP (192.168.1.170): True
2016-06-20 14:05:39.2930 $ WMI response from SERVERSQL02.MYDOMAIN.CORP (192.168.1.168): True
2016-06-20 14:05:39.2930 $ WMI response from SERVERSQL01.MYDOMAIN.CORP (192.168.1.167): True
2016-06-20 14:05:39.5738 $ WMI connectivity test complete
2016-06-20 14:05:39.5894 ? Network scan complete
2016-06-20 14:05:39.5894 ?  -IP Addresses Scanned: 3
2016-06-20 14:05:39.5894 ?  -PING Replies: 3
2016-06-20 14:05:39.6050 ?  -WMI Replies: 3
2016-06-20 14:05:39.6206 ? Beginning SQL Service discovery scan
2016-06-20 14:05:39.6674 ? Scanning SERVERSQL03.MYDOMAIN.CORP at IP address 192.168.1.170 for SQL Services [Device 1 of 3]
2016-06-20 14:05:39.6674 ? Scanning SERVERSQL02.MYDOMAIN.CORP at IP address 192.168.1.168 for SQL Services [Device 2 of 3]
2016-06-20 14:05:39.7298 ? Scanning SERVERSQL01.MYDOMAIN.CORP at IP address 192.168.1.167 for SQL Services [Device 3 of 3]
2016-06-20 14:05:40.3070 ? Found SQL Server named instance SERVERSQL03.MYDOMAIN.CORP\INSTANCE at IP address 192.168.1.170
2016-06-20 14:05:40.3070 ? Found SQL Server Agent named instance SERVERSQL03.MYDOMAIN.CORP\INSTANCE at IP address 192.168.1.170
2016-06-20 14:05:40.5722 ? Found SQL Server named instance SERVERSQL01.MYDOMAIN.CORP\INSTANCE at IP address 192.168.1.167
2016-06-20 14:05:40.5722 ? Found SQL Server Agent named instance SERVERSQL01.MYDOMAIN.CORP\INSTANCE at IP address 192.168.1.167
2016-06-20 14:05:40.5878 ? Found SQL Server named instance SERVERSQL01.MYDOMAIN.CORP\INSTANCE2 at IP address 192.168.1.167
2016-06-20 14:05:40.5878 ? Found SQL Server Agent named instance SERVERSQL01.MYDOMAIN.CORP\INSTANCE2 at IP address 192.168.1.167
2016-06-20 14:05:40.8530 ? Found SQL Server named instance SERVERSQL02.MYDOMAIN.CORP\INSTANCE at IP address 192.168.1.168
2016-06-20 14:05:40.8530 ? Found SQL Server Agent named instance SERVERSQL02.MYDOMAIN.CORP\INSTANCE at IP address 192.168.1.168
2016-06-20 14:05:40.8686 ? Found SQL Server named instance SERVERSQL02.MYDOMAIN.CORP\INSTANCE2 at IP address 192.168.1.168
2016-06-20 14:05:40.8686 ? Found SQL Server Agent named instance SERVERSQL02.MYDOMAIN.CORP\INSTANCE2 at IP address 192.168.1.168
2016-06-20 14:05:41.1338 ? SQL Server service discovery complete
2016-06-20 14:05:41.1650 ?  -SQL Server Instance Count: 5
2016-06-20 14:05:41.1650 ?  -SQL Server Agent Instance Count: 5
2016-06-20 14:05:41.1650 ? Beginning scan of 0 instance(s)
2016-06-20 14:05:41.4771 ? Instance scan complete (Success: 0; Failure: 0)
2016-06-20 14:05:41.4927 ? Writing Inventory to disk
2016-06-20 14:05:41.4927 + No machines found!
2016-06-20 14:05:41.5083 ? End Script: C:\Users\foo\Documents\WindowsPowerShell\Get-SqlServerInventoryToClixml.ps1
I've run snippet in PS you have provided to DerekrhSQL and that's the result;
The following exception occurred while trying to enumerate the collection: "SQL Server WMI provider is not available on SERVERSQL01.MYDOMAIN.CORP.".
At line:11 char:1
+ $ManagedComputer.Services | Format-Table -Property DisplayName, StartMode, Servi ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], ExtendedTypeSystemException
    + FullyQualifiedErrorId : ExceptionInGetEnumerator
 
The following exception occurred while trying to enumerate the collection: "SQL Server WMI provider is not available on SERVERSQL01.MYDOMAIN.CORP.".
At line:11 char:1
+ $ManagedComputer.Services | Format-Table -Property DisplayName, StartMode, Servi ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], ExtendedTypeSystemException
    + FullyQualifiedErrorId : ExceptionInGetEnumerator
Thank you for your time and help. Looking forward to solve this issue.

PS: I've changed names of computers, domain and IP addresses.
Coordinator
Jun 22, 2016 at 3:57 PM
Hi ddragman,
I've seen this caused by two issues:
  1. The version of SMO that gets loaded by Power Doc is lower than the version of SQL Server that you're connecting to
  2. Something's wrong with the WMI provider on the machine running SQL Server
It looks like you might have #1 going on here...What's the output if you run the following in a PowerShell console?
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
You can tell if #2 is the issue because SQL Server Configuration Manager will give you a similar error message when you run it on your SQL Server and try to view services. There's an easy fix - re-register the WMI MOF files for SQL. See https://support.microsoft.com/en-us/kb/956013 for instructions on how to do this.
Jun 23, 2016 at 12:25 PM
Hi Kendal,

Thanks for your reply!

I've run the code in PowerShell console on computer from which I'm collecting data and that's the result.
PS C:\Windows\system32> [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')

GAC    Version        Location                                                                                                                                           
---    -------        --------                                                                                                                                           
True   v2.0.50727     C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SMO\13.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SMO.dll                                        
I've also run the same code in PowerShell on all three cluster node computers and that's the result. I believe that's OK, because SQL cluster is SQL 2012.
PS C:\Windows\system32> [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')

GAC    Version        Location                                                                                                                    
---    -------        --------                                                                                                                    
True   v2.0.50727     C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SMO\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SMO.dll
WMI provider looks fine on machines runing SQL Server. SQL Server Configuration Manager runs without errors and I can view services runing on the machine.