Sql-server – MSSQLSvc Service Principal Names, Kerberos, and NTLM

active-directorykerberossql server

Was recently helping a DBA with an issue that appeared to be related to an invalid SPN. Discovered that a good number of SQL service accounts simply don't have an SPN set, resulting in NTLM authentication.

I've added SPN configuration to our build process, but am unsure whether going back to existing systems using NTLM and configuring an SPN to allow Kerberos authentication will break anything.

Are there any common scenarios where configuring MSSQLSvc SPNs to allow Kerberos authentication will break existing systems that are functioning without issue over NTLM?

Here's the code I'm using:

#Query to identify authentication type for various connections on a SQL server
    $query = "SELECT
        s.session_id,
        c.connect_time,
        s.login_time,
        s.login_name,
        c.protocol_type,
        c.auth_scheme,
        s.HOST_NAME,
        s.program_name
    FROM sys.dm_exec_sessions s
    JOIN sys.dm_exec_connections c
    ON s.session_id = c.session_id"

#Everything comes back NTLM
#Source: https://raw.githubusercontent.com/RamblingCookieMonster/PowerShell/master/Invoke-Sqlcmd2.ps1
    Invoke-Sqlcmd2 -ServerInstance ServerInQuestion -query $query

#Get a list of SPNs associated with ServerInQuestion
#Results indicate no SPNs exist
#Source: http://gallery.technet.microsoft.com/scriptcenter/Get-SPN-Get-Service-3bd5524a
    Get-SPN -ServiceType MSSQLSvc -ComputerName ServerInQuestion

#Configuring SPNs for test systems results in Kerberos working without issue
    setspn -A "MSSQLSvc/ServerInQuestion.DOMAIN.XXX:1433" DOMAIN\SVCACCOUNT
    setspn -A "MSSQLSvc/ServerInQuestion.DOMAIN.XXX" DOMAIN\SVCACCOUNT

My concern is that if I go back to existing systems and configure SPNs, I might break existing applications or processes that work with NTLM but not Kerberos. I assume they would fail back to NTLM if needed, but this isn't my area of expertise and I'm uncomfortable making that assumption.

Thanks!

Best Answer

You're probably right to be at least a little concerned. Unfortunately, whether a specific application will break or not all depends on the application. But in general, it is not likely that anything will break as long as you set the SPNs properly.

My main piece of advice would be: An incorrect SPN is worse than no SPN at all.

The only thing I see being a potential problem for you is if the SPNs are set, but set incorrectly.

Take this example:

  • If a remote client attempts to authenticate to SQL and finds a valid SPN, it will use Kerberos.
  • If the remote client attempts to connect and finds no SPN, it will use NTLM.
  • If the remote client attempts to connect and finds an SPN, and then tries to use that SPN to authenticate via Kerberos but fails because the SPN was invalid... will it still fail back to NTLM or not?

That last question is very specific behavior that varies by application and Windows version.

This is a good follow-up article, even though it's old, it's still mostly relevant:

http://blogs.msdn.com/b/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx

Update:

If it's any consolation, I just ran a test using SQL Server 2012, and a remote client running Server 2012 R2 and SQL Management Studio. When the SPN was registered properly, Kerberos was used. When the SPN was absent, the SQL Management Studio failed over to NTLM. When I purposely introduced a typo into the SPN on the service account, the connection was still established, and failed over to NTLM. So that's good news, but the fact remains that other unspecified applications might not implement Windows authentication correctly to take advantage of that negotiate protocol... so if you have odd applications, you'll still want to test them. And in your testing, don't forget that NTLM will always be used for local connections, regardless of SPNs... depending on OS version. :D

Related Topic