SQL Server Service Account Password Rotation (Including SPN Updates and AAG Handling)

 

Runbook: SQL Server Service Account Password Rotation (Including SPN Updates and AAG Handling)

---

Environment

• Primary Server: ServerA
• Secondary Server: ServerB
• SQL Server: AlwaysOn Availability Group (AAG) with Automatic Failover

---

Task Breakdown

1. Before Change Start Tasks ✅

1.1 Raise Change Ticket

• Open a Change Request (CR) in ITSM Jira tool.
• Add details:
  • SQL Service account password rotation.
  • Update SPNs.
  • Application validation.
• Attach:
  • Impact Analysis
  • Backout Plan

1.2 Get CAB Approval

• Submit CR for CAB review and approval.
• Get approvals from:
  • CAB
  • QA
  • Application owners

1.3 Create New AD Service Account.

• Request AD team to create:
  • Username: SQLService_ServerA
  • Password: Strong and non-expiring.
• Permissions:
  • Add to Domain Users
  • Add to Local Administrators Group

Command to Add Account to Local Admin Group:

net localgroup administrators "DOMAIN\SQLService_ServerA" /add

1.4 Grant SQL Server Access

• Connect to SQL Server as sysadmin.

SQL Command:

CREATE LOGIN [DOMAIN\SQLService_ServerA] FROM WINDOWS;

ALTER SERVER ROLE sysadmin ADD MEMBER [DOMAIN\SQLService_ServerA];

---

2. During Change Tasks✅

2.1 Deregister Old SPN (If Moving to New Account)

Command:

setspn -D MSSQLSvc/ServerA.domain.com:1433 DOMAIN\OldAccount

setspn -D MSSQLSvc/ServerA.domain.com DOMAIN\OldAccount

2.2 Register SPN for New Account

Command:

setspn -S MSSQLSvc/ServerA.domain.com:1433 DOMAIN\SQLService_ServerA

setspn -S MSSQLSvc/ServerA.domain.com DOMAIN\SQLService_ServerA

(Repeat for ServerB)

2.3 Update SQL Server Services to Use New Account

• Open services.msc.
• For each server (ServerA, ServerB):
  • Update SQL Server and SQL Server Agent service logon credentials.

Alternative PowerShell Command:

Get-WmiObject -Class Win32_Service -Filter "Name='MSSQLSERVER'" | Set-Service -Credential (Get-Credential)

Get-WmiObject -Class Win32_Service -Filter "Name='SQLSERVERAGENT'" | Set-Service -Credential (Get-Credential)

2.4 Restart Services

Manual:

• Restart SQL Server (MSSQLSERVER) service.
• Restart SQL Server Agent (SQLSERVERAGENT) service.

Command Line:

net stop MSSQLSERVER
net start MSSQLSERVER
net stop SQLSERVERAGENT
net start SQLSERVERAGENT

2.5 Validate Connectivity

PowerShell Command:

Test-NetConnection ServerA -Port 1433

• Connect using SSMS to ensure no login failures.

2.6 Validate SPNs Registered Correctly

Command:

setspn -L DOMAIN\SQLService_ServerA

• Verify entries exist for MSSQLSvc.

2.7 Perform 4-Eye Review

• Second DBA reviews:
  • Service accounts.
  • SQL login permissions.
  • SPN registrations.
  • Connectivity tests.

2.8 Perform Changes on Secondary Server (ServerB) First

• Update service accounts and restart services on ServerB first.

2.9 Failover AG to ServerB

SQL Command:

ALTER AVAILABILITY GROUP [AGName] FAILOVER;

• Confirm AG is online and healthy on ServerB.

2.10 Apply Changes on New Secondary (ServerA)

• Update service accounts and restart services on ServerA.

2.11 Optional: Failback to ServerA

• If required, failback AG to ServerA after validations.

---

3. After Change Complete Tasks✅

3.1 Perform Final Validation

• Confirm:
  • Database status (synchronized, online)
  • Application connectivity
  • SQL Agent jobs running successfully
  • No Kerberos errors in event logs

3.2 Remove Old Service Account Access

• Remove old service account from SQL Server logins:

SQL Command:

DROP LOGIN [DOMAIN\OldAccount];

• Remove old service account from local Administrators group:

Command:

net localgroup administrators "DOMAIN\OldAccount" /delete

• Plan for deletion of the old AD account after a retention period (e.g., 30 days) if no longer in use.

3.3 Close Change Ticket

• Update with:
  • Work notes
  • Test evidence (screenshots, SPN outputs)
• Change status to Completed.

3.4 Send QA Approval Email

Example Email Text:

Subject: SQL Service Account Password Rotation Completed Successfully

Hi QA Team,

SQL Server service account password rotation completed successfully. All validations passed. Kindly validate application end-to-end. 

Regard

Apratim Verma

✌️👍🤝 

Done!