Kerberos Configuration Manager for SQL Server SPN Validation

Problem

Kerberos is the default authentication protocol for Active Directory domains starting with Windows 2000 and it has been the recommended authentication protocol for almost two decades. Kerberos is more secure and faster than NTLM. Review this article to see other advantages of using Kerberos over NTLM.

In some cases by default, applications connect with NTLM authentication protocol when we setup SQL Server and configure applications to connect to SQL Server instance.

To enable Kerberos for SQL Server we need to create Server Principal Names (SPNs) in Active Directory if they are not registered automatically.

We have created (registered) SPNs for one of our servers, but still see many connections that use NTLM.

We’ve heard about Kerberos Configuration Manager that can help and we have these questions:

• How SQL Server security relates to the Kerberos authentication?
• How do we validate Kerberos authentication on SQL Server?
• What is Kerberos Configuration Manager?
• How to use Kerberos Configuration Manager?
• How do we generate and validate SPNs with Kerberos Configuration Manager?

Solution

When we talk about Kerberos authentication protocol and SQL Server security we should understand that this is only applicable to the Windows Authentication method. SQL Server logins do not use Kerberos as they are not authenticated with the Active Directory domain. This is another reason to use, where possible, Windows logins vs. SQL Server logins.

More information about Kerberos and how it works with SQL Server could be found in the “FAQs Around Kerberos and SQL Server“. You can find a lot of useful information in these FAQs even if you have some knowledge about Kerberos and SPNs.

Validating Authentication Properties Used by Connections

We have used the SetSPN tool and the steps from this Microsoft article. Here is a basic syntax example for the SQL Server SPN (it should run from a command line by a person with enough permissions in Active Directory to register SPNs):

setspn -A MSSQLSvc/host.domain.com:1433 domain\accountname

This TechNet Wiki article has more details about the SetSPN tool’s syntax.

We will run the following query to verify the connections’ authentication:

USE [master]
GO
SELECT COUNT(auth_scheme) as sessions_count, net_transport, auth_scheme 
FROM sys.dm_exec_connections
GROUP BY net_transport, auth_scheme

It looks like only a small number of connections use Kerberos:

Note, that connections with “SQL” authentication (auth_scheme) can’t use Kerberos as these are SQL authenticated logins, not Windows. The “net_transport” column will always return “Session” when a connection has both multiple active result sets (MARS) enabled, and connection pooling enabled as per this Microsoft documentation.

We should keep in mind that Dedicated Admin Connection (DAC) only uses NTLM and some of the local (internal) connections will use the “Shared memory” net_transport.

We will ignore the records with the “SQL” Authentication Scheme as well as “Session” and “Shared memory” net_transport.

Read more about connection properties used in the sys.dm_exec_connections Dynamic Management View here.

Here is an example of the “Shared memory” connections:

SELECT DISTINCT 
   DB_NAME(database_id), 
   auth_scheme, 
   net_transport, 
   client_net_address, 
   local_tcp_port, 
    [host_name], 
    [program_name], 
   login_name
FROM sys.dm_exec_connections c JOIN 
   sys.dm_exec_sessions s ON c.session_id = s.session_id 
WHERE [host_name] = SUBSTRING(@@SERVERNAME, 1, LEN(@@SERVERNAME) - CHARINDEX('\', REVERSE(@@SERVERNAME)));

We can see that these connections in our example are originated from the SQL Server host.

Kerberos Configuration Manager

The next step to resolve SPN issues is to use the Kerberos Configuration Manager.

Kerberos Configuration Manager Interface

Kerberos Configuration Manager is a tool provided by Microsoft and it helps to troubleshoot Kerberos-related connectivity issues. It validates SPNs and can generate scripts for you to create missing SPNs. You can use Kerberos Configuration Manager for Kerberos authentication validation and troubleshooting for SQL Server, SQL Server Reporting Services (except SharePoint integrated mode), and SQL Server Analysis Services.

The first screen has general information about the tool:

When we click “Connect” we get the following screen with instructions for the connection information:

After connecting to the server, you will see three tabs – “System”, “SPN” and “Delegation”:

The “System” tab will have information about the system you are connected to:

Tithe “Delegation” tab helps to identify potential problems in delegations. This might be helpful when you troubleshoot double-hop Kerberos authentication issues:

The “SPN” tab is the one that we will be using in our tip for troubleshooting configuration issues related to the Kerberos.

The results of the scanning are usually “Good” or “Missing”. But sometimes you can see “Warnings” as well. We will review a couple of examples later in our tip:

If you have any missing SPNs you will see buttons next to the “Missing” SPN status. This allows you to generate scripts for SPNs creation (“Generate” for the “SPN Script” column) or fix them right away if you have permissions to do so (“Fix” for the Action column).

If you are a Database Administrator chances are that you don’t have permissions to register SPNs in Active Directory. In this case, you can generate the scripts and send them to Domain Administrators to execute.

Note, that you can generate separate scripts for each “Missing” line or have a single script that will fix all issues:

Scripts

When you click the “Generate” or “Generate All” button the scripts can be saved as “*.cmd” files. Here is an example of a script:

:Prompt
set /p answer=Are you sure you want to continue? (Y/N):

if %answer% == Y goto Yes
if %answer% == y goto Yes
if %answer% == N goto No
if %answer% == n goto No

cls
echo Unknown input
goto Prompt

:No
exit

:Yes

SetSPN -s "MSSQLSvc/DEMOSQLSRV1.domain.com" "domain\SQLSvcAccount"

set /p answer=Press any key to continue...
@echo on

Running only the SetSpn command from this script using the “cmd.exe” will work just fine as well:

SetSPN -s "MSSQLSvc/DEMOSQLSRV1.domain.com" "domain\SQLSvcAccount"

Validating SQL Server Availability Group Listeners

We have installed and ran the tool on a remote server (which is not part of the Availability Group), and it didn’t show any issues with our configuration, but we still can’t connect to the Availability Group Listener name using Kerberos.

After a closer look at the “System Requirements” for the Kerberos Configuration Manager on the download page we have discovered that the tool must run from a Primary Node of the Availability Group:

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

After installing Kerberos Configuration Manager on a primary node and running it against the Availability Group Listener we had the following results:

The generated script contained these SetSPN commands:

SetSPN -s "MSSQLSvc/AG1.domain.com:INST1" "domain\SQLSvcAccount1"
SetSPN -s "MSSQLSvc/AG1.domain.com:75507" "domain\SQLSvcAccount1"
SetSPN -s "MSSQLSvc/AG2.domain.com:INST2" "domain\SQLSvcAccount2" 
SetSPN -s "MSSQLSvc/AG2.domain.com:75507" "domain\SQLSvcAccount2"
SetSPN -s "MSSQLSvc/AG3.domain.com:INST3" "domain\SQLSvcAccount3" 
SetSPN -s "MSSQLSvc/AG3.domain.com:75507" "domain\SQLSvcAccount3"

Note, that for each Availability Group there will be 2 SPN records – for the Instance name and the Instance port number.

The reason for multiple records is that (as per this Microsoft’s article) there are 3 SPN formats – one for an instance using TCP and two others that work with “a protocol other than TCP”:

Examples of the SPN validations

• We already reviewed the “Missing” status of the SPNs. The lines with “Missing” status will have the option to generate scripts or fix SPNs.
• “Good” status is self-explanatory. It means SPNs are registered correctly and no action is required. Note, that some SPNs can be registered automatically when SQL Server service account is a virtual account or MSA (Managed Service Account):

• Sometimes you can see “Warning” under the Status column:
  • In our first screenshot of the “SPN” tab, we had a “Dynamic Port” warning. Dynamic ports impact Kerberos authentication because SPNs have to be deleted and registered again every time instance’s port changes.
  • We were able to spot another “Warning” that was related to the Reporting Services Kerberos configuration:

Exceptions, Observations, and other “Good to Know” things

Here are some quick tips that might be useful if you are new to the Kerberos Configuration Manager and/or Kerberos:

• Read the prerequisites and make sure that the Kerberos Configuration Manager is installed properly (for example, for the Availability Groups) and permissions allow you to run the tool. Here are some of the prerequisites:

“…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.”

• Virtual accounts and MSAs can register SPNs automatically.
• Dynamic ports don’t work well with SPNs.
• Cross-domain connections require special configuration in order for Kerberos to work. Otherwise, connections will use NTLM.
• If you connect to the SQL Server instance from the same host where SQL Server is installed then you may see connections using NTLM.
• Internal connections and DAC, as we mentioned before, won’t use the Kerberos either. They will be connected using Shared memory and NTLM.
• Depending on your Availability Group setup you may need to register SPNs for both – Listener and instance name (for example, if a secondary replica is readable). Note, that highlighted TCP ports on the screenshot below are different. The reason is that a DBA was connected with SQL Server Management Studio (SSMS) directly to the SQL Server instance name instead of the Availability Group Listener name:

• You can validate SSRS and SSAS SPNs, not only SQL Server ones.
• Note, that the tool doesn’t generate and/or validate SQL Server Browser SPNs for the named SSAS instances (for multiple SSAS instances using dynamic ports). You can find details in this Microsoft article. Here is an example of SetSPN syntax for SQL Server Browser to use Kerberos with SSAS named instances:

Setspn -S MSOLAPDisco.3/DEMOSRV01.Domain.com DEMOSRV01

Summary

Kerberos authentication issues are sometimes difficult to troubleshoot. Setting SPNs seems straightforward, but until you validate it you can’t be sure your connections use Kerberos.

In this tip, we went through the Kerberos Configuration Manager interface and provided a couple of examples for the tool usage.

Next Steps

• Find more tips about SQL Server Security (including SPNs) here.
• Microsoft provides the additional reading materials on the Kerberos Configuration Manager download page.
• This blog post has steps for the Kerberos setup with Availability Groups.
• “Questions About Kerberos and SQL Server That You Were Too Shy to Ask“.
• Another great resource is the Kerberos Survival Guide Wiki. It hasn’t been updated for a couple of years, but it has tons of useful links.