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.