Hi,
I am trying to setup the sql server alwayson availability group. But I am getting the following error. Please help.
TITLE: Microsoft SQL Server Management Studio
——————————
Create failed for Availability Group Listener ‘aglisten1’. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.5000.0+((SQL14_PCU_main).160617-1804)&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 Windows Server Failover Clustering (WSFC) resource control API returned error code 5057. The WSFC service may not be running or may not be accessible in its current state, or the specified arguments are invalid. For information about this error
code, see «System Error Codes» in the Windows Development documentation.
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.
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: 41009)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.5000&EvtSrc=MSSQLServer&EvtID=41009&LinkId=20476
——————————
BUTTONS:
OK
——————————
Hi Don,
Thanks for the reply. Since posting I was able to resolve my issue by doing the following:
I deleted the AG group, disabled AG High Availability in each of my SQL nodes (required if you are going to destroy the cluster), and then destroyed the cluster.
I cleaned-up AD and DNS (forward and reverse lookups) for all computer, cluster, AG nodes and listeners in my network and ensured only the computer machine names were listed with their server IPs.
I checked all the NICs on all the servers involved in my AG to ensure there were no duplicate IPs. Found that the AG listener IP somehow got added to the NIC of my primary node, possibly from a bad cluster creation/destroy (I’d done it so many times
I lost count!) It was actually that reason why the IP kept getting added to the DNS as the primary node’s computer name, and not an error being caused by the AG wizard.
Having done that, I tried again and still got a failure, this time with a slightly different mesage:
The WSFC cluster could not bring the Network Name resource with DNS name ‘MyAGListener’ 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)
Well, I checked the WSFC cluster resource CNO in Active Directory, and the SELF account definitely had «Full Control», and I even explicitly added the resource name and gave it «Full Control» too. Nope, problem NOT SOLVED!
I finally noticed that when listing out all the explicit permissions in AD, there didn’t seem to be «Create Computer objects» listed among the various permissions. There was however «Create all child objects». Hmmm, that got
me thinking…
We have a corporate policy which is to move all SQL Servers to a different organizational unit «<domain> — IT Group — Servers» as opposed to the
default OU «<domain> — Computers». My primary and secondary nodes were all in this OU. When WSFC Cluster was created, the CNO was also created in this OU. I started to wonder if adding the
listener, which does not appear to have an option to change the OU, was trying to add it to the default OU. I tested this theory and moved all my servers back to the default OU. I deleted AG, destroyed the cluster, cleaned-up AD and DNS again,
ensured NICs had only their server IPs again, rebooted each machine (because I had to ensure all the GPOs were applied properly and reset firewall, which in my case caused the firewall to be turned on again!), double-checked NIC and firewall settings, and
then tried again…
Success!!! Creating the WSFC cluster created the CNO in the default OU, as I expected since that’s where my primary node was located. When I ran the AG wizard this time and created the listener, it also successfully created the CNO for the listener
in the default OU. DNS had also added an entry with the listener name and IP in the forward lookup successfully too.
So there you have it, for those of you struggling with the same issue. I’m not an AD or DNS expert by any stretch of the imagination, but I sure learned a lot more than I ever wanted to know because of this issue!
If anybody out there knows if it’s possible to create computer objects from a lower node in AD, please let me know how. I’m not sure if ultimately it is just a GPO that’s preventing access to this, or if it’s strictly not possible. In which case,
then I guess my only other choices are to create the whole environment in default OU and then move it to the correct OU after the fact, or to prestage the cluster and AG listener in the AD and DNS. I had tried to do that earlier, but it didn’t seem to
help when I tried to use the AG wizard to create the listener, but perhaps it’s because I was trying to create the object at the wrong OU or one of the other previous issues I was experiencing was preventing it.
Diane
This post primarily concentrates on setting up the listener for a SQL Server Availability Group in AWS in a multi-subnet cluster. Just a warning, the listener does not function in AWS as you would expect on local instances. Don’t assume this article solves listener issues. It does not. I’ll outline what the issues are with the listener in a later post.
I’m assuming you’ve already done the following:
- Installed Sql Server 2012 on a couple of EC2 instances located in different subnets.
- Created a few test databases (and done full backups) on one of said database EC2 instances.
- Created an availability group hosting said databases.
Before I begin, a shout out is deserved to Jeremiah Peschka (blog | twitter), as I’d never have gotten this figured without this post.
Create the listener
In your availability group, create your listener
Enter in the Listener DNS Name, enter in the port (I left the default port of 1433 for this demonstration to keep it simple), and select StaticIP for the Network Mode.
Next, at the bottom of the New Availability Group Listener dialog, click the Add button. This will bring up the Add IP Address dialog. Enter in your static IP addresses. If these IP addresses already exist, you’ll get this error message:
The Windows Server Failover Clustering (WSFC) resource control API returned error code 5057. The WSFC service may not be running or may not be accessible in its current state, or the specified arguments are invalid. For information about this error code, see «System Error Codes» in the Windows Development documentation.
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.
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: 41009)
You’ll want to create new IP addresses for your listener that are not being used by the failover cluster itself. The IP addresses will be added to DNS automatically, as will be illustrated further down in the post. For example, currently on my servers I have addresses of 192.168.10.70 & 192.168.10.50 for one node, and 192.168.11.70 & 192.168.11.50 for the second node. For my IP addresses for the listener, I will add IP addresses of 192.168.10.77 & 192.168.11.77.
Obviously, you have to run this as an account that has the proper permissions to add entries to DNS. In my case, I had god rights. You’ll probably want to be a bit more restrictive.
Next, if you look in your DNS, you’ll see it added the listener name with both IP’s.
Next, go to your AWS console. In the console under your EC2 Dashboard under Network & Security, click the Network Interfaces.
Select the network interface that is associated with your AMI and click Manage Private IP Addresses.
Now add the IP address that the listener created to said network interface. Do this for both IP addresses on each of the network interfaces. Only add the .10 to the AMI in the .10 subnet, and add only the .11 to the AMI in the .11 subnet. (It won’t allow you to add an 11 to the 10 subnet, but just trying to be clear. 
)
Your listener should now be ready for testing. This is where the fun really begins. I’ll split the testing out to another post, as this one is getting pretty big.
Published
October 10, 2012October 10, 2012
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.
Creating availability group listener resulted in an error.
ADDITIONAL INFORMATION:
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.
Solution
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.
Related Articles:
- 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
I hope you like this article. Please follow us on our facebook page and on Twitter handle to get latest updates.
- Author
- Recent Posts
I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Summary
Article Name
Fix Error 19471: Listener issue that came during Configuring SQL Server AlwaysOn Availability Group
Description
AOAG Listener was failed to create during its configuration and displayed error 19471. This error might have multiple reasons. Read this article to fix this issue in step by step manner.
Author
Manvendra Deo Singh
Publisher Name
Techyaz.com
-> I tried creating an Always On Listener on an existing 4 node Always on Availability Group with below details,
-> It failed with below error,
TITLE: Microsoft SQL Server Management Studio
Create failed for Availability Group Listener ‘JBApplication’. (Microsoft.SqlServer.Smo)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.46521.71+(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 Windows Server Failover Clustering (WSFC) resource control API returned error code 5057. If this is a WSFC availability group, the WSFC service may not be running or may not be accessible in its current state, or the specified arguments are invalid. Otherwise, contact your primary support provider. For information about this error code, see “System Error Codes” in the Windows Development documentation.
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.
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: 41009)
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-41009-database-engine-error
-> As per the error message, I checked what is Error code 5057 in “System Error Codes” by executing below command in Command prompt.
NET HELPMSG 5057
-> Error code 5057 refers to “The cluster IP address is already in use.”
-> This means that the IP address is already taken by some other host name.
-> Checking cluster.log for more details. Please use this article to generate cluster.log.
-> Below entries found in cluster.log which is same as what we got from Helpmsg,
000015f4.00000544::2022/05/30-11:58:35.110 ERR [RES] IP Address : IpaValidatePrivateResProperties: IP address 172.20.1.20 was detected on the network.
000015f4.00000544::2022/05/30-11:58:35.110 WARN [RHS] Resource JBAG1234_172.20.1.20 called SetResourceStatus, but OnlineOrOfflineCall call is NULL: Offline, 1.
000015f4.00000544::2022/05/30-11:58:35.110 WARN [RHS] returning ResourceExitStateTerminate.
000015f4.00000544::2022/05/30-11:58:35.110 WARN [RHS] Error 5057 from ResourceControl 20971654 for resource JBAG1234_172.20.1.20.
-> Lets find out the hostname that is using this IP Address by executing below command,
ping -a 172.20.1.20
-> I see that hostname JBSAPP.JBSWIKI.com is utilizing this IPAddress. I advised the same to my platforms team and got a new IP Address. I was able to create the listener after getting the new IP Address.
Thank You,
Vivek Janakiraman
Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.