Multiple Listener Creation

We all know that only ONE Availability Group Listener can be created for Availability Group , if we tried to create another Listener using SSMS we will get below error :

As per the above error we can’t create another Listener in an Existing Availability Group.

But sometimes having Multiple Listeners will help when performing Migrations. So, my todays post is how to create Another Listener on Existing Availability Group .

Creating another Listener is not supported by Microsoft that’s the reason we are not able create using SSMS and its undocumented. This can be approached through Failover Cluster Manager.

open your WSFC–> expand your Cluster –>click on Roles–>on Right Pane select the Availability Group –> down at User Setting click on RESOURCES TAB to see existing Listener we have

To create new Listener on the existing Availability Group go t hrough below steps :

Now in Roles on Right pane–>Select your Availability Group for which you want to create another Listener–>Right click on the Availability Group–>go to Add Resources –> click on ‘ Client Access Point

After clicking on Client Access Point–> A Pop Window ‘ New Resource Wizard ‘ will be opened–> Provide your New Listener Name in Name Field–>Also Provide the New Listener IP in ADDRESS Field–> click on NEXT

Your New Listener AGL1 will get created and it will be in OFFLINE Status , right click on AGL1 and click BRING ONLINE as shown below :

As I configured Multisubnet Failover Cluster seeing two IP’s for Listener.

Once new Listener was created –>Right click on Availability Group under User Settings–> AG Properties Pop window opens–> select DEPENDENCIES TAB–>Enter your New Listener Name on Resource Field and Select OR option as shown like below :

Once above all steps are completed we can see your New Listener in SSMS now :

But if you try connecting with New Listener Name AGL1 it will fail as we have not provided the PORT info in above steps .So, go to your New Listener AGL1 Properties in SSMS –> enter the PORT Number –>click OK

Now try connecting with New Listener Name AGL1 and it will get successfully connected.

Availability Group Listener Creation Failed because of CNO Permission

The most common issue we face when creating Availability Group Listner in SQL Server using SQL Server Management Studio(SSMS)

Below Screenshot shows the error details :

To conclude that the above error is for CNO Permission Issue, please generate the cluster logs on your Primary replica and go through the logs , once it confirms the issue is with Lack of CNO Permission in Active Directory to read and create computer objects for Listener

CNO is nothing but Cluster Name Object which is a Cluster Name in my case it is AWON

Follow the steps below to resolve the CNO Permission Issue:

To remedy this, we must GRANT CNO the following permissions:

  1. Read all Properties
  2. Create Computer Objects

Follow the steps below to grant the aforementioned permissions:

  • Open Active Directory Users and Computers ( shortcut to open from powershell : dsa.msc )
  • Click on VIEW and select ‘ Advanced Features
  • Locate the container in which the listener will be formed; in my case, this will be COMPUTER, as seen below.
  • select computer–> right click –>Properties
  • Now click on ADD button and enter your CNO name. In my case it is AWON$
  • Now click on object types and enable check box COMPUTERS then click on OK ->OK
  • And now you will be back to Computer Properties TAB, click on ADVANCED TAB –>Advanced Security Settings for computers Pop window will be opened–>Select your CNO ( Mine is AWON ) from Permission Entries –> Click on EDIT button
  • A pop window ‘ Permission entry for Computers ‘ will be opened , now Enable check boxes for ‘ Read all Properties ‘ and ‘ Create Computer Objects ‘ –>click OK –> Again click OK –> click Apply –> click OK as shown below :

Now try again to create Listener and it will get created without any issue.