Icons made by Srip from Flaticon

How to Configure IIS application pool and SQL Servers to using a Group Managed Service Accounts

I developed an application that runs on IIS and queries multiple SQL servers to manage databases. The application uses a local SQL account in each SQL server to communicate with the web app. We want to simplify the account management and make the application more secure using gMSA.

In this story, we configure gMSA (step 1) and set up both IIS (step 7) and SQL Server (step 10) to use manage service accounts

1. Overview of Managed Service Accounts

We have two types of Managed Service Accounts:

• A standalone Managed Service Account (sMSA) is a managed domain account that provides automatic password management, simplified service principal name (SPN) management, and the ability to delegate the management to other administrators.
• The group Managed Service Account (gMSA) provides the same functionality within the domain but also extends that functionality over multiple servers. When a gMSA is used as service principals, the Windows operating system manages the password for the account instead of relying on the administrator to manage the password.

Benefits of using gMSA as the service account for SQL or IIS Services.

• We can use them across multiple servers and services
• Automatic password management
• Administrators do not require to store the passwords in a password vault
• It uses a very complex password(120 characters)
• Administrators don’t know the password
• Automatic SPN registration

2. Requirements:

• Active Directory scheme is set to 2012 R2 level.
• Enable Key Distribution Service (KDS) in the AD domain
• AD PowerShell Module installed in Domain Controller and SQL Servers

3. Installing the AD PowerShell Module

We open Powershell as Administrator and install the Powershell AD-module:

Install-WindowsFeature -Name RSAT-AD-PowerShell

4. Enabling the Key Distribution Service (KDS)

Group managed service accounts require to enable Key Distribution Service (KDS) using the PowerShell. It takes 10 hours for full synchronization between all AD domain controllers.

We open PowerShell on the AD Domain Controller and type the following commands:

Note: we can use the switch –EffectiveTime, so that we don’t have to wait for 10 hours

Add-KdsRootKey –EffectiveTime ((get-date).addhours(-10));

To validate if KDS was enabled, we can use the following command:

Get-KdsRootKey

5. Creating the Global Manage Security Group

We open PowerShell on the AD Domain Controller and type the following command:

NEW-ADGroup –name "gMSASQL" -description "Global Managed Security Group for SQL Servers" –path "OU=SQL,DC=kopicloud,DC=local" -GroupCategory Security -groupscope Global

6. Adding Servers to the Global Manage Security Group

We open PowerShell on the AD Domain Controller.

We want to add the SQL Server to gMSA group, type the server name followed by the dollar ($) sign.

Add-ADGroupMember "gMSASQL" -Members "KOPI-DEV-SQL$"

to add multiple servers type server names, separated by a comma:

Add-ADGroupMember "gMSASQL" -Members "KOPI-DEV-SQL1$", "KOPI-DEV-SQL2$"

Note: After adding all the servers to the Global Manage Security Group, it is recommended to reboot member servers.

7. Creating a New Global Manage Security Group Managed Service Account for IIS

We open PowerShell on the AD Domain Controller.

To create a service account for the IIS account, we type:

New-ADServiceAccount -name SQLIIS -DNSHostName SQLIIS.kopicloud.local -Description "Managed Service Account for IIS" -PrincipalsAllowedToRetrieveManagedPassword gMSASQL -Path "OU=SQL,DC=kopicloud,DC=local"

• Name: the gMSA service account name
• DNSHostName: the FQDN of the service account
• Description (optional): the description of the service account
• PrincipalsAllowedToRetrieveManagedPassword: Specify the AD group name we created in Step 5. Create the Global Manage Security Group
• Path (optional): the location where the account will be stored

If we check on the Active Directory Users and Computer console, we can see the service account is now visible:

8. Installing the IIS Service Account on the Server

We open PowerShell on the Member Server and we type the following command to install the service account on the server:

Install-ADServiceAccount SQLIIS

To validate that the service account was installed successfully, type:

Test-ADServiceAccount SQLIIS

9. Configuring IIS to use a Managed Service Account

We open the IIS Management Console and selected the application pool to use with the managed service account.

We right-click the application pool and select Advanced Settings.

We select the Identity item and click on the three dots button, then change to Custom account.

Now we type the name of the domain name, backslash, and Managed Service Account with “$” add the end. We leave the Password and Confirm password text boxes empty.

10. Creating a New Global Manage Security Group Managed Service Account for SQL Servers

We open PowerShell on the AD Domain Controller.

To create a service account for the SQL Server, we type:

New-ADServiceAccount -name SQLIIS -DNSHostName SQLIIS.kopicloud.local -Description "Managed Service Account for SQL" -PrincipalsAllowedToRetrieveManagedPassword gMSASQL -Path "OU=SQL,DC=kopicloud,DC=local"

Note: take a look to the step 7. Creating a New Global Manage Security Group Managed Service Account for IIS for details

11. Settings Extra Permissions for Kerberos Authentication

We open PowerShell on the AD Domain Controller and we type the following command to allow the gMSA account to register its SPN (service principal name) for Kerberos authentication in SQL Server.

dsacls (Get-ADServiceAccount -Identity SQLDev).DistinguishedName /G "SELF:RPWP;servicePrincipalName"

12. Install the SQL Service Managed Account on the SQL Server machine

We open PowerShell on the Member Server and we type the following command to install the service account on the server:

Install-ADServiceAccount SQLDev

Note: take a look to the step 8. Installing the IIS Service Account on the Server for details

13. Configuring SQL Services to use the SQL Service Managed Account

We open the SQL Server Configuration Manager and select SQL Server Services.

We select the Log On tab, then This Account, and type domain name, backslash, and Managed Service Account for SQL with “$” add the end. We leave the Password and Confirm password text boxes empty.

Then, to start the SQL Server service, we right-click on the SQL Server service and click on Start.

And that’s all folks. If you liked this story, please show your support by 👏 this story. Thank you for reading!