SQL Server , Excel VBA: Using Group Managed Service Accounts with IIS 10 on Server 2016

Using NetworkService powered application pools does have the nice effect, that there is no password needed, because the pool will be running with the credential of the webserver machine account, which is a domain account, where no password management is needed.

To access resources on the network, the webserver machine account must be enabled on the network destination and everything is fine and secure using windows authentication or Kerberos

This approach is good enough, if the scenario is limited to one application per server, because the minute you need another application, which does have different requirements in terms of security, then this approach will fail.

Lets assume, there are 2 web apps on the machine, which each does have its own SQL Server DB and which should not be allowed to access the other ones data.

This scenario can only be used with custom domain accounts, if windows authentication should be used.

Only with 2 different accounts and 2 application pools, the security on each database can be limited to the one matching application pool.

But then someone has to manage this domain passwords and make sure, that they are not expiring, but still changed from time to time. A tedious task and the passwords are probably distributed across the company, hopefully in a secure way and not inside XLS or Textfiles…

Another way with Server 2016 is to use Group Managed Service accounts.

This requires, that Active Directory scheme is on level 2012 R2, only then, the feature “Group Managed Service Accounts” can be used.

Setup a Group Managed Service Account

Login to DC:

Enable gMSA globally on Domain

— for Lab environments we use the switch –EffectiveTime, so that we don’t have to wait for 10 hours, which usually should make sure, that AD sync is ready.

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

This will usually be done from the Active Directory team in your environment

Open ServerManager => Tools => Active Directory Administrative Center

Add new global SecurityGroup named gMSAGroup

Go to AD Admin Center and search for the newly created group (gMSAGroup)

OR: Use Powershell and first install the Powershell AD-modules

Install-WindowsFeature -Name RSAT-AD-PowerShell

Then create the global security group using

NEW-ADGroup –name “gMSAGroup” –path “OU=XYZ,DC=mydomain,DC=com” -GroupCategory Security -groupscope Global

Right click the gMSAGroup entry and add all the memberserver, which should be able to use the Group Service Managed Account IIS1Svc

or use Powershell:

Add-ADGroupMember "gMSAGroup” -Members "Server1$", "Server2$"

After adding all the memberservers to the Group Managed Service Group, they must be rebooted!

Create first gMSA Account on the DC: (max 15 chars)

New-ADServiceAccount IIS1SvC -DNSHostName IIS1Svc.corp.litware.com -PrincipalsAllowedToRetrieveManagedPassword gMSAGroup

optionally use –path to define, whe the account should be placed into the domain structure, eg:
-Path "OU=OUXy,DC=mydomain,DC=com"

Check in AD Admin Center, that the account is visible
S

Switch to MemberServer (HSW2K12R2Web1)

Install on MemberServers: Remote Server Administration Tools via Server Manager to get Active Directory Module for Windows Powershell

OR with Powershell: Install-WindowsFeature -Name RSAT-AD-PowerShell

Open Powershell Admin Console and

Install-ADServiceAccount IIS1Svc

If error is “access denied”, make sure, that the memberserver was added to the allow list of the group service group and the server was rebooted afterwards!