LLI Wiki — My Digital Knowledge Base

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:

& 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

Mastodon LLIMastodon SocialFosstodon