SQL 2008, Kerberos and SPNs

Last weekend I was attending (or better to say I was just passing by as it was short visit) conference organized by some .NET developers and SQL users groups called Community to Community 2008. If You ask me this was really cool event if You think that it was organized only by people from communities and free to attend for all. Hope we will get more of these in Poland soon.

But this isn't my main topic here. I had time to attend only one technical session which was delivered by Marcin Szeliga – Polish SQL MVP. He was talking about SQL 2008 and changes in this product related to security. I'm not really SQL type of guy but I managed to understand most of it πŸ™‚ but he really got my attention when he started to talk about changes in authentication and Kerberos support.

What have attracted my attention was statement that "SQL 2008 doesn't require Service Principal Names to make Kerberos to work anymore, instead of this You can specify SPN in connection string". Hmmm … I've confirmed what he had said with him after the session and I've said that I will check it … so here I am with conclusions.

BTW – are SPNs really such problem?  I know that it tends to be a problem when it comes to application deployment \ configuration. This is first thing which is likely to be forgotten or which will cause a problem to be configured. It often requires to get to domain admin who has rights to write to servicePrincipalName – I don't know why they are not delegating this for service account which is being used during deployment.

OK – so getting back on track. So during this session I've heard that SPNs are not necessary to be registered and can be specified in connection string. I dug a bit and as often is showed that as often there is a piece of truth in this statement.  SQL 2008 has some improvements in Kerberos support and when it comes to SPNs:

  • Yes, You can specify SPN as a part of connection string and this is optional
  • Yes, You still require SPNs to be registered for SQL service if:
    • You will not provide SPN in connection string
    • You will provide SPN in MSSQLSVC/FQDN:<port|instancename>
  • In case You will provide SPN as a part of connection string service itself will not try to construct SPN and will use the one provided
    • so make sure that You have double checked Your configuration settings πŸ™‚
  • You don't have to register SPNs for SQL service as long as you will always provide SPN as a part of connection string and You will use one of following formats for it:
    • serviceacount@domain – which basically is UPN for service account
    • domain\serviceaccount – which points to specific logon name in domain
    • machine$@domain – I haven't checked SQL documentation but this probably is valid only when SQL works on one of system built-in accounts
    • host\FQDN – with same note as above.

So this fits better to what I know about this stuff :).

So good to know about such changes. I'm afraid that it will cause a bit of mess in deployments of applications as right now nobody will talk with directory admins when application with get deployed and then this admin will be the one who will have to solve authentication related problems … which might be incorrect SPN in connection string.

So just FYI …

3 thoughts on “SQL 2008, Kerberos and SPNs”

  1. Thanks for the post! Kerberos configration is something that has become more of a pain point since we've introduced SharePoint into our ILM projects so I'm always trying to stay on top of this. The obvious issue here is that in order for this to be of use the applications (like SharePoint) will need to be easily configurable to work in this mode (which it is not today and barely functions with Kerberos short of an act of God but I digress…). I'm guessing that for coexistance sake you'll still see SPN's registered using the "traditional method" until all applications catch up. Personally, I'm for anything that makes it easier to consume Kerberos delegation without jumping through as many hoops.

  2. Yeah … Kerberos configuration is making a lot of troubles in app deployments but to be honest … I don't know why. In most cases these problems are caused by some developer which forgot to set something or "person" who forgot to set SPN. I saw Your session during last DEC (BTW great information how to make ILM projects a bit more rich from UI perspective) and I was wondering why it causes this amount of troubles with SPS to get it running?

  3. Truthfully, as much as I understand about Kerberos I don't deal with it everyday and when I do, on occasion need to set the SPN's for an application I look up my notes to make sure I'm doing it correctly. However, for most AD or Application Admins, they either don't understand Kerberos delegation or work with it so infrequently that they also have to resort to looking it up when needed. There are a number of bad or misleading articles or posts out there on Kerberos setup that complicates this problem further but honestly it's still way too difficult to configure to be immune to error and with Kerberos delegation all it takes is one thing to be out of line and you're troubleshooting for hours. Thanks for the nod on the DEC presentation – I'm glad you got something valuable out of it; next time say hi!

Comments are closed.