KB2829783 - Troubleshooting AlwaysOn availability group listener creation in SQL Server 2012

This issue occurs because the Windows cluster cannot create and bring online the client access point that is a clustered resource. When you encounter this issue, you can collect the relevant information from the Windows cluster log and the Windows System event log in order to find the cause. To do this, follow these steps.

Step 1: Note the exact time that the problem occurredPerform whatever steps are required to reproduce the problem, and note the time that this problem occurs.

Step 2: Review the data in the Windows cluster log (Cluster.log)Search the Cluster.log file for the network name that you designated for the group listener.

Notes

• The Cluster.log file entries are in logged in Coordinated Universal Time (UTC).

• To generate the Windows cluster log by using an administrative command prompt in Windows Server 2008 R2 or a later version of Windows Server, run the following command:
  

  Cluster log /g

  
  

• To generate the Windows cluster log by using Windows PowerShell, run the following cmdlet in an elevated PowerShell window:
  

  Get-ClusterLog

  
  

• By default, the log file is created in %WINDIR%\cluster\reports.

Step 3: Review the data from the Windows System event logReview the Windows System event log for entries that are related to the listener creation attempt by using the time that you noted in step 1.

Sometimes, SQL Server cannot report specific information about why you cannot create a group listener because of domain policy. For example, domain policy enables a domain user to create ten computer accounts by default. When you try to create a group listener that would be your eleventh computer account, SQL Server can only report the generic error messages "Msg 19471" and "Msg 19476" that are discussed in "More Information" section.

To find the cause for why you cannot create a group listener, review the Windows System event log and the Cluster.log file.

• To review the Windows System event log, follow these steps:
  

  1. Click Start, point to Programs, point to Administrative Tools, and then click Event Viewer.
     

  2. In the console tree, expand Windows Logs, and then click System.

  3. In the details pane, scroll through the most recent events and locate event 1194. Under the General tab, several causes are proposed for the cause of the failure.
     

  4. Click the Details tab. In the Friendly View under the EventData section, you can find a more specific error message that resembles the following:
     

     You have exceeded the maximum number of computer accounts that you are allowed to create in this domain.

     

• To review the entries in the Cluster.log file, follow these steps:

  1. Click Start, point to All Programs, point to Windows PowerShell V2, right-click Windows PowerShell ISE, and then click Run as administrator.
     

  2. At the command prompt, run the following cmdlet in order to generate a cluster log:
     

     Get-ClusterLog

     
     

  3. Open the Cluster.log file in Notepad.

  4. Click Edit, and then click Find in Notepad, and search for the string "Failed to create Computer Object <DNS Name>." Review the results, and you can find messages that resemble the following:
     
     

     00000d24.00000dc8::<Time stamp> INFO [RES] Network Name: [<Network name>] NetUserAdd object <DNS name> on Sub domain name: \\Domain name, result: 8557
     

     
     
     

     00000d24.00000dc8::<Time stamp> ERR [RES] Network Name: [<Network name>] Failed to create Computer Object <DNS name> in the Active Directory, error 8557
     

  5. To determine what the error indicates, type Net helpmsg 8557 at the command prompt, and then press Enter.
     

ConclusionYou can now conclude that the cause of the problem is attributed to an Active Directory policy that is in effect in your environment. In this specific case, authenticated users in a domain that are assigned the "Add workstations to a domain" user permission and can create up to ten computer accounts in the domain. You receive the error message because you have exceeded this limit.

Sometimes, SQL Server cannot report specific information about why you cannot create a group listener because of insufficient permissions in Active Directory. For example, the cluster name account requires the "Create Computer objects" permission when you create a clustered service or an application. When the cluster name account does not have this permission, SQL Server cannot create the availability group listener. When you try to create a group listener with insufficient permissions, SQL Server can only report the generic error messages "Msg 19471" and "Msg 19476" that are discussed in "More Information" section.

To find the cause of this problem, review the Windows System event log and the Cluster.log file.

• Review the Windows System event log. To do this, follow these steps:
  
  

  1. Click Start, point to Programs, point to Administrative Tools, and then click Event Viewer.
     

  2. In the console tree, expand Windows Logs, and then click the System log that contains the event that you want to view.

  3. Under the Details tab, scroll through the most recent events and locate event 1194. Under the General tab, several reasons are proposed for the cause of the failure, such as "The cluster identity '<Domain user>' has Create Computer Objects permissions."
     

  4. Click the Details tab. In the Friendly View under the EventData section, you can find a more specific error message that resembles the following:
     

     The specified directory service attribute or value does not exist.

     

• Review the entries in Cluster.log file. To do this, follow these steps:
  

  1. Click Start, point to All Programs, point to Windows PowerShell V2, right-click Windows PowerShell ISE, and then click Run as administrator.
     

  2. At the command prompt, run the following cmdlet in order to generate a cluster log:
     

     Get-ClusterLog

     

  3. Open the Cluster.log file in Notepad.

  4. Click Edit, and then click Find in Notepad, search for the string "Failed to create Computer Object <DNS name>." Review the results, and you can find a message that resembles the following:
     
     

     00000d24.000005f8::<Time stamp> ERR [RES] Network Name: [<Network name>] Failed to create Computer Object <DSN name> in the Active Directory, error 8202
     

     
     

  5. To determine what the error indicates, type Net helpmsg 8202 at the command prompt, and then press Enter.
     

ConclusionYou can now conclude that the cluster name account does not have the "Create Computer object" permission in the computer container in Active Directory Users and Computers.

For more information about how to obtain sufficient permissions in Active Directory when you create availability group listeners in SQL Server, go to the following Microsoft TechNet website:

How to configure Active Directory computer accounts in a failover clusterBefore you create a Windows failover cluster, you have to check whether the domain user account that is running the Create Cluster Wizard has the "Create Computer Objects"permission.For more information, you can view the "Steps for configuring the account for the person who installs the cluster" section of the previously mentioned guide.

If your organization security policy does not let you grant this permission to a specific user account, you can ask your domain administrators to grant the "Create Computer Objects" permission to the cluster name account after you have created the Windows failover cluster. For more information, you can view the "Steps for prestaging the cluster name account" section of the previously mentioned guide.

When you run the High Availability Wizard, it is usually simpler to let the account be created and configured automatically if you have not prestaged the computer account for a clustered service, an application, or a listener virtual network name. However, if you have to prestage accounts because of requirements in your organization, then follow the steps in the "Steps for configuring the account for a clustered service or application" section of the previously mentioned guide.

Notes

• To use the procedure that is described in this section, you must have the domain administrator or account operator permission.

• If you set up an availability group listener across multiple subnets, you must obtain static IP addresses from each subnet that your replica belongs to. Usually, you have to talk to the network administrators to do this.