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.

Listener Creation Fails due to Lack of Permission Issue

This post is continuation of my previous Availability Group Listener creation post

If the domain Administrator is not allowing the Permission to create and read computer Objects then we can use this method for successful creation of Availability Group Listener.

Here we are creating the VCO ( which is Virtual Computer Object ) manually in Active Directory as we don’t have permission to create computer objects Automatically.

For doing this make sure the user who logged in has a permission to create computer objects in domain.

  • Open Active Directory Users and Computers ( shortcut to open from powershell : dsa.msc )
  • Click on VIEW and select ‘ Advanced Features 
  • Locate your container where the listener is getting created, in my case it will locate on COMPUTER as shown in below

Now right click on the container where VCO needs to get created in my case it is in COMPUTERS –>click on NEW–>Click on Computer

Here we are creating the Listener Object in COMPUTERS Container

A Pop window ‘ New-Object Computer ‘ will be opened to provide your Listener Name –> Enter your Listener Name in Computer Name Field (AGL)–> click OK.

Now right click on the created VCO which is AGL in my case –click on Properties–>Click ADD button–>enter your cluster name ( in my case AWON$)–>click on Object types–>Enable check box for Computers–> click OK –>Again click OK

Now click on the Cluster Name ( AWON ) and see the permissions below and then click OK.

Read
Allowed To Authenticate
Change Password
Receive As
Reset Password
Send As
Validate write To DNS Host Name
Validate Write To Service Principle Name
Read Account Restrictions
Write Account Restrictions
Read DNS Host Name Attributes
Read MS-TS-GatewayAccess
Read Personal Information
Read Public Information

So, now Virtual Computer Object which is Listener is Created in Active Directory Domain.

Now try to create Listener Using SQL Server Management Studio(SSMS) and it will get created successfully without any Issue.

Availability Group Listener Creation Fails 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 is the Error Screenshot we get :

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

To resolve CNO Permission Issue follow below instructions:

To fix we have to GRANT below 2 permissions to CNO :

  1. Read all Properties
  2. Create Computer Objects

For providing above permissions proceed with given steps:

  • Open Active Directory Users and Computers ( shortcut to open from powershell : dsa.msc )
  • Click on VIEW and select ‘ Advanced Features
  • Locate your container where the listener is getting created, in my case it will locate on COMPUTER as shown in 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.