Group Managed Service Accounts (gMSA) – New in Windows 2012
1. Password for gMSAs are generated and maintained by the Key Distribution Service (KDS). This allows multiple hosts to use the gMSA. Member servers that wish to use the gMSA, simply query the DC for the current password.
2. Password will be changed on every 30 Days (Default)
A. Create the KDS Root Key**
#it will take up to 10 Hours to complete as it need to ensure that the changes are replicated to all DC
Add-KdsRootKey –EffectiveImmediately
#or
#Use the following in Lab Enviroment to take affect Immediately
Add-KdsRootKey –EffectiveTime ((get-date).addhours(-10))
B. Create gMSA
I will use sqladmin as Service Account for SQL2014 and allow MDT-SQL1 & MDT-SQL2 Server to use it
New-ADServiceAccount SQLAdmin -Description "SQL Service Account" -DNSHostName sqladmin.mymdt.local -PrincipalsAllowedToRetrieveManagedPassword MDT-SQL1$,MDT-SQL2
C. Install gMSA in Local SQL Server
Install-WindowsFeature RSAT-ADDS
Install-ADServiceAccount -Identity sqladmin
Test-ADServiceAccount sqladmin # Ensure that the return value = TRUE
D. Preparation of SQL INI File
Install .NET 3.5
Install-WindowsFeature Net-Framework-Core
Mount the SQL2014 ISO File, and start the installation of SQL 2014 Server.Please stop at the last final screen
Copy the file highlighted in RED BOX as the reference INI file to install SQL 2014 Silently later
E. Provisioning of SQL2014 silently with INI File
Save the SQL Configuration in C:\Temp\SQLConfig.ini and run the following to start the installation silently
PS D:\> .\setup.exe /ConfigurationFile=C:\Temp\SQLConfig.ini /IAcceptSQLServerLicenseTerms /q
You can verify the SQL Installation log file to ensure that all components are installed successfully
#Verify SQL Setup Log
$SQLSetupLog="C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log"
Get-Content $SQLSetupLog\summary.txt
F. To ensure SQL Services started successfully whenever server is rebooted
Configure Dependency Service for MSSQLSERVER
$Path="HKLM:\System\CurrentControlSet\Services\MSSQLSERVER\"
$NewName="DependOnService"
$NewValue=@("KEYISO","W32Time","netlogon")
New-ItemProperty $Path -Name $NewName -Value $NewValue -PropertyType MultiString
Configure the MSSQLSERVER services to Delay Startup and restart 3 times in every minute
$MSSQLSERVER ="MSSQLSERVER"
$SQLVERSERAGENT ="SQLSERVERAGENT"
#Restart 3 Time every 1 Minute
$Action = "restart/60000/restart/60000/restart/60000" #60000 Milliseconds = 1 Day
#Reset the count after 1 day
$Reset="86400"
SC.EXE \\$env:ComputerName Config $MSSQLSERVER Start= Delayed-Auto
SC.EXE \\$env:ComputerName Config $SQLVERSERAGENT Start= Delayed-Auto
SC.EXE \\$env:ComputerName failure $MSSQLSERVER reset=$Reset actions= $Action
SC.EXE \\$env:ComputerName failure $SQLVERSERAGENT reset=$Reset actions= $Action
Reference links
1. https://blogs.technet.microsoft.com/askpfeplat/2012/12/16/windows-server-2012-group-managed-service-accounts/
2. https://blog.waynesheffield.com/wayne/archive/2018/02/using-gmsa-sql-server/