Sql server error 19471

Often while configuring Always On Availability Group on the database server, it displays Microsoft SQL Server error 19471.

As a part of our Server Management services, we help to fix similar SQL-related errors regularly.

Today, let us see how our Support Engineers fix this error.

What is “SQL Server Error 19471”?

Recently we received a support request that the customer was receiving an error while configuring Always On Availability Group again on the database server. We noticed that the listener failed to create during AOAG configuration and it displayed Microsoft SQL Server error 19471.

On checking the AlwaysOn High Availability folder on any of the replica, everything was working fine. AOAG was configured successfully and working fine, but listener was not configured due to above error.

There is no listener showing in their respective folder of the AOAG configuration. When tried creating Listener name from “Add Listener…” wizard, it failed with same error.
 

How to fix “SQL Server Error 19471”?

To solve this error, we can follow the steps given below to add the listener.

• Create a Client Access Point (CAP) for the AOAG listener in Failover Cluster Manager.
• Bring CAP Online.
• Repair the CAP, if it is not coming ONLINE.
• Once CAP comes online, remove it from the failover cluster manager.
• Add a listener to AOAG in SQL Server Management Studio (SSMS).

We have to remove CAP after adding, repairing, and bring it online. Creating CAP and bringing it online is to just verify that everything is fine or not. If CAP will not come online, then there might be some issue at a certain level that needs to be fixed first before creating a listener.

Launch failover cluster manager and click on the Roles tab from the left side pane. Here we can see our availability group role in the right-side pane. Right-click on this role and select “Add Resources”. Then click on “Client Access Point“.

We will get a screen to enter listener details. Enter listener name into name section and listener IP into Address section. On clicking the Next button to proceed, it resulted in the error given below:

'Listener_name' listener is already in use in Active Directory.

The listener name and IP address should be unique and unused. So we need to remove these listener details from Active Directory.

Once listener details will be cleared from Active Directory, go ahead and try to add listener name to the existing AlwaysOn Availability Group. This time we will be able to create the listener name.
 

Failed to add Listener after clearing DNS entries and DNS Status showing “The handle is invalid”

If we are still facing the same issue during adding listener or configuring AlwaysOn Availability Group, then there is a possibility of some entries in AD for the listener name and IP during our failed attempts to create the listen to name. If there is no entry, then follow the steps given below:

We will create a Client Access Point in the failover cluster manager for the Availability Group role. Right-click on Availability Group role, select “Add Resources” and then “Client Access Point”. We will get a screen to enter the listener name and corresponding IP address. Click on the Next button to proceed post entering the listener details.

We will get a screen to proceed, once we click on the next button.

Click on Finish button to create the Client Access Point in failover cluster manager for this listener.

Once we will click on finish button, Client Access Point for our listener will be created in the failover cluster manager. The Availability Group role was running fine. But after adding Client Access Point, it has changed into Partial Running mode.

We can see CAP is showing as offline, due to which Availability Group role has changed into Partial Running mode. Now we will bring it online manually. Right click on Listener CAP and click on “Bring Online” option.

We can see Listener IP has come online whereas listener name failed to come online. Let us check the properties of the client access point (CAP).

Right click on Listener Name and choose Properties option. We will get a screen where we can see NetBIOS, DNS and Kerberos status. NetBIOS and Kerberos status is showing OK. But DNS status is not OK and showing “The handle is invalid.” This is the issue that needs to be fixed to create listener.
 

“The handle is invalid” Error

When we checked the cluster events, we saw below error details:

Cluster network name resource ‘Cluster Name’ failed registration of one or more associated DNS name(s) for the following reason:
The handle is invalid.

Ensure that the network adapters associated with dependent IP address resources are configured with at least one accessible DNS server.

To fix this DNS issue (The handle is invalid), we will repair this CAP. Right click on Listener Name showing in failover cluster manager, click on “More Actions” and then click on Repair.

Once we will click on Repair, it will come online and then go in offline. Now right click on Listener name again and click on Bring Online button. This time Listener Name will come online if CAP has been repaired successfully. We can repeat the repair, if CAP is not coming online. If we are still facing issue, then we can again contact to domain admin to look into Active Directory server.

Now we can see Client Access Point for the listener is showing online. If we go to the property of this Listener CAP, we can see NetBIOS, DNS and Kerberos status is showing OK.

Close above property window. The next step is to remove this Client Access Point for this Listener and create the listener in SSMS that will create Listener client access point (CAP) automatically in the failover cluster manager.

Once we click on remove button, another window will come to proceed. Click on OK to proceed.
 

Adding Listener

Once this CAP is removed, then connect to primary replica of the AOAG configuration. Expand the AlwaysOn High Availability folder and then Availability Group Listeners folder. We will not be able to see anything as there is no listener as of now. Right click on Availability Group Listeners folder and click on “Add Listener…” option.

We will get New Availability Group Listener Wizard to enter the listener details. Enter the Listener DNS Name then followed port by choosing Network Mode. Click on Add button to enter Listener IP Address. Once we are done with all details, click on OK button to apply this changes. Add Listener wizard will disappear if listener will be created successfully. Otherwise, it will give the error on the same wizard.

As we have created Availability Group Listener for this AOAG configuration, we can go ahead and verify this in SSMS by expanding Availability Group Listeners folder. We can see the same listener name is showing under Availability Group Listeners folder on both primary as well as secondary replica.

 
[Need any further assistance to fix SQL errors? – We’re available 24*7]

Conclusion

In short, “SQL Server Error 19471” can trigger while configuring Always On Availability Group again on the database server. Today, we saw how our Support Engineers fix this error.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Problem

You’re attempting to create a Availability Group Listener after successfully creating a SQL Server 2019 Availability Group but receive the following error:

TITLE: Microsoft SQL Server Management Studio

——————————

Create failed for Availability Group Listener ‘PRD_TX_Listener’. (Microsoft.SqlServer.Smo)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.44091.28+(SMO-master-A)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+AvailabilityGroupListener&LinkId=20476

——————————

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

The WSFC cluster could not bring the Network Name resource with DNS name ‘PRD_TX_Listener’ online. The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible. Use a different DNS name to resolve name conflicts, or check the WSFC cluster log for more information.

The attempt to create the network name and IP address for the listener failed. If this is a WSFC availability group, the WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. Otherwise, contact your primary support provider. (Microsoft SQL Server, Error: 19471)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.00.2000&EvtSrc=MSSQLServer&EvtID=19471&LinkId=20476

——————————

BUTTONS:

OK

——————————

===================================

Create failed for Availability Group Listener ‘PRD_TX_Listener’. (Microsoft.SqlServer.Smo)

——————————

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.44091.28+(SMO-master-A)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+AvailabilityGroupListener&LinkId=20476

——————————

Program Location:

at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()

at Microsoft.SqlServer.Management.SqlManagerUI.AGListenerConfigurationNewModeViewData.DoWorkCore(AGListenerController controller)

at Microsoft.SqlServer.Management.SqlManagerUI.AGListenerConfigurationControl.OnRunNow(Object sender)

at Microsoft.SqlServer.Management.SqlMgmt.PanelExecutionHandler.Run(RunType runType, Object sender)

at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType)

at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)

at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)

at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType, Boolean retry)

at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries, Boolean retry)

at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext, Boolean executeForAlter)

at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplFinish(StringCollection createQuery, ScriptingPreferences sp)

at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()

===================================

The WSFC cluster could not bring the Network Name resource with DNS name ‘PRD_TX_Listener’ online. The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible. Use a different DNS name to resolve name conflicts, or check the WSFC cluster log for more information.

The attempt to create the network name and IP address for the listener failed. If this is a WSFC availability group, the WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. Otherwise, contact your primary support provider. (.Net SqlClient Data Provider)

——————————

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.00.2000&EvtSrc=MSSQLServer&EvtID=19471&LinkId=20476

——————————

Server Name: BMA-PROD-SQL1

Error Number: 19471

Severity: 16

State: 0

Line Number: 1

 ——————————

Program Location:

at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)

Solution

This error is typically displayed if the Cluster Name Object (CNO) in Active Directory does not have permissions to create and read the listener computer object. The CNO name can be found in the Failover Cluster Manager:

To correct the issue, simply navigate to the OU containing CNO, which would be where it will attempt to create the listener computer object, right click on the OU and select Delegate Control… to launch the Delegation of Control Wizard:

Add the CNO computer object:

Select Create a custom task to delegate and click Next:

Select This folder, existing objects in this folder, and creation of new objects in this folder then click Next:

Select General and Creation/deletion of specific child objects, then locate the following and select the following:

• Read all properties
• Create Computer objects
• Delete Computer objects

With the CNO computer object granted the respective permissions, the listener should now create successfully:

Hi.

Now I am facing issues for creating AO listener in this setup. CNO pre-staged in AD cluster name object created successfully and up & running.

Error Messages:

The WSFC cluster could not bring the Network Name resource with DNS name 'ClusterHMSCLr' online. The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible. Use a different DNS name to resolve name conflicts, or check the WSFC cluster log for more information.

The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. (Microsoft SQL Server, Error: 19471)

CNO — Cluster Name Object by used for domain account SQLAlways.admin having full control in AD.

Cluster name object created successfully up and running already.

Pre-staged listener object created in AD- domain account SQLAlways.admin having full control in AD and even though Computer Name Object creation permission assigned. but not hope still that same Error 19471 occurred while creating AO listener. Please help me what else need to be checked.

Event log errors 1: while creating AO listener group

Cluster network name resource 'HMS-HADR_CLUSTERHMSL' failed to create its associated computer object in domain 'in.abc.com' during: Resource online.

The text for the associated error code is: Access is denied.

Please work with your domain administrator to ensure that:

- The cluster identity 'CLUSTERHMSC$' has Create Computer Objects permissions. By default all computer objects are created in the same container as the cluster identity CLUSTERHMSC$'.

- The quota for computer objects has not been reached.

- If there is an existing computer object, verify the Cluster Identity 'CLUSTERHMSC$' has 'Full Control' permission to that computer object using the Active Directory Users and Computers tool.

Event log errors 2:

Cluster resource 'HMS-HADR_CLUSTERHMSL' of type 'Network Name' in clustered role 'HMS-HADR' failed.

Based on the failure policies for the resource and role, the cluster service may try to bring the resource online on this node or move the group to another node of the cluster and then restart it. Check the resource and group state using Failover Cluster Manager or the Get-ClusterResource Windows PowerShell cmdlet.

Thanks