1

We are getting below issue :

‘SQL Server cannot authenticate using Kerberos because the Service Principal Name (SPN) is missing, misplaced, or duplicated.’

I found an article how to get it fixed. But I have questions below. Can anyone please help them clarified.

https://blogs.msdn.microsoft.com/sqlupdates/2014/12/05/sql-server-kerberos-and-spn-quick-reference/

  1. Current our auth_scheme is NTLM and how NTLM is different from Kerberos.
  2. What exactly is this SPN because I am worried about my PRODUCTION servers.
  3. Which is more secure and recommended by Microsoft. "NTLM" or "Kerberos".
  4. In what cases it is used.
  5. What if I don't fix this error, what issues I am going to face ?

Thanks

2

NTLM is a challenge/response type system, and while it has a resonable level of security it is potentially vunerable to attack and credential theft. In fact NTLM V1 is a known attack vector, considered insecure and should be disabled on your systems. NTLM V2 is more secure, and the current standard.

Kerberos handles mutual authentication between parties, ensuring that the tokens are encrypted and secure. I'd recommend reading the Kerberos basic authentication concepts to get a handle on it. The reading is a little dry, but it will give you a greater understanding.

Of the two, Kerberos is far more secure, and is the recommended protocol for Windows connections to SQL Server.

To resolve your SPN problem look for duplicate tickets using SETSPN-L with both the server, and the service account, and look for duplicates. Remove those, and then adjust the SQL Server service account to allow it to self register its service principal name. This will allow it to handle things itself, which makes life a lot easier.

You will not experience any actual problems with SQL (except for times when you might be using linked servers, or some other double hop type scenario where the ticket cannot be passed along to a second machine), and so it may not be a critical fix to you. Only your security team can really define that.

  • @dba.stackexchange.com/users/11639/nic,if I set SPN,will it affect my current set up ? What are the things I need to consider ? Can you please tell me ? – SQLBoy Sep 24 '16 at 8:10
  • 1
    It won't damage your current setup in any way (just make it more secure). In the event that kerberos authentication fails it will drop back to using NTLM. – Nic Sep 24 '16 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.