How to check the SQL WMI namespace and remove a corrupted instance using a Powershell script (4036463)

You are unable to set the SQL credentials for one or more SQL instances hosted on a protected server. You suspect that it is a WMI issue and you want an easy quick way to determine if that is the case and to remove the corrupted WMI instance.

 An interactive Powershell script has been prepared compatible with Powershell 3.0 or later has been prepared. The script investigates the WMI namespace for the Microsoft SQL server, detects the available instances and allows the user to select the instances to check. Please note that normally only the instances named "ComputerManagement<nnn>" where <nnn> may be either a number or null, are of interest. The other instances are still shown but should not be removed unless you a support engineer directs you so. Once the checks are done, the results are displayed in another gridview that allows checking the instance to remove. After a warning dialogue, the chosen instance is removed.

Please note that before removing a WMI instance, it is recommended to make a beckup of the WMI repository.

The script code is shown below between horizontal lines:

________________________________________________________

cls

Write-Host "SQL Server WMI Corrupted Instance Removal Tool`r`n----------------------------------------------"

[array]$x = get-wmiobject -query “SELECT * FROM __Namespace" -Namespace “root\Microsoft\sqlserver”

$table=@()

$i=0

[array]$sqlnamespace = $x.name | Out-GridView -Title "Select the Namespace to check" -PassThru 

if ($sqlnamespace.count -eq 0){Write-Host "No namespace to check was selected. Operation cancelled.`r`nExiting..."; exit}

foreach ($sqlns in $sqlnamespace){

$fullns= "root\microsoft\sqlserver\$sqlns"

$q = "SELECT * FROM ServerNetworkProtocol WHERE InstanceName = 'MSSQLSERVER' AND Enabled = 'True'"

try{

$queryresult = get-wmiobject -query "SELECT * FROM ServerNetworkProtocol WHERE InstanceName = 'MSSQLSERVER' AND Enabled = 'True'" -namespace "root\microsoft\sqlserver\$sqlns" -ErrorAction Stop

$result = $queryresult.protocoldisplayname

}

catch{

$ErrorMessage = $_.Exception.Message

$FailedItem = $_.Exception.ItemName

$FullException = $_.Exception

$result = "Error: $ErrorMessage"

}

$i++

$table += [pscustomobject]@{Number=$i;Instance=$sqlns;Value=$result}

}

for(;;){

[array]$remove = $table | out-gridview -Title "Please select the instance to be removed" -PassThru -ErrorAction Stop

if ($remove.count -eq 0){Write-Host "No Namespace to remove was selected. Operation cancelled.`r`nExiting..."; exit}

if ($remove.count -gt 1){Write-Host "Select only one item, please"; start-sleep 2}

else { break}

}

Write-host "`r`nRemoving:"

$remove | ft -AutoSize -Wrap

for($j=0; $j -lt $x.Count; $j++){

if($x[$j].name -eq $remove[0].instance){$index=$j; break }

}

$OK = Read-Host "`r`nHit Y remove from WMI the instance $($remove[0].instance)"

if ($OK -eq "Y"){

try{

$x[$index] | Remove-WmiObject -ErrorAction stop

Write-Host "Done!"

}

catch {

$ErrorMessage = $_.Exception.Message

Write-Host "Error: $ErrorMessage`r`nExiting..."

}

}else {Write-Host "`r`nNo Action Taken..."}

________________________________________________________