Introduction
After a recent SQL Server migration from 2012 to 2022, our team encountered a classic authentication issue:
The target principal name is incorrect. Cannot generate SSPI context.
Youtube video:
This blog post details my real-world journey identifying the root cause, the steps I followed, and key lessons for database administrators facing similar challenges.
Scenario: Local Logins Fine, Remote Logins Fail
An end user reported:
Local login (from the server running SQL Server) succeeded via Windows Authentication.
Remote login (from another server or workstation) repeatedly failed with the SSPI context error above.
Such errors often point to problems with Kerberos authentication, host/service principal names, or SQL Server’s service account configuration.
Background: What Changed During Migration?
Our upgrade steps involved:
Set up SQL Server 2022 on a new, upgraded Windows Server.
Restore databases from the SQL Server 2012 instance onto the new server.
Switch hostnames and IP addresses so that the new server replaced the old one.
Following these changes, remote Windows Authentication was broken—prompting our investigation.
Step 1: Initial Troubleshooting
As DBA, I:
Tried various SSMS versions (2012, 2022) from remote machines; both failed with the same error.
Attempted connections using the hostname and IP address—no luck.
Checked which service account was running the new SQL Server instance and found it used a virtual account:
NT Service\MSSQLServerRealized we didn’t know what service account the old (source) SQL Server used—the migration team hadn’t recorded it before decommissioning the old server.
Step 2: Investigating the Old Server’s Service Account (Without Booting It!)
Instead of powering up the old server just to check the service account, I coordinated with the backup team to recover the system’s Windows Registry “hive” files from backup:
SYSTEM
SOFTWARE
SECURITY
SAM
DEFAULT
Safely Loading Old Registry Hives
Because hive files are raw binaries, importing them directly might overwrite or corrupt settings on the new server. To avoid this, here’s the safe approach I followed:
1. Save the Registry Hive (on test machine, not live!)
powershellreg save HKLM\SYSTEM D:\temp\SYSTEM.bkp
2. Set Variables & Create a Temporary Branch
powershell$HivePath = "D:\temp\SYSTEM.bkp" $TempKey = "TempSYSTEM"
3. Load the Hive Temporarily
powershellreg.exe load "HKLM\$TempKey" "$HivePath"
4. Explore the Old Registry in regedit
Open Registry Editor. You should see a new branch (
TempSYSTEM) containing the old server’s settings.Locate SQL Server’s service account.
5. Clean Up
powershellreg.exe unload "HKLM\$TempKey"
Step 3: What I Found and Why It Mattered
The original (source) server used:
NT Authority\NetworkServiceThe new (target) server was using:
NT Service\MSSQLServer(a virtual account)
Why is this important?
The NetworkService account is a domain account, enabling Kerberos across the network. A virtual account is local-only and cannot register Service Principal Names (SPNs) in Active Directory—making Kerberos authentication for remote logins impossible.
Kerberos, SPNs, and SQL Server: Key Takeaways
Domain service accounts or Group Managed Service Accounts (gMSA) are recommended for SQL Server where Kerberos authentication or cross-host access is needed.
Virtual accounts are more restrictive—best for standalone, local-only database access.
SPNs for SQL Server must be registered in Active Directory for Kerberos to function. Virtual accounts can’t do this natively.
In summary:
Our migration accidentally moved a network-accessible SQL Server to a self-contained setup, restricting external access and breaking Kerberos logins.
Conclusion: Migration Lessons Learned
Document your SQL Server service account configuration before any migration!
For environments where remote Windows Authentication is required, always use a domain account or gMSA—not just a local virtual account.
Registry hive analysis is a safe and powerful way to recover configuration from offline systems without booting up or risking the production environment.
If you found this guide useful, consider sharing it with fellow DBAs or IT professionals managing SQL Server migrations. Questions? Leave a comment!
---------------- Hand written notes below -----------------------------------
Objective: Troubleshoot one of the reasons for the below error in MSSQL.
The target principal name is incorrect. Cannot generate SSPI context
Situation: End user reporting that they are facing the below condition..
a. Login using windows authentication from the same server (where sqlserver runs) works fine
b. Login using remote server fails with the message highlighted above
In the process of troubleshooting, we learn the following....
1. There was a sqlserver upgrade or migration from 2012 to 2022; which involves the following...
a. Setup SQLServer 2022 in a new server (since we need to upgrade the windows server as well)
b. Backup/restore the db from 2012 (old machine) to 2022 (new machine).
c. Switch the names of the old and new machine including IP address.
2. Post this end users are reporting the problem highlighted above. As a DBA I did the following..
a. I validated if SSMS version has any issues from remote server, both version 2012 and version 20 both failed with same message
b. I changed the hostname (from remote server) in ssms to connect to actual hostname, ip, still the same error
c. Validated the service used for MSSQL instane
i)Service name: NT Service\MSSQLServer [this is a virtual account]
d. But what was the service account used in source server, the team which performed the migration didnt notice the service account config from source. The server is shutdown.
i) The server can be brought back online to examine the service account usage -- but that will be a last resort.
ii) So the server backup at vmlevel was secured
1) I requested the backup team to navigate to the registry hive file located under C:\Windows\System32\config
2) restore all the following hive files: SYSTEM, SOFTWARE, SECURITY, SAM, DEFAULT
3) Now is the tricky situation, how will I asses the hive files???
Hive files are kind of db binary file in raw format. When it is loaded to registry using regedit>Import, you can access the data in readable format.
But if you load asis, you will purge or overwrite the target machine setting.
To prevent this situation we need to follow the below procedure
Step 1)
##for testing, you cant access live registry. So you need to save registry in raw format using this command:
##powershell in admin mode:
reg save HKLM\SYSTEM D:\temp\SYSTEM.bkp
In real life scenario, you will restore the hive files from backup of the old machine.
Step 2)
##Store path of the hive file from above
$HivePath = "D:\temp\SYSTEM.bkp"
Step 3)
##Create a temporary branch to load the hive file in current server itself, this will avoid registry overwrite
$TempKey = "TempSYSTEM"
Step 4)
## Load the registry key
reg.exe load "HKLM\$TempKey" "$HivePath"
Step 5) OPen regedit, you should see the new branch and registry from old system loaded here. Now we can assess whatever we need; for example MSSQL service account details.
Step 6) Close regedit.
Step 7) Unload the registry
reg.exe unload "HKLM\$TempKey"
Step 8) open regedit and examine if you still see the temporary branch
====Actual output:
PS C:\Windows\system32> reg save HKLM\SYSTEM D:\temp\SYSTEM.bkp
The operation completed successfully.
PS C:\Windows\system32> cd D:\temp\
PS D:\temp> dir
Directory: D:\temp
Mode LastWriteTime Length Name
---- ------------- ------ ----
-a---- 10/09/2025 22:33 20422656 SYSTEM.bkp
PS D:\temp> $HivePath = "D:\temp\SYSTEM.bkp"
PS D:\temp> $TempKey = "TempSYSTEM"
PS D:\temp> reg.exe load "HKLM\$TempKey" "$HivePath"
The operation completed successfully.
PS D:\temp> reg.exe unload "HKLM\$TempKey"
The operation completed successfully.
=====================
So this is how you can assess a particular root cause. In my case the service account config was improper in the target.
Source: NT Authority\NetworkService
Target: NT Service\MSSQLSERVER
The earlier one allows network access of the db (kerberos setup), later virtual account doesnt allow that.
SPNs and Kerberos: With a domain service account or gMSA, SPNs for MSSQLSvc can be registered and managed centrally; with virtual accounts, SPNs are not auto-registered in AD, so a domain account is preferred when Kerberos is required across hosts.
So the DBA who migrated the db, brought a network accessed db into a self constrained setup trimming all external access (more restricted setup by using virtual account).
-------------------
