January 4, 2011 at 5:44 am
Hello,
I had a SQL 2008 R2 cluster ticking along nicely, but then AD corrupted. The network guy recreated AD and all the nodes had to be taken off the domain, then added to the new one (the user and computer accounts were all recreated and realigned to account for the different SIDs). Other stand alone SQL servers managed to come back online after specifying new service accounts, but the cluster is giving me some trouble.
The cluster service has started and all the disks are online, when I run the cluster validation tests, it fails with the validate Server Principle Name test, stating that 'there is no such object on the server'. I have read up on SPN's but I at a loss as to how I can change things in order for the SPN test to be successfull.
Has anybody done, or been through this before that could help me out, please?
Regards,
D.
January 6, 2011 at 3:20 pm
I haven't had to do what you have but i did have problems registering a spn.
I am a domain admin so i added the sql server service account to the domain admins and then restarted the server - it logged in fine, registered the spn without an issue.
I then removed the sevrice account from the Domain Admins group and everything worked fine.
That's about the closest I can come to that, it may help.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 8, 2011 at 1:36 pm
Duran (1/4/2011)
Hello,I had a SQL 2008 R2 cluster ticking along nicely, but then AD corrupted. The network guy recreated AD and all the nodes had to be taken off the domain, then added to the new one (the user and computer accounts were all recreated and realigned to account for the different SIDs). Other stand alone SQL servers managed to come back online after specifying new service accounts, but the cluster is giving me some trouble.
The cluster service has started and all the disks are online, when I run the cluster validation tests, it fails with the validate Server Principle Name test, stating that 'there is no such object on the server'. I have read up on SPN's but I at a loss as to how I can change things in order for the SPN test to be successfull.
Has anybody done, or been through this before that could help me out, please?
Regards,
D.
Running the SQL Server service as a domain admin or local system will automatically allow the service to dynamically register\unregister SPN's. The SQL Server service is supposed to register or unregister SPNs when the service starts or stops. There are granular permissions you can grant in AD using ADSIEDIT to allow the service account to manage it's SPN's without being a domain administrator. A couple of questions;
What tool did you use to change the SQL Server service account?
Are there any messages in the event log?
Is this a Windows 2003 or 2008 domain?
What does the following produce when executed from the command prompt (substitute SQLSRVUSER for the name of the service account)?
SETSPN –L SQLSRVUSER
To set the AD permissions to allow the sql server service account to manage SPNs perform the following.
➡ Have the domain admin open the AD catalog via adsiedit and drill down to the user object key.
➡ Right click the user key and select properties.
➡ On the properties window click the security tab and then click the advanced button.
➡ Scroll down the list and find the permission "Read\write personal information" for SELF. Select this permission and click edit.
➡ In the permissions edit window scroll down and click allow for the following items
Read servicePrincipalName
Write servicePrincipalName
➡ Click OK, then apply and OK on the next and close the last dialog
Your service account should now have the required permissions to register SPNs dynamically.
Shout back if you're still stuck 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 10, 2011 at 4:59 am
Hi Perry,
Thanks for getting back,
What tool did you use to change the SQL Server service account?
SQL Configuration manager, not services.msc
Are there any messages in the event log?
Lots, the main one says that the <clustername> object does not exists on the server. When I also look at the properties of the cluster, the kerbeos status says there is no such object on the server. I am also having a problem getting the clustered DTC to start. If I right click on My Computer (which indicates its down) in Component servers and choose the MSDTC tab, there is nothing in the cluster default coordinator drop down box and when I click into it I get a dialogue box appear that states 'There are no more endpoints available from the endpoint mapper'. unfortunatly, I have never looked here before, but I am guessing the cluster account should be in there.
Is this a Windows 2003 or 2008 domain?
2008 R2 Native.
What does the following produce when executed from the command prompt (substitute SQLSRVUSER for the name of the service account)?
Registered serviceprinciplename for CV='serviceaccount',OU='Service Accounts',OU='Domain', DC=Local, DC='DomainName',DC=UAT, DC=com:
I followed your directions and all but 3 of the resource groups have gone to an hourglass (including the DTC resource) and its stayed there way for the last 50 minutes. Previously there where all showing as down. I cant do much with it while this goes on. I'll try to add more error log details in while. One of the nodes (currently the owner of all the resource groups) also seems to be failing to logon with a bad username or password error.
Thanks for your help.
Regards,
D.
January 10, 2011 at 1:31 pm
ensure all nodes are online and on the node currently owning the resources use sql server config manager to change the username and password for the sql server services, then try and restart them.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 11, 2011 at 4:41 am
Hi Perry,
If I try to change the account in SQL configuration manager, I can choose the account no problem, when I click apply and/or OK I get the following error as a dialogue box...
WMI Provider Error [call to wmi provider returned error code: 0x800742a2]
I have looked this up on the web but so far nothing works. When I try to start the SQL service, I get the following error in the event log
Initerrlog: Could not open error log file '<Drive:\log destination\Errorlog'. Operating system error=3(Failed to retrieve text for this error. Reason: 15105).
The seems to be 3 errors for everything that is wrong with this, the nodes fail to logon with bad username and password, the DTS does not start with errors stating 'Unable to obtain the primary cluster name identity token' DTS also show errors with 'There are no more endpoints available from the endpoint manager'. I am now wondering if this cluster is so far gone its unfixable and I'd be better off destroying it all and rebuilding it. Also, the validate cluster test states of the cluster itself 'There is no such object on the server'.
Regards,
D.
January 11, 2011 at 6:51 am
It might just be quicker to scrap the cluster and reinstall it (make sure you don't erase all your db files).
January 11, 2011 at 1:35 pm
yeah I think I'd have done a rebuild by now, once you spend more than a couple of days it's better to bite the bullit and rebuild. You also get absolute peace of mind that something else isn't going to catch you out further down the line. Charge the time to the person who caused the problem in the first place.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 11, 2011 at 1:44 pm
The error 3 leads me to believe that at some point the service account has been modified via services.msc
Check the ms kb article for setting manual permissions, google will help here.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 12, 2011 at 1:38 am
Hi Perry, which service account are you refering to? The sql service account? I have multiple named instances, only one was changed via services (not by me), the others where all changed via SQL configuration manager, I have a different domain user account for every SQL service. The cluster service is running under local system on both nodes.
Thank you all for taking the time to reply.
Regards,
D
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply