SQL Server Security: Ensure that SQL Server service accounts are not a member of the Windows local Administrator group

These last few weeks, I work on a project to check the compliance of an instance with the Center for Internet Security (CIS) document.

The Benchmark documentation provides prescriptive guidance to secure SQL Server and I develop a tool to do a security audit automatically following these recommendations.

On the Authentication and Authorization Chapter, you have three sub-chapters:

• Ensure the SQL Server’s MSSQL Service Account is not an Administrator
• Ensure the SQL Server’s SQLAgent Service Account is not an Administrator
• Ensure the SQL Server’s Full-Text Service Account is not an Administrator

These three services should not be a member of the Windows Administrator Group.

It is also, why the “NT Authority\System” should not be used as service account for SQL Server…

I search how to do it automatically and find a way with PowerShell. It was not easy and I believe that it will be a good sharing opportunity.

The first step is to find these services for an instance.

I use the Get-WmiObject and the class Win32_Service in a simple request like this:

$MSSQL_Services = Get-WmiObject -Class Win32_Service | where {$_.Name.ToUpper() -like '*MSSQL*'}
$Agent_Services = Get-WmiObject -Class Win32_Service | where {$_.Name.ToUpper() -like '*SQLAgent*'}
$FullText_Services = Get-WmiObject -Class Win32_Service | where {$_.Name.ToUpper() -like '*MSSQLLFDLauncher*'}

If you have multiple instances on the same server, you can add a filter on the instance name as follow:

$Instance = '<instance_name>'
$Inst = '*'+$Instance+'*'
$MSSQL_Services = Get-WmiObject -Class Win32_Service | where {$_.Name.ToUpper() -like '*MSSQL*'} | where {$_.DisplayName.ToUpper()-like $Inst }
$Agent_Services = Get-WmiObject -Class Win32_Service | where {$_.Name.ToUpper() -like '*SQLAgent*'} | where {$_.DisplayName.ToUpper()-like $Inst }
$FullText_Services = Get-WmiObject -Class Win32_Service | where {$_.Name.ToUpper() -like '*MSSQLLFDLauncher*'} | where {$_.DisplayName.ToUpper()-like $Inst }

After this part, the second step is to find if these services are in the local administrator group.

To find the members of a local group, use the function Get-LocalGroupMember

Then for each service, add a clause where and the result will give us if thee service is in the local Administrator group or not:

Get-LocalGroupMember -Name 'Administrators' | where{$_.Name -like $MSSQL_Services}
Get-LocalGroupMember -Name 'Administrators' | where{$_.Name -like $Agent_Services}
Get-LocalGroupMember -Name 'Administrators' | where{$_.Name -like $FullText_Services} 

If I let the script like this, it will not working. The three variables will have only the name of the service without the domain or “NT SERVICE”. To find the service in the local administrator group, we need to adapt the script with wildcard to find the service:

$MSSQL_Services_local = $MSSQL_Services.name
$MSSQL_Services_local = "*$MSSQL_Services_local*"

Get-LocalGroupMember -Name 'Administrators' | where{$_.Name -like $MSSQL_Services_local}

$Agent_Services_local = $Agent_Services.name
$Agent_Services_local = "*$Agent_Services_local*"
Get-LocalGroupMember -Name 'Administrators' | where{$_.Name -like $Agent_Services_local}

$FullText_Services_local = $FullText_Services.name
$FullText_Services_local = "*$FullText_Services_local*"
Get-LocalGroupMember -Name 'Administrators' | where{$_.Name -like $FullText_Services_local}

To finish and be sure to not do the search for nothing, a little if not null or empty for each services can be add:

if (-not ([string]::IsNullOrEmpty($MSSQL_Services)))
{
$MSSQL_Services_local = $MSSQL_Services.name
$MSSQL_Services_local = "*$MSSQL_Services_local*"
Get-LocalGroupMember -Name 'Administrators' | where{$_.Name -like $MSSQL_Services_local}
}
if (-not ([string]::IsNullOrEmpty($Agent_Services)))
{
$Agent_Services_local = $Agent_Services.name
$Agent_Services_local = "*$Agent_Services_local*"
Get-LocalGroupMember -Name 'Administrators' | where{$_.Name -like $Agent_Services_local}
}
if (-not ([string]::IsNullOrEmpty($FullText_Services)))
{
$FullText_Services_local = $FullText_Services.name
$FullText_Services_local = "*$FullText_Services_local*"
Get-LocalGroupMember -Name 'Administrators' | where{$_.Name -like $FullText_Services_local} 
}

In my example below to illustrate my script, I have a named instance INST01 with the engine and the agent. I put the Agent Service in the local administrator group:

Of course, at the end I give you the complete script:

$Instance = 'INST01'
$Inst = '*'+$Instance+'*'
$MSSQL_Services = Get-WmiObject -Class Win32_Service | where {$_.Name.ToUpper() -like '*MSSQL*'} | where {$_.DisplayName.ToUpper()-like $Inst }
$Agent_Services = Get-WmiObject -Class Win32_Service | where {$_.Name.ToUpper() -like '*SQLAgent*'} | where {$_.DisplayName.ToUpper()-like $Inst }
$FullText_Services = Get-WmiObject -Class Win32_Service | where {$_.Name.ToUpper() -like '*MSSQLLFDLauncher*'} | where {$_.DisplayName.ToUpper()-like $Inst }


if (-not ([string]::IsNullOrEmpty($MSSQL_Services)))
{
$MSSQL_Services_local = $MSSQL_Services.name
$MSSQL_Services_local = "*$MSSQL_Services_local*"
Get-LocalGroupMember -Name 'Administrators' | where{$_.Name -like $MSSQL_Services_local}
}
if (-not ([string]::IsNullOrEmpty($Agent_Services)))
{
$Agent_Services_local = $Agent_Services.name
$Agent_Services_local = "*$Agent_Services_local*"
Get-LocalGroupMember -Name 'Administrators' | where{$_.Name -like $Agent_Services_local}
}
if (-not ([string]::IsNullOrEmpty($FullText_Services)))
{
$FullText_Services_local = $FullText_Services.name
$FullText_Services_local = "*$FullText_Services_local*"
Get-LocalGroupMember -Name 'Administrators' | where{$_.Name -like $FullText_Services_local}

We can do better every time (add a list of servers to verify, read the CMS) but with this blog, I will give you a start.

Don’t hesitate to come back to us if you need to test and see your compliance with the CIS document.

We can help you!  😉