DataBases and Operating Systems

Hi

Description:

MSSQL 2012 server need to be configured with Kerberos authentication under gMSA and MSA service accounts.

Solution:

1. Need to create accounts. Run this command to create gMSA account (in Active Directory PowerShell):

new-adserviceaccount -name mssqlgmsa -dnshostname dev_dc01.dev.local                                                                            -PrincipalsAllowedToRetrieveManagedPassword "MSSQLSERVERS"

mssqlgmsa – account name

dev_dc01.dev.local – DC name where i run this command

mssqlservers – security group that you should create in computers OU in AD and add as memebers all MSSQL servers where you want to use this gMSA account.

2. Run this command to create MSA account:

new-adserviceaccount -name mssqlmsa -RestrictToSingleComputer

mssqlmsa – account name 

3. Configure SPN records for accounts created above:

setspn -D MSSQLSvc/MSSQL_2012_DEV.dev.local:MSSQLSRV2012X64 mssqlgmsa 

setspn -D MSSQLSvc/MSSQL_2012_DEV.dev.local:1433 mssqlgmsa 

setspn -D MSSQLSvc/MSSQL_2012_DEV.dev.local:MSSQLSRV2012X86 mssqlmsa

setspn -D MSSQLSvc/MSSQL_2012_DEV.dev.local:2433 mssqlmsa

MSSQLSvc – MSSQL service name. It is standard for all servers.

MSSQL_2012_DEV.dev.local – MSSQL server FQDN name

MSSQLSRV2012X64 and X86 – MSSQL instance names

1433 and 2433 – MSSQL ports configured for connections

mssqlgmsa and mssqlmsa – service accounts

Now you can configure MSSQL service on MSSQL server to run under AD gMSA and MSA accounts. To do that in service properties 

choose log on as dev\mssqlgmsa$ or dev\mssqlmsa$ and do not provide a password.

After that all domain users with permission to log in to the database will use Kerberos.

Thank you.