How to view Service Principal Names in Active Directory

09 March,2021 by Jack Vamvas

In an environment with Active Directory and SQL Server using Kerberos ,Service principal names (spn) become important. Windows supports delegating to NTLM protocol if Kerberos is not available , but being able to use Kerberos is the primary method. 

NTLM (New Technology LAN Manager)  is a propietery Microsoft authentication protocol.The NTLM protocol is considered a legacy authentication protocol with various vulnerabilities, which pose a security risk. It's based on a relatively weak cryptographic scheme and is vulnerable to various attacks. It's replaced with Kerberos, which is a lot more secure and recommended. NTLM authentication should only be used in a secure trusted environment, or when Kerberos can't be used.

Linux on SQL Server containers relies on ADUtils - a toolset developed by Microsoft to support Integrated authentication on the Container environment. It is a Kerberos only solution , therefore SPNs and management of SPNs becomes critical to support Integrated authentication from applications dependant on SQL Server Containers

SPN is similar to an alias of an AD object used for Computer,Service & User account. 

 

From Windows Command Prompt use setspn

setspn -l  myservername

 

From Powershell 

Example 1 : get the spns for a specific computer object in the same domain 

Get-ADComputer -Identity myservername -Properties ServicePrincipalNames |Select-Object -ExpandProperty ServicePrincipalNames

Example 2 : get the spns for a specific user object in a different domain using the Powershell Get-ADUser cmdlet 

Get-ADUser -Identity myuser -Properties ServicePrincipalNames -server myotherdoamin|Select-Object -ExpandProperty ServicePrincipalNames

 

Read more on Service Principal Names , Kerberos and Active Directory 

List the differences between Kerberos and NTLM

Error: 0x202b State 15 The SQL Network Interface library could not register the Service Principal Name for the SQL Server service


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to view Service Principal Names in Active Directory


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer