T-SQL Tuesday #016:Check Your Service Accounts with PowerShell

This T-SQL Tuesday is about Aggregate Functions.

It may be a bit of a stretch, but a security best practice to use separate service accounts for all your SQL Server services, so I've written some PowerShell code to check to see if any account is used more than once on a given machine. I take advantage of the SQLWmiManagement DLL to find the SQL Server services, which is a safer bet than filtering on a service name.

First I load the SQLWmiManagement DLL and initialize an empty collection to hold the unique service accounts used by the various services.

# Load SMO Wmi.ManagedComputer assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null

$srvaccts = @()  # Initialize a collection to hold the service accounts

Next, I've got a function which checks to see if a string value passed in as an argument already exists in the collection, returning either $True or $False.

# Create a function to iterate through the collection to check for duplicates
function Check-Duplicate([string]$cacct) {
	foreach ($sacct in $srvaccts) {
		if ($sacct.ToLower() -eq $cacct) {
			return $True
			}
		}
	return $False
	}

Now we can create a new ManagedComputer object connecting to the desired machine.

$mach = '.'
# Connect to the instance using SMO
$m = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $mach

Next, we iterate through the Services collection of the ManagedComputer object and get the ServiceAccount property. We pass that value to the function, and if it returns $False we add it to the collection of unique accounts used.

# Iterate through the services to check accounts
foreach ($srv in $m.Services) {
	[string]$acct = $srv.ServiceAccount

	# Add the Service Account to the collection if it's not already there
	$chkdup = Check-Duplicate($acct)
	if ($chkdup -eq $False) {
		$srvaccts += $acct
		}
	}

Here's where it gets interesting. We can now iterate through the unique service accounts and pull just the services used by each account into a variable called $tmp. If the Count property (see - I said there was aggregation involved) is greater than 1 for a given account, it fails the security best practices test, and we return the ServiceAccount, Name and Type properties.

# For each service account used, get the services used by that account
# and if there's more than one, return the account, name and type of service
foreach ($sacct in $srvaccts) {
	$tmp = $m.Services | where {$_.ServiceAccount -eq $sacct}
	$srv = $tmp | where {$tmp.Count -gt 1} | select ServiceAccount, Name, Type
	$srv
	}

The great thing is that if there are no duplicates, there's no result. If there are results, it's something I need to look into and correct.

Allen