Wednesday, July 4, 2012

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


08-01-2012 Update:
Jonathan Almquist has posted an excellent posting about the technical details of this faulty monitor. To be found here

07-04-2012 Update
:
Even though the resolution mentioned in this posting (first posted on the 3rd of April 2012) worked for my test environment, it turns out this Alert is a bug as well. Tabish Ansari
already pointed it to me, but back then I didn’t share his opinion about this Alert, being a bug.

Case is this Monitor ‘Service Principal Name Configuration Status’ is targeted against SQL Server 2012 DB Engines. However, this Monitor runs against SQL 2008 DB Engine class as well. On the TechNet Forums this issue is explained by SME-45: ‘…it looks for the right SPNs but is unable to detect that a SQL-Server which runs as NT AUTHORITY\NETWORKSERVICE is the right one for SPN FQDN.So it sees 2 different names and complains. When SQL runs as Domain Account this logic runs perfect but for Local System and Networkservice the Script just makes no sense…’

Therefore it’s best to disable this Monitor for now since this Monitor raises false-positives.

Thanks to Tabish Ansari and the TechNet Forums.


Bumped into this issue in my test environment which I am building for the new series of blog postings about migrating from SCOM R2 CU#5 to OM12. The SQL server threw this Warning Alert in the SCOM R2 Console: ‘SQL Server cannot authenticate using Kerberos because the Service Principal Name (SPN) is missing, misplaced, or duplicated.’:
image

Resolution
So the SPN for the SQL server was missing. Time for some action! However, the command as stated in the same Alert under the tab Product Knowledge wasn’t going to work:
image

In my days being a system engineer I have fought a battle or two with SPNs and those syntaxes were a bit longer. Time to search for the correct syntax.

Soon I found it on this website, all about configuring SPNs for SQL Server Site Database Servers:
image

In my case it translated to:
setspn -A MSSQLSvc/SCOM-DB01.scom2om12.local:1433 scom2om12\scomsql

I ran this line from an elevated cmd-prompt on the SQL server with an account with domain admin permissions. I ran for a minute and then I got the message all was fine. And indeed, the Alert doesn’t come back anymore.

Case closed.

8 comments:

Sandra said...

Hi Marnix,

Tnx for the artical, I have this problem to after updating my SQL MP's. Unfortunately this did not solve my problem.

Kind Regards,
Sandra

Tabish Ansari said...

This error is simply a BUG.

Nothing to worry about,just ignore it or disable the monitor.

Marnix Wolf said...

Hi Tabish.

You're right. Thanks for pointing it out to me. Based on your information I have updated the posting.

Cheers,
Marnix

Dominique said...

Hello,

My question will be why is it happening only on one Cluster Server? Is it only the one hostiong the OperationManager Database which is in trouble?
I have 15 Clusters and more than 200 SQL Servers otherwise...

Thanks,
Dom

Dominique said...

Hello,

Is it the SETSPN or the disabling which is the best option so far? I have only one SQL Cluster for now...

Thnaks,
Dom

Marnix Wolf said...

Disabling

Jonathan said...

From what I can tell, there are two issues with this monitor. First, if you are using a service account that exists in another domain, there is a problem in the script in which it queries LDAP instead of GC. Second, there is a problem in the condition detection in the monitor type. Both healthy and unhealthy CD's evaluate to TRUE, so this monitor will never produce a positive result for healthy.

Pavithra S said...

Wanted to know if we are facing this issue in SQL 2014 DB engine monitoring also? We have many servers facing same issue in SCOM environment.