Wednesday, 5 August 2015

Kerberos Configuration Manager for SQL Server v3

By David Postlethwaite

I’m speaking about Kerberos for the SQL DBA at SQL Saturdays in Oslo, Gothenburg and Utrecht in September 2015.
Kerberos is one of those things that can drive you nuts and it’s easy to get it wrong.


If you are not aware Microsoft have produced a program to help SQL Server DBAs called Kerberos Configuration Manager (KCM). This is an excellent tool to help you to configure and check your SPNs for a SQL Server instance.


During my preparations for SQL Saturday I have come across a problem with it. I’ve been using KCM version 2 for the last year but this didn’t support SQL 2014 instances so I upgraded to version 3. This is much improved - as well as recognising SQL 2014 it now supports Analysis Services as well.
However I hit a problem with v3. It works fine on a Windows server with SQL 2014 installed, here it will recognise any older instance installed on that server as well but if you have a server with only an older version of SQL (in my case just SQL 2012) then it won’t recognise that instance and displays an error saying no SQL Server found.
Looking in the error log KCM reads the WMI to find the SQL information. With v3 it looks in root\Microsoft\SqlServer\ComputerManagement12 . This will only exist if SQL 2014 has been installed and this explains why it couldn’t find my SQL 2012 instance.


I uninstalled v3 and reinstalled v2 and that found my SQL 2012 server without issue.


Looking at the logs v2 looks in root\Microsoft\SqlServer\ComputerManagement11. I suspect that if you only have a SQL 2008 or 2005  instance it won’t work for you either.

I’d be interested to hear from anyone who has also encountered this problem and if they have found a fix.