Microsoft® Kerberos Configuration Manager for SQL Server®

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.

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, Microsoft SQL Server instances and Always On Availability Group Listeners installed on a server.
• Report on all SPN and delegation configurations on the server.
• Identify potential problems in SPNs and delegations.
• Fix potential SPN problems.

Supported Operating Systems

Windows Server 2008, Windows Server 2016, Windows 10, Windows Server 2012 R2, Windows Server 2008 R2 SP1, Windows Server 2012, Windows 7, Windows 8, Windows Server 2019

Supported SQL Server versions

SQL Server 2008 through SQL Server 2019

Support notes:

• SQL Server 2008 and SQL Server 2008 R2 are no longer supported. For more information, refer to SQL Server end of support options.
  
• SQL Server Reporting Services in SharePoint mode is not supported.
  
• For Always On Availability Group Listeners discovery, run this tool from the owner node.
  
• Microsoft Kerberos Configuration Manager for SQL Server requires a user with permission to connect to the WMI service on any machine its connecting to. For more information, refer to Securing a Remote WMI Connection.
  
• The following are required on the machine where the Kerberos Configuration Manager for SQL Server is launched:
• .NET framework 4.0 or higher.

To install:

1. Download the 32-bit or 64-bit version of the Kerberos Configuration Manager (KCM) installer that matches your computer’s OS architecture.
2. Click Open to start the installation immediately or click Save to save the installation .msi file to disk and install it later.
3. Accept the license term of this tool.
4. Click Next to complete the installation.
5. By default, this application is installed under %SystemDrive%:\Program Files\Microsoft\Kerberos Configuration Manager for SQL Server.

To launch the KCM tool:

1. After the installation is complete successfully, double click KerberosConfigMgr.exe to launch the application.
2. To troubleshoot a connectivity issue with SQL Server, connect to the target computer with a domain user account that has user permission to that computer.
3. To troubleshoot a connectivity issue with SQL Reporting Services, connect to the target computer with a domain user account that has administrator level permissions to that computer.

To generate the SPN list from the command line:

1. Open a command line window.
• IMPORTANT: To troubleshoot connectivity issue with SQL Reporting Services, launch the command line window as an administrator.
2. Switch to the folder where KerberosConfigMgr.exe is located.
3. Type KerberosConfigMgr.exe -q -l
4. For more command line option, type KerberosConfigMgr.exe -h

To save a server’s Kerberos configuration information:

1. Connect to the target Windows machine.
2. Click on Save button on the toolbar.
3. Specify the location where you want the file to be saved to. It can be on a local drive or a network share.
4. The file will be saved as XML.

To view a server’s Kerberos configuration information from a saved file:

1. Click on the Load button on the toolbar.
2. Open the XML file generated by Kerberos Configuration Manager.

To generate a script to fix an SPN from the command line:

1. Click on the Generate button for the SPN entry.
2. The generated script can be used by a user who has privilege to fix the SPN on the server.

To see the log files for the KCM tool:

1. By default, a log file is generated each time the application is executed, and placed in the user’s application data folder: %APPDATA%\Microsoft\KerberosConfigMgr

Options to get help:

• Hover the mouse cursor over the command for tooltip.
• Run KerberosConfigMgr.exe –h from command line.
• Click the Help button in the toolbar.

Kerberos is a complicated subject to comprehend and troubleshoot. The following reading materials provide additional reference information.

• Kerberos Authentication Overview
• How to use Kerberos authentication in SQL Server
• Register a Service Principal Name (SPN) for Kerberos Connections
• Delegating authentication
• Troubleshooting Kerberos Delegation
• Solving Connectivity errors to SQL Server