How to fix Error 9821 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: Error 9821 – Internal Error During Time Zone Conversion in SQL Server 2025

Why This Error Happens

Error 9821 typically occurs when SQL Server’s internal time zone database (ICU-based tz data embedded in the engine) is out of sync with the Windows OS time zone registry entries, causing a mismatch during AT TIME ZONE or datetimeoffset conversions. This can also be triggered by a corrupted or outdated Windows ICU/tzdata package, incomplete Windows Updates, or custom/legacy time zone registrations (via tzutil or manual registry edits) that SQL Server’s conversion engine cannot resolve. In some cases, it stems from SQL Server service account permission issues preventing read access to HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones.

Step-by-Step Resolution Guide

Step 1: Verify and Update Windows Time Zone Data

  • Open PowerShell as Administrator and run Get-TimeZone -ListAvailable to confirm the time zone database isn’t corrupted.
  • Ensure Windows Server 2025 / Windows 11 has the latest cumulative update installed via Settings > Windows Update, since these updates often include ICU tzdata patches.
  • Run tzutil /g to confirm the current system time zone is valid and not a custom/unsupported entry.

Step 2: Check SQL Server Service Account Permissions

  • Open regedit and navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones.
  • Right-click the key, select Permissions, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has at least Read access.
  • Restart the SQL Server service after applying permission changes using services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.

Step 3: Rebuild or Refresh SQL Server’s Internal ICU Cache

  • Stop the SQL Server instance completely.
  • Clear any cached ICU data by running DBCC FREESYSTEMCACHE('ALL') before shutdown (if the instance is still responsive).
  • Reinstall the latest SQL Server 2025 Cumulative Update (CU) from the Microsoft Download Center, as CUs frequently refresh bundled ICU time zone libraries.
  • Restart the instance and re-run the failing query that referenced AT TIME ZONE or SWITCHOFFSET.

Step 4: Validate Time Zone Conversion Query Syntax

  • Re-check the T-SQL query causing the error for unsupported or deprecated time zone names (e.g., using "Pacific Standard Time" instead of IANA-style names not yet supported).
  • Replace ambiguous or custom time zone strings with standard Windows time zone names found in sys.time_zone_info.

Alternative or Verification Steps

  • Run SELECT * FROM sys.time_zone_info WHERE name = 'Your Time Zone Name'; to confirm SQL Server recognizes the zone correctly post-fix.
  • If the error persists, use sp_configure to check for any legacy compatibility level settings forcing outdated time zone handling, and consider running the query against a test database with the latest compatibility level (160+) to isolate whether it’s a database-specific or server-wide issue.