Powershell services not finding SQL services in loop, but service is there when run under get-service

We were tracking SQL services in vendor tools, that we no longer have access to. So I'm trying Powershell, which I'm new to. When I run this, the SQL services error out saying Get-Service: Cannot find any service with service name 'MSSQLSERVER' and the same for 'SQLSERVERAGENT'. I don't really need VSS and Spooler, but added them to make sure it is working properly. It seems that other services work fine. When I run Get-Service -computername 'server1', the SQL services show their status in the list of services. Same powershell window/user/permissions. I've tried run as admin as well with no luck. Are the SQL services run differently? The loop seems to work with other services, just not SQL.

$servers = 'server1','server2','server3','server4', 'server5', 'server6'
$path = 'c:\temp\DatabaseServicesStatusReport.html' 

$header = @"
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
<title>System Status Report</title>
<style type="text/css">
<!--
body {
background-color: #E0E0E0;
font-family: sans-serif
}
table, th, td {
background-color: white;
border-collapse:collapse;
border: 1px solid black;
padding: 5px
}
-->
</style>
"@

$body = @"
<h1>SQL Services Status</h1>
<p>As of: $(get-date).</p>
"@

$results = foreach ($server in $servers) 
{ 
    [PSCustomObject]@{
        ServerName = $server
        VSS = (Get-Service -name vss).Status
        Spooler = (Get-Service -name spooler).Status
        MSSQLSERVER = (Get-Service -name MSSQLSERVER).Status
        SQLSERVERAGENT = (Get-Service -name SQLSERVERAGENT).Status
    }
} 

$results | ConvertTo-Html -head $header -body $body | foreach {
    $PSItem -replace "<td>Stopped</td>", "<td style='background-color:#FF8080'>Stopped</td>"
} | Out-File C:\temp\DatabaseServicesStatusReport.html

Invoke-Expression c:\temp\DatabaseServicesStatusReport.html

Solution

You need to run the same script, but add the -ComputerName parameter to check in the specified server:

$results = foreach ($server in $servers) 
{ 
    

    [PSCustomObject]@{
        ServerName = $server
        VSS = (Get-Service -ComputerName $server -name vss).Status
        Spooler = (Get-Service -ComputerName $server -name spooler).Status
        MSSQLSERVER = (Get-Service -ComputerName $server -name MSSQLSERVER).Status
        SQLSERVERAGENT = (Get-Service -ComputerName $server -name SQLSERVERAGENT).Status
    }
}

Check another similar question: How to check if a particular service is running in a remote computer using PowerShell