Granting read access to SQL Server services with Just Enough Administration

We’ve all been there as DBAs…people requesting access to the servers that we look after to be able to view certain things.

I’ve always got, well, twitchy with giving access to servers tbh…but what if we could completely restrict what users could do via powershell?

Enter Just Enough Administration. With JEA we can grant remote access via powershell sessions to servers and limit what users can do.

So let’s run through an example. Here we’re going to create a configuration to allow users to view the status of the MSSQLSERVER and SQLSERVERAGENT services only.

Firstly, let’s create a session configuration file: –

New-PSSessionConfigurationFile -SessionType RestrictedRemoteServer  -Path .JeaSqlConfig.pssc

I stripped out pretty much all the default settings in the file to leave it as this: –

@{
# Version number of the schema used for this document
SchemaVersion = '2.0.0.0'
# ID used to uniquely identify this document
GUID = '60732de2-33cc-420b-a745-5596b27cf761'
# Author of this document
Author = 'Andrew Pruski'
# Description of the functionality provided by these settings
Description = 'Allow users to view and restart SQL Server services'
# Session type defaults to apply for this session configuration. Can be 'RestrictedRemoteServer' (recommended), 'Empty', or 'Default'
SessionType = 'RestrictedRemoteServer'
# Directory to place session transcripts for this session configuration
TranscriptDirectory = 'C:JEASQLSERVER'
# Whether to run this session configuration as the machine's (virtual) administrator account
RunAsVirtualAccount = $true
# User roles (security groups), and the role capabilities that should be applied to them when applied to a session
RoleDefinitions = @{ 'DOMAINtestuser' = @{ RoleCapabilityFiles = 'C:JEASQLSERVERJeaSqlConfig.psrc' }; } 
}

The important part in the file is this: –

RoleDefinitions = @{ 'DOMAINtestuser' = @{ RoleCapabilityFiles = 'C:JEASQLSERVERJeaSqlConfig.psrc' }; }

This defines the user(s) that have access to the server…in this case [DOMAINtestuser]. However we haven’t set what that user can do…for that we need a role capability file. Also note, we’re not granting any other permissions on the server to this user…permissions and capabilities are solely defined in JEA config files.

Now create the role capability file: –

New-PSRoleCapabilityFile -Path .JeaSqlConfig.psrc

Again, I stripped out all the defaults and left the file as: –

@{
# ID used to uniquely identify this document
GUID = '44de606d-8ac0-4b27-bd3f-07cad2378717'
# Author of this document
Author = 'apruski'
# Description of the functionality provided by these settings
Description = 'JEA Role Capability File for SQL Server Services'
# Company associated with this document
CompanyName = 'Pure Storage'
# Copyright statement for this document
Copyright = '(c) 2022 Andrew Pruski. All rights reserved.'
# Cmdlets to make visible when applied to a session
VisibleCmdlets = @{ Name = 'Get-Service'; Parameters = @{ Name = 'Name'; ValidateSet = 'MSSQLSERVER', 'SQLSERVERAGENT' }}
}

The last part of the file is again the important part. Here the file is saying that the user can run the Get-Service cmdlet for the MSSQLSERVER and SQLSERVERAGENT services on the target server.

OK, now copy the files to the target server: –

Invoke-Command -ComputerName <<SERVERNAME>> -Script {New-Item C:JEASQLSERVER -Type Directory}
Copy-Item .JeaSqlConfig* \<<SERVERNAME>>C$JEASQLSERVER

OK, now we can create the configuration on the target server. A word of warning however…this can be done via a remote powershell session but it sometimes errors out. If you get an error, RDP to the server and then run the command: –

Register-PSSessionConfiguration -Name SqlConfig -Path C:JEASQLSERVERJeaSqlConfig.pssc

To view the configuration: –

Get-PSSessionConfiguration -Name SqlConfig

N.B. – I’m using my lab for my Chaos Engineering demos to set this up, that’s why the domain for the user is “Chaos”

Ok, now we can test. Create a credential for the test user and open a remote powershell session to the target server:-

$Cred = Get-Credential
Enter-Pssession -ComputerName <<SERVERNAME>> -ConfigurationName sqlconfig -Credential $Cred

And then test viewing the MSSQLSERVER service: –

Get-Service -Name MSSQLSERVER

The results should display as normal: –

However if they try to view another service, an error will display: –

Similarly, if they try to run a different cmdlet: –

And that’s how to use JEA to give user’s access to view ONLY the SQL Server services on a target server. That’s a very basic demo of JEA as there’s a tonne of cool stuff that you can do with it but I hope that’s useful.

Thanks for reading!