CODEWITHSUNDEEP
sql-serverpowershellwmigmsa
Jamie
Jamie

Reputation: 483

How do I add a group Managed Service Account to a newly created domain, add to security group, and assign the account to that group of computers?

With a newly create domain, the SQL Servers require a group Managed Service Account (gMSA) to run their services. What are steps from assigning a Kerberos capability to the gMSA through creating the Security Group in the domain, to assigning the computers to the security group and providing the machines in that group, the ability to pull the accounts using the PrincipalsAllowedToRetrieveManagedPassword parameter, and possibly deal with the inital WMI error while trying to assign the gMSA to the database engine on SQL Server?

Upvotes: 0

Views: 5805

Answers (2)

Cliff Williams
Cliff Williams

Reputation: 33

@Jamie answer is good but one important step is missing. If you are switching an existing SQL server to use a gMSA you must set the ServicePrincipalName. If you are installing a new SQL server the Install program will do this for you.

There are several ways to do this.

Use the Set-ADServiceAccount cmdlet:

Set-ADServiceAccount -Identity gmsaSQL -ServicePrincipalName @{add="MSSQLSvc/sqlhost.contoso.com:1433"}

Use SetSPN:

SetSPN -S MSSQLSvc/sqlhost.contoso.com:1433 contoso\gmsaSQL$

Grant right to the gMSA to create the service principal name:

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

The SQL Server service should set the SPN when it starts. (This has been hit or miss for me)

Remember to set all required permissions for the database, Log and backup directories.

Upvotes: 1

Jamie
Jamie

Reputation: 483

  • Create a group Managed Service Account using the Active Directory PowerShell module by running

New-ADServiceAccount -name gmsaSQL -DNSHostName gmsaSQL.contoso.com

  • Install the gMSA to the Managed Services Accounts in the Active Directory

Install-ADServiceAccount -Identity gmsaSQL

  • Test if the gMSA is working properly

Test-ADServiceAccount -Identity gmsaSQL

  • Create an Active Directory Organizational Unit in the Active Directory

New-ADOrganizationalUnit -Name "Security Groups" -Path "DC=Contoso,DC=com"

  • Create an Active Directory Security Group in the new OrganizationalUnit

New-ADGroup -Name "SQL Servers" -GroupScope Global -GroupCategory Security -Path "OU=Security Groups,DC=Contoso,DC=com"

  • Add computer objects to Security Group

Add-ADGroupMember -Identity "CN=SQL Servers,OU=Security Groups,DC=Contoso,DC=com" -Members "MyComputer$"

  • Assign the gMSA to SQL Server security group (later add other computers if required)

Set-ADServiceAccount -Identity gmsaSQL -PrincipalsAllowedToRetrieveManagedPassword "SQL Servers"

  • Open SQL Server Configuration Manager on each server where SQL Server services are installed

  • In SQL Server Configuration Manager, select SQL Server Services in the left pane.

  • In the right pane, right-click on SQL Server (MSSQLSERVER) and select Properties.

  • In the SQL Server (MSSQLSERVER) Properties dialog box, click on the Log On tab.

  • Select the This account option and enter the name of the gmsaSQL$ service account in the format contoso\gmsaSQL$.

  • Click on OK to save the changes.

  • If a WMI error occurs upon assignment, check the MOF file in the program files (x86) path

  • To resolve any WMI Provider Error that may occur, you can try repairing or recreating MOF file using mofcomp command. After running this command, restart WMI service for changes to take effect.

Upvotes: 1

Related Questions