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 …