SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Understanding Kerberos, Part I

A get a lot of questions where I work about Kerberos and how it works for SQL Server, whether we're talking about the database engine or Reporting Services. I also see it quite a bit on Twitter. This is a series of posts that looks to explain Kerberos in more detail than a single article or post would. For today we'll just tackle some basic questions I see a lot.

What is Kerberos?

Kerberos, at least as far as computers are concerned, is a security protocol which provides authentication services. Basically, Kerberos helps with the "Who are you?" question. Kerberos was taken from Greek mythology, specifically the name for the three-headed dog, which we usually spell Cerberus.

When did Kerberos come about?

In the Microsoft world, Kerberos was first introduced with the operation system with Windows 2000. However, it was developed at MIT some time in the '80s (at least, Kerberos version 4 was). Therefore, it is not a "Microsoft protocol."

Why did Microsoft start moving to Kerberos?

Quite simply, to solve issues that the previous core security protocol, NTLM (NT LAN Manager), couldn't. Among these:

  • Authenticate the server and thus make Man-in-the-Middle (MITM) attacks more difficult.
  • Allow for two or more hops with the passing of credentials (called Kerberos delegation).
  • Make replay attacks more difficult by including a timestamp, which NTLM doesn't have.
  • Off-load some of the work from domain controllers because of Kerberos' ticket architecture.

What is an SPN?

SPN is an abbreviation for Service Principal Name. The SPN is what a client checks out in order to authenticate a server. If what the client finds out about the server doesn't match the SPN it gets from Active Directory, then Kerberos authentication fails (which is what you would want because it means something is wrong). An SPN in Active Directory basically communicates the following information:

  • What the service is.
  • What the "server" where the service is running.
  • What account the service should be running under.

Are some SPNs built in?

Yes. There is a HOST SPN that every computer in Active Directory automatically has. It covers quite a few services. However, it does not cover the SQL Server database engine, which used MSSQLSvc, or SQL Server Analysis Services, MSOLAPSvc.3. It also does not cover SSRS if SSRS is running under an account other than Network Service or if you're accessing the SSRS site with something other than the physical name of the server.

Can you use a "common name" for an SPN?

Absolutely. For instance, it would not be unusual to see reports.contoso.com for a Reporting Services farm.

What are the most common tools for managing SPNs?

The two most common tools, at least the ones I use, are SETSPN and LDP. LDP moreso for finding duplicate SPNs (which would prevent Kerberos authentication from working properly).

Do you have to have special permissions to manage SPNs?

In general, yes. Basically you either have to appear as the computer account for a machine (meaning the service is running as System or Network Service and you can only manage that machine's SPNs) or you have to be a member of the Domain Admins group.

You said, "in general," so does that mean permissions can be assigned?

Yes, Active Directory has a very robust permission delegation structure. So the ability to manage particular SPNs could be accomplished by managing the account those SPNs belong to.


K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.


Posted by Steve Jones on 17 October 2011

Nice intro, looking forward to seeing more.

Posted by Jeff Langdon on 17 October 2011

Thanks Brian.  Always enjoy reading your posts.

Posted by mtassin on 20 October 2011

I do hope when this series is done, Kereberos won't be the mystery protocol to me that it is now.  All I know is I need it, and when I try to direct the NetAdmin team to get it setup right, we all typo it and get it wrong.  :(

Posted by andy russell on 20 October 2011

Great article!

Posted by DEK46656 on 20 October 2011

I'm with the others here: this is a great start, and I hope a complete series is done on this.

Posted by msmithson on 20 October 2011

Thanks, this was very interesting, looking forward to part 2

Leave a Comment

Please register or log in to leave a comment.