SQL Server Failover Cluster Instance is taken offline when you change the password for the service account on the non-owner node

Source: Microsoft Support

RAPID PUBLISHING ARTICLES PROVIDE INFORMATION DIRECTLY FROM WITHIN THE MICROSOFT SUPPORT ORGANIZATION. THE INFORMATION CONTAINED HEREIN IS CREATED IN RESPONSE TO EMERGING OR UNIQUE TOPICS, OR IS INTENDED SUPPLEMENT OTHER KNOWLEDGE BASE INFORMATION.

Consider the following scenario:

• You have a SQL Server 2005 Failover instance that is running in a 2 node or more environment.
• You try to change the password for SQL Server virtual instance on one of the nodes that does not host the resources by using either SQL Server management studio or by using the WMI Provider for Configuration Management.

In this scenario, you would expect that the SQL Server instance continues to be online on the current node. However, you find that the instance actually goes offline.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

To work around this problem, always change the password on the owner node of the SQL Server.

Method 1

If you use SQL Server Configuration Manager, start it on the owner node and change the password.

Method 2

If you change the password programmatically by using the WMI Provider for Configuration Manager, find the owner node and connect to it when you get the SQLService Class object.

Refer to the following example script if you use this method:

Option Explicit

 

Dim oCluster, oResource, oClusProperty         ' Cluster Object, Resource Object, ClusProperty Object

Dim strCluster, strResource             ' Cluster name, Resource name

Dim strWQL, strInstance, strOwnerNode, strPassword    ' WMI Query, SQL Server Instance name, Owner node hostname, Password

Dim oSqlSvc      ' SqlService Class

 

strCluster = "ClusterName"              ' Cluster Network Name

strResource = "SQL Server"            ' Resource Name of SQL Server Resource

strInstance = "MSSQLSERVER"        ' Instance Name. MSSQL$InstanceName if named instances

strPassword = "password" ' New password

 

' Get the owner node of the resource

Set oCluster = CreateObject("MSCluster.Cluster")

oCluster.Open strCluster

For Each oResource In oCluster.Resources

    If oResource.Name = strResource Then

        If oResource.State = 2 Then    ' Online

            For Each oClusProperty in oResource.OwnerNode.CommonROProperties

                If oClusProperty.Name = "NodeName" Then

                    strOwnerNode = oClusProperty

                End If

                Exit For

            Next

        Else

            strOwnerNode = "."            ' Connect to the local node if the resource is offline

        End If

        Exit For

    End If

Next

 

' Create WMI Query string

strWQL = ""

strWQL = strWQL & "WINMGMTS:\\" & strOwnerNode & "\root\Microsoft\SqlServer\ComputerManagement"

strWQL = strWQL & ":SqlService.ServiceName='" & strInstance & "'"

strWQL = strWQL & ",SQLServiceType=1"

 

'Set the password.

Set oSqlSvc = GetObject(strWQL)

oSqlSvc.SetServiceAccountPassword "", strPassword

For more information, see the following:

• SQL Server Configuration Manager (http://msdn.microsoft.com/en-us/library/ms174212.aspx (http://msdn.microsoft.com/en-us/library/ms174212.aspx) )
• Failover Cluster Troubleshooting (http://msdn.microsoft.com/en-us/library/ms189117.aspx (http://msdn.microsoft.com/en-us/library/ms189117.aspx) )
• SQL Server Configuration Manager behavior in Clustered installation (http://blogs.msdn.com/jorgepc/archive/2007/12/28/sql-server-configuration-manager-odd-behavior-in-clustered-installation.aspx (http://blogs.msdn.com/jorgepc/archive/2007/12/28/sql-server-configuration-manager-odd-behavior-in-clustered-installation.aspx) )
• Change the SQL Server Service Account password without Server Restart (http://blogs.msdn.com/stuartpa/archive/2005/09/02/460363.aspx)

MICROSOFT AND/OR ITS SUPPLIERS MAKE NO REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY, RELIABILITY OR ACCURACY OF THE INFORMATION CONTAINED IN THE DOCUMENTS AND RELATED GRAPHICS PUBLISHED ON THIS WEBSITE (THE “MATERIALS”) FOR ANY PURPOSE. THE MATERIALS MAY INCLUDE TECHNICAL INACCURACIES OR TYPOGRAPHICAL ERRORS AND MAY BE REVISED AT ANY TIME WITHOUT NOTICE.

TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, MICROSOFT AND/OR ITS SUPPLIERS DISCLAIM AND EXCLUDE ALL REPRESENTATIONS, WARRANTIES, AND CONDITIONS WHETHER EXPRESS, IMPLIED OR STATUTORY, INCLUDING BUT NOT LIMITED TO REPRESENTATIONS, WARRANTIES, OR CONDITIONS OF TITLE, NON INFRINGEMENT, SATISFACTORY CONDITION OR QUALITY, MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE, WITH RESPECT TO THE MATERIALS.