GMSA: CREATE THE ACCOUNTS AND THE GROUP

December 27, 2020

So…

After creating the KDC keys in your Active Directory we can continue with where it all is about: creating the GMSA.

Before we will create the account, we first need to create a group. This group will hold all of the computeraccounts of the servers which run the services with the gMSA acccounts.

It is a good practice to follow your conventions. These conventions will merely hold naming conventions, place where your group will reside and supportable items.

In my case, I will just create a group named “Maroli-mgmt.SQL2019DE.gs” This is the group which will hold all the SQL Server computeracounts . It will be managed by my “Maroli-mgmt.SQLAdmins.gs”. In this group my DBA’s are added.

I will use the following command:

New-ADGroup -Name “Maroli-mgmt.SQL2019DE.gs” -SamAccountName “Maroli-mgmt.SQL2019DE.gs” -GroupCategory Security -GroupScope Global -DisplayName “Group used by GMSA for SQL Server” -Path “CN=Groups,DC=MaroliCIT,DC=Intra” -Description “Members of this group have the SQL servers role with a GMSA account ” -ManagedBy “Maroli-mgmt.SQLAdmins.gs”

You can check the group by using the get-adgroup command.

Now the group has been created and verified, the most important part of gMSA is there, we need to create the account. I will use the name “Maroli.SQL2016DE” for this.

New-ADServiceAccount “Maroli.SQL2016DE” -DNSHostName “Maroliict.intra” -PrincipalsAllowedToRetrieveManagedPassword “Maroli-mgmt.SQL2019DE.gs” -KerberosEncryptionType RC4, AES128, AES256 -TrustedForDelegation $true -ServicePrincipalNames  
 http/SQLFarm1.maroliict.intra/maroliict.intra,  
 http/SQLFarm1.maroliict.intra/maroliict,  
 http/SQLFarm1/maroliict.intra,  
 http/SQLFarm1/maroliict

This sets up the Group Managed Service accounts. But with GMSA in place, you can also add Service Principal Names for your servers. Therefore you need to modify the read and write to Service Principal Names. Therefore we can do this manual, but hey,

We are automating the stuff

So this means we need to be able to reproduce that. Therefore I have borrowed some code of my colleagues from Active Directory Management. That piece of code is down here:

# ===================== Functions 
Function Set-SpnPermission {  
\#https://dzone.com/articles/manage-serviceprincipalname-properties-using-power  
param(  
\[adsi\]$TargetObject,  
\[Security.Principal.IdentityReference\]$Identity,  
\[switch\]$Write,  
\[switch\]$Read  
)

if(!$write -and !$read){  
 throw “Missing either -read or -write”  
}

$rootDSE = \[adsi\]”LDAP://RootDSE”  
$schemaDN = $rootDSE.psbase.properties\[“schemaNamingContext”\]\[0\]  
$spnDN = “LDAP://CN=Service-Principal-Name,$schemaDN”  
$spnEntry = \[adsi\]$spnDN  
$guidArg=@(“”)  
$guidArg\[0\]=$spnEntry.psbase.Properties\[“schemaIDGUID”\]\[0\]  
$spnSecGuid = new-object GUID $guidArg  
if($read ){  
 $adRight=\[DirectoryServices.ActiveDirectoryRights\]”ReadProperty”  
}

if($write){  
 $adRight=\[DirectoryServices.ActiveDirectoryRights\]”WriteProperty”  
}

if($write -and $read){  
 $adRight=\[DirectoryServices.ActiveDirectoryRights\]”readproperty,writeproperty”  
}

$accessRuleArgs = $identity,$adRight,”Allow”,$spnSecGuid,”None”  
$spnAce = new-object DirectoryServices.ActiveDirectoryAccessRule $accessRuleArgs$TargetObject.psbase.ObjectSecurity.AddAccessRule($spnAce)  
$TargetObject.psbase.CommitChanges()   
return $spnAce  
}

Below are the command that we used to get the proper values and to combine the function and standard AD functions to a gMSA account which creates the SPN’s:

# ======================= Request the gMSA properities 

$groupObj = Get-ADServiceAccount $gMsaSvcAccountName -Properties ManagedBy -server $Domain  
\#$groupObj = get-adgroup -Identity $RetrieveMgmtPasswd #RetrieveMgmtPasswd  
$acl = ($groupObj.DistinguishedName) #group  
$TargetObject = “LDAP://$($acl)”  
$Identity = \[security.principal.ntaccount\]”SELF”  
Set-SpnPermission -TargetObject $TargetObject -Identity $Identity -write -read

This piece of code will do that trick.

That was it for this time. Next time we talk about modifications to the installation of SQL and what we need to do before installation.