In this article, we will explore the functionality to secure reporting Services with Group Managed Service accounts ( GMSA)

With Reporting Services’ (SSRS) ability to scale out to multiple hosts, you may quickly come across the need to reuse domain accounts. Updating passwords for such accounts becomes a lengthy and risky process often requiring downtime. You may have a reliable process, set in stone for updating service account credentials every 30, 60 or 90 days or you could be stumbling across accounts that haven’t changed in 5 years and the passwords are in a shared text file somewhere. In either case, there’s risk that’s hard to mitigate without magically creating more hours in the day.

The same trouble probably applies to other applications or components of SQL Server but I want to demonstrate how to better secure SSRS while simultaneously reducing administrative effort and service downtime. This holy grail can be achieved by utilizing a feature introduced in Windows Server 2012 called Group Managed Service Accounts (GMSA).

GMSA take the same functionality of Managed Service Accounts, introduced in Windows Server 2008 R2, and make it usable, most importantly, across multiple hosts. If this is all new information, I’ll explain a little about gMSAs:

A GMSA is an active directory (AD) account that requires no password. They can be “applied” to a group of host servers in AD and then used to run a service on all those hosts. Passwords are auto generated, managed, and reset every 30 days by Active Directory, requiring no user maintenance. You can find more specifics on how that all works on this great blog post by Doug Symalla.

Unlike the first iteration of this technology in MSAs you can use GMSAs with SQL Server, its component applications (SSRS) and even run scheduled tasks. They are a little more complex to set up, so I’ll cover all of the steps here.

Preparation

To create and enable a GMSA, first ensure that the hosts that you would like to involve, exist in a Security Group that is the lowest domain level necessary. Typically, this would be a Domain Local group. Here’s one I created with my SSRS servers in it, called SSRSDEV.

If this is the first time using GMSAs or MSAs in your AD forest, we’ll need to also create the KDS Root Key. This is used by the KDS service to generate passwords. From a domain controller execute the following PowerShell command:

You can also use this command if you want to avoid the built-in 10-hour wait designed to ensure propagation to all other domain controllers:

Creating the GMSA

We are now ready to create the gMSA using the following Powershell command from a domain controller:

You can see we have set the security group as the group that’s allowed to retrieve the password.

We’ve now created our group managed service account and we’ve told it which hosts are allowed to use it. The next step is to install the GMSA on the hosts inside our security group, the ones we’ll use it on. Again, we can do this in PowerShell which is great but if you’re not a fan, there is no other method I’m afraid.

Before running these commands we will need to ensure that the Active Directory module is installed. This can be added as a feature in the Server Manager:

Select Active Directory module for Windows PowerShell found under Remote Server Administration Tools > Role Administration Tools > AD DS and AD LDS Tools. Or you can do this with PowerShell:

Now we can “Install” the gMSA on the server:

We can then test that the account installed on the host correctly by running this:

The test command should return True or you’ll receive a verbose error message.

With SSRS and most other applications, you’ll also need to consider setting Service Principal names if you want Kerberos authentication to work properly. With SQL Server the application does this for you so you don’t need to worry about that. As we’re setting up SSRS I’ll demonstrate what you need to do.

This is the command to make changes to the service principal names on the gMSA. SPNs are passed through the -ServicePrincipalNames parameter as an array:

You can also use this command to make other changes to the SPNs too

To remove values, use the following:

To replace values:

To clear all values:

You can specify more than one change by separating the arrays by semicolons. For example, use the following syntax to add and remove service principal names.

If you are making multiple changes to SPNs in a single command it’s worth noting that the operators are applied in the following order:

• Remove
• Add
• Replace

You’ll also need to enable delegation for the new GMSA to utilize Kerberos authentication using the following PowerShell command:

Adding the GMSA to SSRS

The last part of the process is to finally add the GMSA to the Reporting Services service.

Open the Reporting Services Configuration Manager and from the Service Account tab delete the account info you have already and enter the GMSA name suffixed with a $ (dollar sign). This is important. Leave the password field blank and click Apply. (It will look like a password has been filled in once you Apply changes but you don’t provide this.

If everything is configured correctly your SSRS host will now be using a group managed service account. Repeat this on each server in your scale-out deployment and you’re done.

Taking it to the cloud

I’ve described how to setup and use gMSAs within an Active Directory domain but what if you have an SSRS Scale-out deployment in Azure, under Azure Active Directory? This great Docs article describes the steps required when using AAD and again, it’s easily setup using PowerShell.

Conclusion

GMSAs aren’t the simplest method to getting an environment set up and that probably doesn’t suit agile development or cases where environments are destroyed and created as needed, but the benefits in the long run outweigh the plethora of steps described above to get it up and running. Implementing this approach, if you don’t already employ gMSAs on other services shouldn’t be a hard sell to the business:

• Improved Security
• Reduced administration time
• No downtime
• Elimination of human error in password update processes

For some more in-depth detail on gMSAs and getting started with them check out this Docs page which goes into great detail on the security benefits, how to set a GMSA up and also how to remove it.

• Author
• Recent Posts

Craig Porteous

Craig is a Microsoft certified BI Developer & Administrator and has worked with the Microsoft SQL stack for over 8 years. From working with cloud technologies like AWS & PowerBI to managing enterprise level Projects & deployments, Craig is passionate about developing his skills. He enjoys contributing to a personal blog to give back to the SQL community. In his spare time Craig is an avid runner

View all posts by Craig Porteous

Latest posts by Craig Porteous (see all)

• How to secure Reporting Services with Group Managed Service Accounts (GMSA) - November 7, 2018
• Contribute, contribute, contribute! - June 20, 2018
• Top 10 things you must document in SQL Server Reporting Services (SSRS) - February 26, 2018