Category Archives: Config

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:\JEA\SQLSERVER'

# 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 = @{ 'DOMAIN\testuser' = @{ RoleCapabilityFiles = 'C:\JEA\SQLSERVER\JeaSqlConfig.psrc' }; } 

}

The important part in the file is this: –

RoleDefinitions = @{ 'DOMAIN\testuser' = @{ RoleCapabilityFiles = 'C:\JEA\SQLSERVER\JeaSqlConfig.psrc' }; }

This defines the user(s) that have access to the server…in this case [DOMAIN\testuser]. 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:\JEA\SQLSERVER -Type Directory}

Copy-Item .\JeaSqlConfig* \\<<SERVERNAME>>\C$\JEA\SQLSERVER

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:\JEA\SQLSERVER\JeaSqlConfig.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!

Using SQL Client Aliases

~

SQLServerCentral.com recently published my case study on how my company implemented Windows Containers running SQL Server to streamline our QA setup process. If you haven’t seen it, it’s here.

One of the problems that we ran into when moving to using containers was how to get the applications to connect. Let me explain the situation.

The applications in our production environment use DNS CNAME aliases that reference the production SQL instance’s IP address. In our old QA environment, the applications and SQL instance lived on the same virtual server so the DNS aliases were overwritten by host file entries that would point to 127.0.0.1.

This caused us a problem when moving to containers as the containers were on a separate server listening on a custom tcp port. Port numbers cannot be specified in DNS aliases or host file entries and we couldn’t update the application string (one of the pre-requisites of the project) so we were pretty stuck until we realised that we could use SQL client aliases.

The client aliases would allow us to replicate the DNS aliases that we had in production and we wouldn’t have to change the app connection string.

Setting up aliases is simple enough if you have the SQL configuration manager installed. Simply navigate down to the SQL Native Client section within the config manager, right click and fill out the name, server IP and port number: –

N.B. – I always do both 64 and 32-bit sections to be safe

But we didn’t want to install the config manager on the QA servers and also, that’s a very manual process. So we scripted it out.

First thing to do was get the SQL Native Client installed on all the VMs. The way we did this was to install it on one and then create a VM template that all the other servers were built from. The SQL Native Client installer can be downloaded from here. (that’s for SQL 2012 btw as that’s the version of SQL that we worked with).

Once that was done we then ran a powershell script to create the aliases directly in the registry of the server. Here’s a sample of the script: –

# First of all create the registry keys to hold the aliases
New-Item -Path "HKLM:\Software\Microsoft\MSSQLServer\Client\" -Name ConnectTo -Force -ErrorAction Stop | Out-Null
New-Item -Path "HKLM:\Software\Wow6432Node\Microsoft\MSSQLServer\Client\" -Name ConnectTo -Force -ErrorAction Stop  | Out-Null

# Then create the aliases
$ServerName = "192.168.1.7";
$dbPort     = "10010";
$TCPAlias   = "DBMSSOCN," + $ServerName + "," + $dbPort

$Aliases = @("Alias1","Alias2")

foreach($Alias in $Aliases)
{
    New-ItemProperty -Path "HKLM:\Software\Microsoft\MSSQLServer\Client\ConnectTo\" -Name $Alias -PropertyType String -Value $TCPAlias -ErrorAction Stop | Out-Null
    New-ItemProperty -Path "HKLM:\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo\" -Name $Alias -PropertyType String -Value $TCPAlias -ErrorAction Stop | Out-Null
}

Obviously we wrapped the script up in a load of try…catch blocks to make sure that if the keys were already there then it would drop & re-create but the core of what it does is above. What the script above will do is create two aliases that map to server 192.168.1.7 on port 10010: –

This allowed us to move the apps from using local instances of SQL to using containers listening on a custom port on a remote server without having to change anything in the app configuration. This reduced our server setup time significantly and freed up resources on the app server. Also, we could control what container the apps were pointing to via the registry entries! Very handy.

Thanks for reading, let me know if you have any questions.