SQL Server – Kerberos Authentication – Service Principal Name & Klist

Background

One of the things to check for when experiencing Kerberos Authentication authentication errors is to determine the Service Principal Name and issue klist request.

Once klist is issued, review its output.

 

Trouble Shooting

Get MS SQL Server Instance Service Name

There are a few ways to get the SQL Server Engine’s name, here are some of them..

sys.dm_server_services

Script


select 
		  [sqlInstance]	
			= serverproperty('servername') 	

		, [computerNetbios]	
			= serverproperty('ComputerNamePhysicalNetBIOS') 	

		, [serviceName]
			= tblSDSS.serviceName

		, [serviceAccount]
			= tblSDSS.service_account

from   sys.dm_server_services tblSDSS


 

Output

Output-Production-01

ays01

Output-Dev-01

eias01-serverservices

Control Panel \ Services Applet

Access Control Panel Services Applet…

Output-Production-01

controlpanel-services-ays01

Output-Dev-01

controlpanel-services-dev01

 

 

klist

Klist / List Kerberos tickets

Get Kerberos Tickets.

Syntax


klist tickets

 

Output
Output-Image – Production-01

klist-listtickets-ayso1

Explanation
  1. On each entry we want to look for the Server Entry
  2. We want to pay attention to the Server Name.  Here are the entries we see
    • krbtgt
    • host
    • cifs
    • ldap
  3. Notice MS SQL Server service, MSSQLSvc, is not listed

Output-Image – Development-01

klist-listtickets-eiaso1

Explanation

  1. You want to look for entries that have Server MSSQLSvc listed
  2. In the Screen Shot above that  is entry #2
    1. Pay Attention to the Encrytion TYpe
    2. And, the Kdc called
      1. KDC is the Domain Controller that satisfied our request

Klist / Attempt to get Kerberos tickets

Get Kerberos Tickets.

Syntax


set _serviceClass=MSSQLSvc
set _computerName=%computername%
set _domainName=%USERDNSDOMAIN%
set _FQDN=%_computerName%.%_domainName%
set _PortNumber=1433

rem klist Syntax
rem klist get MSSQLSvc/[computer-name].[domain-name]:1433

klist get %_serviceClass%/%_FQDN%:%_PortNumber%


 

Output
Output-Image – Production-01

klist-service-ayso1-brushedup

Output-Textual – Production-01

Current LogonId is 0:0x2fe0e41b
Error calling API LsaCallAuthenticationPackage (GetTicket substatus): 0x6fb

klist failed with 0xc000018b/-1073741429: The SAM database on the Windows Server
 does not have a computer account for this workstation trust relationship.


Output-Image – Development-01

klist-service-eiaso1
Explanation

  1. You want to look for entries that have Server /MSSQLSvc

 

References

  1. > Using Active Directory Domain Services > Mutual Authentication Using Kerberos  > Service Principal Names
    Link

 

Summary

When SQL Server Engine is running as “Local System”, it is running as a machine Account, and has more leeway.

Service Principal Names (SPN) does not have to be registered.

On the Other hand, when it is running under a Service Account, SPNs either have to be explicitly registered or the Service Account has to have the authority them itself.

More much Later…

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s