다음을 통해 공유

Installation of SQLserver2008 cluster fails on windows2008.(The group or resource is not in the correct state to perform the requested operation. (Exception from HRESULT: 0x8007139F)

  • 아티클

Installation of SQLserver2008 cluster might fail on windows2008 with error mentioned below

The cluster resource 'SQL Server' could not be brought online.
Error: The group or resource is not in the correct state to perform the requested operation. (Exception from HRESULT: 0x8007139F)

 

Root Cause

This problem occurs because of a new security feature named Loopback check functionality. By default, loopback check functionality is turned ON in Windows and the value of the DisableLoopbackCheck registry entry is set to 0 (zero).
https://support.microsoft.com/kb/957097/

With this feature being turned ON: windows do not allow NTLM authentication if we try to access server from Local server using a name which is not its Net-Bios name (or) IPAddress.

When SQLServer Agent is started, SQLAgent resource access the SQLServer using SQLVirtualServer name and hence we do not allow NTLM. So the SQLServer Agent would fail and the SQLServer Agent Resource creation would also fail.

SQLServer resource will fail to come Online because, IsAlive check will be done using NTLM Autentication i.e: Cluster service startup account resolves as NT AUTHORITY\ANONYMOUS LOGON when connecting to SQLServer for IsAlive check and the connection fails.

We will not get in to this issue if startup account of SQLServer has permissions to read and write SPN's.

 
After the installation fails you will see the SQLServer resource is created but not the SQLAgent resource.

There are three ways to resolve this issue.

Option 1

1. After the failure, create the SPN’s manually using SetSPN tool (or) Configure SQL Server service to create SPNs dynamically for the SQL Server instances (Refer KB: 811889)

Example for creating SPN’s manually:
SETSPN -A MSSQLSVC/VSName.XX.XX.EDU:1433 <STARTUP ACCOUNT NAME>
SETSPN -A MSSQLSVC/VSName.XX.XX.EDU <STARTUP ACCOUNT NAME>

2. Bring the SQL Server Resource online.

3. Create the SQL Server Agent resource type.

{
To add the sql server agent resource type execute the below command:

cluster restype "SQL Server Agent" /create /DLL:sqagtres.dll .Once done we got the
update that the Resource type 'SQL Server Agent' created.
}

4. Create SQL Server agent resource manually.

We need to make sure that the newly created SQL server Agent resource have the virtualservername and Instancename .

To add this property go to "failover cluster management" ==>SQLServer Agent Resource==>Properties==>properties
check for the two parameters (virtualservername and Instancename) and fill in the
details.

}

5. Change configuration reg_dword values of all components to 1 in below registry path

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLX.MSSQLSERVER\ConfigurationState

Option 2

1. Do a Complete uninstall of failed installation (or) Configure SQL Server service to create SPNs dynamically for the SQL Server instances (Refer KB: 811889) and move to Step 3.

2. Create the SPN's before we do the installation. --

 

Example:
SETSPN -A MSSQLSVC/VSName.XX.XX.EDU:1433 <STARTUP ACCOUNT NAME>
SETSPN -A MSSQLSVC/VSName.XX.XX.EDU <STARTUP ACCOUNT NAME>

Note:Beginning with SQL Server 2008, the SPN format is changed and new SPN format does not require a port number Refer: https://msdn.microsoft.com/en-us/library/ms191153.aspx

3. Then install the SQL Server on cluster

Option 3 (Recommended)

1. Disable the authentication loopback check by setting the DisableLoopbackCheck value in
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa registry subkey to 1.
To set the DisableLoopbackCheck registry entry to 1, follow below steps on all nodes of cluster.

      a. Click Start, click Run, type regedit, and then click OK.
b. Locate the following registry path:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
c. Right-click Lsa, select New, and then click DWORD Value.
d. Type DisableLoopbackCheck, and then press ENTER.
e. Right-click DisableLoopbackCheck, and then click Modify.
f. In the Value data box, type 1, and then click OK.

2. Restart the system.

3. Do complete uninstall and re-run the setup(or) Follow the steps from step2 in option 1.

 

 

Note:

1. We will encounter above error if we are installing the named instance of SQL Server and SQL Server browser is in stopped state.

2. If you have installed SQLServer 2012 (Denali) and uninstalled it on same cluster. You might encounter above issue. Refer below link for details.

https://mssqlwiki.com/2012/01/31/sql-server-resource-fails-to-come-online-is-alive-check-fails/

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group https://www.facebook.com/mssqlwiki#!/groups/454762937884205/

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Comments

  • Anonymous
    March 26, 2009
    PingBack from http://blog.a-foton.ru/index.php/2009/03/27/installation-of-sqlserver2008-cluster-fails-on-windows2008the-group-or-resource-is-not-in-the-correct-state-to-perform-the-requested-operation-exception-from-hresult-0x8007139f/

  • Anonymous
    September 10, 2009
    The comment has been removed

  • Anonymous
    October 06, 2009
    you are the best option 3 worked for me

  • Anonymous
    March 02, 2010
    You are amazing. I have fought this issue for a named instance install on my cluster for a week now. I knew what the issue was but had no idea how to fix it. Thanks for option #3.

  • Anonymous
    November 24, 2010
    thks a lot

  • Anonymous
    December 16, 2010
    Thank You, Option 3 has to be installed on both the nodes and failover worked!!!

  • Anonymous
    December 20, 2010
    For my install, tried option 3 first, but still got the error.  Had to do option 1 as well as option 3.  Thanks for the info!

  • Anonymous
    April 08, 2011
    Instructions were very helpful. Also be aware that software firewall settings can keep the setup from running with sucess. Make sure there are allows for the Instance IP addresses. I also found that I needed to turn off the firewall during initial setup and again when first moving the created resource to another node.

  • Anonymous
    May 28, 2012
    registry entry changes, server restarted, uninstall, new install but same error :-( like before

  • Anonymous
    May 28, 2012
    Hello Tom, ISAlive failure on SQL Cluster because of other reason could cause this. Did you ever install and uninstall denali instance on this server? refer mssqlwiki.com/.../sql-server-resource-fails-to-come-online-is-alive-check-fails Thanks Karthick

  • Anonymous
    August 31, 2012
    I have followed the same-successful on node1, but it threw a different error for me when i tried to add node. During add node setup rule is failing with MSCS Cluster verification error[Cluster report is successful with some warnings]. tried "Setup /SkipRules=Cluster_VerifyForErrors /Action=AddNode", But it did throw into some other error, where the password is not valid though after providing a valid one. can you please comment on this.

  • Anonymous
    August 31, 2012
    Hello Prashanth, check if you are hitting support.microsoft.com/.../828861 To get faster response join our Facebook group www.facebook.com/mssqlwiki    and post your questions. Thanks Karthick