Released: Microsoft Kerberos Configuration Manager for SQL Server 4.1

[Pedro Lopes]

First published on MSDN on Nov 22, 2017
We are pleased to announce the latest generally-available (GA) of Microsoft Kerberos Configuration Manager for SQL Server .

Get it here: Download Microsoft Kerberos Configuration Manager for SQL Server

Note : this replaces the previously released v4.0.

Why Kerberos?
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.

• 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

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.1) adds support for Always On Availability Group Listeners, and fixes SPN format incompatibility with Windows Server 2008 and 2008 R2 (introduced in v4.0).
Notes

• 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 .


• For Always On Availability Group Listeners discovery, run this tool from the owner node.


• Also, if needed for troubleshooting, the Kerberos Configuration Manager for SQL Server creates a log file in %AppData%\Microsoft\KerberosConfigMgr .

[afthornbury]

I think I have come across an odd situation for which this tool does not currently cover: disjoint namespace between the dnsRoot and nETBIOSName (aka Pre-Windows 2000 name) attributes for an Active Directory Domain. For example, there is a domain with a FQDN of "company.com" but the NetBIOS Name is "DOMAIN" (that is not a typo).

In this situation, there is a computer with a default instance of SQL Server 2017 installed and joined to the domain with an FQDN of computer.company.com and a NetBIOS (pre-windows 2000 name) of DOMAIN\computer. There are currently the following SPNs registered to the computer.company.com (aka DOMAIN\computer or company.com\computer) account. This can verified with the following commands:

SetSPN -L company.com\computer$
SetSPN -L DOMAIN\computer$

MSSQLSvc/computer:1433
MSSQLSvc/computer
MSSQLSvc/computer.company.com:1433
MSSQLSvc/computer.company.com

When running Kerberos Configuration Manager, it indicates that the SPNs are misplaced and proposes the following to resolve the issue:

SetSPN -d "MSSQLSvc/computer.company.com" "company\computer$"
SetSPN -s "MSSQLSvc/computer.company.com" "DOMAIN\computer$"
SetSPN -d "MSSQLSvc/computer.company.com:1433" "company\computer$"
SetSPN -s "MSSQLSvc/computer.company.com:1433" "DOMAIN\computer$"

The two SetSPN -d commands will fail as there is no such NetBIOS domain name of "company". The two SetSPN -s commands will also fail because there are already SPNs for DOMAIN\computer$.

Is this an unexpected bug for which Kerberos Configuration Manager was not designed to handle? Or is there something else afoot here? Thank you for your help and creating this great tool!

[Brandonhw]

I had the exact same issue as @afthornbury 

It would appear this tool could use some more development.