One of my client has the requirement to have SQL Service account running with domain account and should not be with local account.
You may receive SSPI error after changing it to domain account. In order to fix that issue check my previous blog “How to register SPN for SQL service account“
$OutputFile = "D:\SC\MSSQL\MSSQL_Service_$((Get-Date).ToString('ddMMyyyy_hhmm')).htm"
$HTML = '<style type="text/css">
table{font-family: Calibri,Candara,Segoe,Segoe UI,Optima,Arial,sans-serif;width:100%; border: black; border-style: double; border-width: 2px;}
table td,table th{font-size:1em;border:1px solid #98bf21;padding:3px 7px 2px;}
table th{font-size:1.1em;padding-top:5px;padding-bottom:4px;background-color:#81BEF7;color:#fff}
</Style>'
$HTML += "<HTML><BODY><Table border=1 cellpadding=0 cellspacing=0 width=100% id=Header>
<TR>
<TH align=center><B>Service Display Name</B></TH>
<TH align=center><B>Service Name</B></TH>
<TH align=center><B>Start Mode</B></TH>
<TH align=center><B>Service Account Name</B></TH>
<TH align=center><B>State</B></TH>
<TH align=center><B>System Name</B></TH>
</TR>"
Foreach($server in Get-Content D:\SC\MSSQL\Server.txt)
{
$Services=gwmi win32_service -computername $server | where {$_.Name -like '*MSSQL$*' -Or $_.Name -like '*SQLAgent$*' -Or $_.Name -eq 'MSSQLSERVER' -Or $_.Name -eq 'SQLSERVERAGENT' -Or $_.Name -like 'ReportServer$*' -Or $_.Name -like 'MsDtsServer*'-Or $_.Name -eq 'SQLWriter'}
$HTML += "<TR>
<TD colspan='7' align=center style='background-color:cyan'>$server</TD>
</TR>"
#Test for unsuccesful connection
if(!(Test-Connection -Cn $server -BufferSize 16 -Count 1 -ea 0 -quiet))
{“Problem still exists in connecting to $server”}
ELSE {
$services | ForEach {
If ($_)
{
$Var0= @{$true="style='background-color:green;'";$false="style='background-color:red;'"}[$($_.State) -eq 'Running']
$var1= @{$false="style='background-color:red;'"}[$($_.Startmode) -eq 'Auto']
$var2= @{$false="style='background-color:red;'"}[$($_.Startname) -like '*S10*' -Or $_.Startname -like '*ssvc*']
$HTML += "<TR>
<TD>$($_.Displayname)</TD>
<TD>$($_.Name)</TD>
<TD $Var1 ALIGN=CENTER>$($_.Startmode)</TD>
<TD $Var2 ALIGN=CENTER>$($_.Startname)</TD>
<TD $Var0 ALIGN=CENTER >$($_.State)</TD>
<TD>$($_.Systemname)</TD>
</TR>"
}
}
}
}
$HTML += "</Table></BODY></HTML>"
$HTML | Out-File $OutputFile
Output:
iLearnSQL 