SSPI Error Fixing using Kerberos Configuration Manager
Have you ever faced below errors, then you would have to try the Keberos Configuration manager to identify the cause. This will guide you to fix the SSPI error while connecting to a remote instance.
Login failed for user ‘NTAUTHORITY\ANONYMOUS’
Login failed for user ‘(null)’
Login failed for user ”
Cannot generate SSPI Context
The Kerberos Configuration Manager for SQL Server is a diagnostic tool that helps troubleshoot Kerberos related connectivity issues with SQL Server, SQL Server Reporting Services (SSRS), and SQL Server Analysis Services (SSAS). It can perform the following functions:
- Collect information about operating system (OS), Microsoft SQL Server instances, and Always On Availability Group Listeners installed on a server.
- Report all Service Principal Name (SPN) and delegation configurations on the server.
- Identify/Suggestion of fix to potential problems in SPNs and delegations.
Let us see how the SSPI Error looks like,
“Cannot connect to [ServerName\inst1].
The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)”
Download and install Kerberos Configuration Manager from Microsoft site.
There you can find a standalone exe file KerberosConfigMgr.exe which needs to copy to a different server than the issue exists in same domain, or if you have domain trust enabled you can run from your local machine as well.
Run KerberosConfigMgr.exe as Administrator.
Go to Connect and enter the remote server name, use SQL service account as user name and password.
The tool will show the connected server details for the user logged on as below.
In case if there any issues found on the check then it will have an image like (Source : Google)
Click on Fix All will allow you to save a file that will have the queries to Run on AD to fix the SPN issues
Once the fix has been applied in in AD, the rerun the KCM, will give you a healthy result as shows below.
Share this:
Like this:
4 thoughts on “ SSPI Error Fixing using Kerberos Configuration Manager ”
Getting below error after running this.
The was an issue with accessing UserAccount information from the System, Please check logs at %AppData%\Microsoft\KerberosConfigMgr for more information.
Getting error and log is empty
Adding to this, you can use the account which has user rights to that machines. Also input as below
ServerName : SERVERNAME.FQDN
User Name : username (do not use domain\username)
password : xxxx
Also please use the same domain account.
Also you can check if that Administrator group has any uncleaned GUIs , please refer below articles.
Also log would be available in C:\Users\”username”\AppData\Roaming\Microsoft\KerberosConfigMgr
You can connect as below
To Start the Tool:
After the installation is complete successfully, double-click KerberosConfigMgr.exe to start the application.
To troubleshoot the connectivity issue with SQL Server, connect to the destination computer by using a domain user account that has user permissions to that computer.
To troubleshoot the connectivity issue with SSRS, connect to the destination computer by using a domain user account that has administrative permissions to that computer.
To Generate the SPN List from the Command Line:
Go to the command line. (Note: To troubleshoot the connectivity issue with SSRS, start the command line window as administrator.)
Switch to the folder where KerberosConfigMgr.exe is.
Type KerberosConfigMgr.exe -q -l.
For more command-line option, type KerberosConfigMgr.exe -h.
To Save a Server’s Kerberos Configuration Information:
Connect to the target windows server.
Click the Save button on the toolbar.
Specify the location where you want the file to be saved. It can be on a local drive or a network share.
The file will be saved as the .XML format.
To View a Server’s Kerberos Configuration Information from the Saved File:
Click the Load button on the toolbar.
Open the XML file generated by Kerberos Configuration Manager.
To Generate a Script to Fix the SPN from the Command Line:
Click the Generate button for the SPN entry.
The generated script can be used by a user who has permissions to fix the SPN on the server.
Kerberos configuration manager как запустить
Kerberos authentication provides a highly secure method to authenticate client and server entities (security principals) on a network. To use Kerberos authentication with SQL Server, a Service Principal Name (SPN) must be registered with Active Directory, which plays the role of the Key Distribution Center in a Windows domain. In addition, many customers also enable delegation for multi-tier applications using SQL Server. In such a setup, it may be difficult to troubleshoot the connectivity problems with SQL Server when Kerberos authentication fails.
Here are some additional reading materials for your reference.
Why use this tool?
The Kerberos Configuration Manager for SQL Server is a diagnostic tool that helps troubleshoot Kerberos related connectivity issues with SQL Server, SQL Server Reporting Services, and SQL Server Analysis Services. It can perform the following functions:
- Gather information on OS and Microsoft SQL Server instances installed on a server.
- Report on all SPN and delegation configurations and Always On Availability Group Listeners installed on a server.
- Identify potential problems in SPNs and delegations.
- Fix potential SPN problems.
This release (v4.0) adds support for Always On Availability Group Listeners.