Preparing a server for SQL Server: AD accounts, local rights and SPNs
Create AD service accounts from a template, grant Lock pages in memory and volume maintenance rights, and register SQL Server SPNs.
This note collects the steps used to prepare a Windows Server for a SQL Server installation: install the required Windows features, create the service accounts in Active Directory by cloning a template account, grant the local rights needed by the SQL Server engine account, and register its Service Principal Names (SPNs).
Install Windows features
NET-Framework-Core needs the sources\sxs folder of the Windows installation media (here a mounted ISO).
$MountedISO_OS = Mount-DiskImage -PassThru "\\fileserver\share\windows-server.iso"
$driveLetterOS = ($MountedISO_OS | Get-Volume).DriveLetter + ":"
Add-WindowsFeature NET-Framework-Core -Source "$driveLetterOS\sources\sxs"
Add-WindowsFeature RSAT-Clustering-AutomationServer
Add-WindowsFeature RSAT-AD-PowerShell
RSAT-AD-PowerShell provides the New-ADUser and Get-ADUser cmdlets used below.
Create a service account from a template
New-ADUser -Instance copies the attributes of an existing account, so every new service account gets the same settings (OU, group memberships defined on the template are not copied, but attributes are).
$Template = Get-ADUser -Identity "svc_template"
$Newbie = "svc_sql"
New-ADUser -Name $Newbie -GivenName $Newbie -UserPrincipalName "svc_sql@contoso.com" `
-Path "OU=SVC,OU=SQL,DC=contoso,DC=com" -Instance $Template -Enabled $true `
-CannotChangePassword $true -PasswordNeverExpires $true `
-AccountPassword (ConvertTo-SecureString -AsPlainText "<PASSWORD>" -Force) -PassThru
Repeat with a different $Newbie for each account (engine, agent, SSIS, ...). Do not keep clear-text passwords in scripts; prompt for them or read them from a vault.
Grant local rights to the engine account
ntrights.exe (Windows Server 2003 Resource Kit tool) grants user rights from the command line. SQL Server benefits from these two rights:
SeLockMemoryPrivilege(Lock pages in memory)SeManageVolumePrivilege(Perform volume maintenance tasks)
& C:\temp\SQLScripts\ntrights.exe --% +r SeLockMemoryPrivilege -u CONTOSO\svc_sql -m \\sqlserver01
& C:\temp\SQLScripts\ntrights.exe --% +r SeManageVolumePrivilege -u CONTOSO\svc_sql -m \\sqlserver01
The --% token stops PowerShell parsing so that the arguments are passed as is.
Register the SPNs
With Kerberos, register the SPNs of the SQL Server service on the service account (short name and FQDN, with and without the port):
& SetSPN --% -s "MSSQLSvc/sqlserver01.contoso.com" "CONTOSO\svc_sql"
& SetSPN --% -s "MSSQLSvc/sqlserver01.contoso.com:1433" "CONTOSO\svc_sql"
& SetSPN --% -s "MSSQLSvc/sqlserver01" "CONTOSO\svc_sql"
& SetSPN --% -s "MSSQLSvc/sqlserver01:1433" "CONTOSO\svc_sql"
Notes
- All names, domains, paths and the password are generic placeholders.
- The original note also ran the SQL Server setup from the mounted media and a script to change the TCP port; those steps are not detailed here.