Fix SQL Server Error 19471: Listener issue that came during Configuring SQL Server AlwaysOn Availability Group
I had an AOAG configuration between two replicas that i have deleted in order to perform some activities. I was configuring Always On Availability Group again on this database server. We followed all processes but Listener was failed to create during AOAG configuration and displayed Microsoft SQL Server error 19471.
I have also explained another aspect of this error in attached article. If your issue is not fixed using this article, you can have a look at this one where i got the same Listener Error 19471 because Kerberos Status was showing ‘The handle is invalid’.
Microsoft SQL Server Error 19471: Create failed for Availability Group Listener ‘Listener Name’.
AOAG Listener was failed to create during its configuration and displayed SQL Server error 19471. This error might have multiple reasons. Here i will fix this issue in step by step manner. The full error details are given below.
Create failed for Availability Group Listener ‘U********0’. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch.
The WSFC cluster could not bring the Network Name resource with DNS name ‘U*********0‘ 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)
The screenshot of this error is given below.
When i checked 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. You can see this in below screenshot.
You can see there is no listener showing in their respective folder of the AOAG configuration in above screenshot. I tried creating Listener name from “Add Listener…” wizard but failed with same error. Let’s move to next section where we will fix this error.
I did some research on this error code and found a solution suggested by Microsoft in attached MSDN article. I followed step by step process given in this article. High level approach to fix this solution using this method is given below.
- 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 failover cluster manager.
- Add listener to AOAG in SSMS.
You might have a question why we are removing CAP after adding, repairing and bring it online. Creating CAP and bringing it online is to just verify that everything is ok or not. If CAP will not come online then there might be some issue at certain level that needs to be fixed first before creating listener. Let’s go ahead with above given steps to fix this issue.
I launched failover cluster manager and clicked on Roles tab from left side pane. Here you can see your availability group role in right side pane. Right click on this role and select “Add Resources” then click on “Client Access Point“.
You will get below screen to enter listener details. I entered listener name into name section and listener IP in to Address section. Once i clicked at Next button to proceed. I got below error which says this listener is already in use in Active Directory.
As we know, listener name and IP should be unique and unused so here we need to remove this listener details from Active Directory. Contact your domain administrator and tell him to remove the entries for this listener name and IP.
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 you would be able to create the listener name. If you are still facing some issue, then we will follow remaining steps of the above suggested MSDN article. Read next section to follow remaining steps to fix this issue.
Failed to add Listener after clearing DNS entries and DNS Status showing The handle is invalid
If you are still facing same issue during adding listener or configuring AlwaysOn Availability Group then you can again ask your domain admin to relook into Active Directory because there is a possibility of some entries in AD for the listener name and IP during your failed attempts to create the listen name. If domain admin will not find any entry then we will proceed with the steps that Microsoft has suggested in above attached article.
We will create Client Access Point in failover cluster manager for Availability Group role as we were trying to create in above section. Right click on Availability Group role and select “Add Resources” then “Client Access Point” as shown in image no 3. You will get below screen to enter listener name and corresponding IP address in highlighted sections. Click on Next button to proceed post entering the listener details.
You will get below screen to proceed once you click on next button of above screen.
Click on Finish button to create the Client Access Point in failover cluster manager for this listener.
Once you will click on finish button, your Client Access Point for your listener will be created in the failover cluster manager as shown in below screen. 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 in above image. That is the reason 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 is failed to come online. Now we will check the properties of the client access point (CAP).
Right click on Listener Name and choose Properties option. You will get below screen where you 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.
When i checked the cluster events, i saw below error details that are also very much same.
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 as per below image.
Once you 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. You can repeat the repair exercise if your CAP is not coming online. If you are still facing issue then you can again contact to domain admin to look into Active Directory server.
Now you can see Client Access Point for the listener is showing online now. if you go to the property of this Listener CAP, you can see NetBIOS, DNS and Kerberos status is showing OK.
Close above property window. Now DNS issue is resolved that was main culprit during listener creation so 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 you click on remove button below window will come to proceed. Click on ok to proceed.
Once this CAP will be removed then connect to primary replica of the AOAG configuration. Expand the AlwaysOn High Availability folder and then Availability Group Listeners folder. You 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.
You will get below image (New Availability Group Listener Wizard) to enter the listener details. Enter the Listener DNS Name then port followed by choosing Network Mode. Click on Add button to enter Listener IP Address. Once you 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 you the error on the same wizard.
As we have created Availability Group Listener for this AOAG configuration so you can go ahead and verify this in SSMS by expanding Availability Group Listeners folder. You can see same listener name is showing under Availability Group Listeners folder on both primary as well as secondary replica in below image.
- Learn what is AlwaysOn Availability Group.
- Fix Listener Error 19471 due to Kerberos Status”The handle is invalid”
- How to fix AlwaysOn Availability group Automatic Failover Issue.
- Fix: Secondary Replica is showing in Resolving state and not becoming Primary
- How to fix error 15141: The server principal owns one or more endpoint(s) and cannot be dropped
- How to Fix SQL Error 1005: A Comprehensive Guide - April 9, 2023
- How to Fix SQL Server Error 207 – Invalid Column Name - April 9, 2023
- How to Fix SQL Error 1045: Resolving Access Denied Issues - April 8, 2023