While doing a preparation of a demo, I encountered below error while creating the listener of the AlwaysOn availability group. I was trying this from Management Studio.
Here is the text of the error message.
The configuration changes to the availability group listener were completed, but the TCP provider of the instance of SQL Server failed to listen on the specified port [AGListener:1433]. This TCP port is already in use. Reconfigure the availability group listener, specifying an available TCP port. For information about altering an availability group listener, see the “ALTER AVAILABILITY GROUP (Transact-SQL)” topic in SQL Server Books Online. (Microsoft SQL Server, Error: 19486)
If you investigate SQL Server ERRORLOG, you should see below.
2017-09-11 11:29:34.60 spid74 Error: 19476, Severity: 16, State: 4.
2017-09-11 11:29:34.60 spid74 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.
2017-09-11 11:30:01.19 Server The Service Broker endpoint is in disabled or stopped state.
2017-09-11 11:30:01.19 Server Error: 26023, Severity: 16, State: 1.
2017-09-11 11:30:01.19 Server Server TCP provider failed to listen on [ 10.0.1.50 1433]. Tcp port is already in use.
2017-09-11 11:30:01.19 Server Error: 26075, Severity: 16, State: 1.
2017-09-11 11:30:01.19 Server Failed to start a listener for virtual network name ‘AGListener’. Error: 10013.
2017-09-11 11:30:01.19 Server Stopped listening on virtual network name ‘AGListener’. No user action is required.
2017-09-11 11:30:01.19 Server Error: 10800, Severity: 16, State: 1.
2017-09-11 11:30:01.19 Server The listener for the WSFC resource ‘dc977169-2387-499e-8047-3b197e7ada61’ failed to start, and returned error code 10013, ‘An attempt was made to access a socket in a way forbidden by its access permissions. ‘. For more information about this error code, see “System Error Codes” in the Windows Development Documentation.
2017-09-11 11:30:01.19 Server Error: 19452, Severity: 16, State: 1.
2017-09-11 11:30:01.19 Server The availability group listener (network name) with Windows Server Failover Clustering resource ID ‘dc977169-2387-499e-8047-3b197e7ada61’, DNS name ‘AGListener’, port 1433 failed to start with a permanent error: 10013. Verify port numbers, DNS names and other related network configuration, then retry the operation.
Above confirmed that there is some other process listener on 1433 port. Then I use one of my old blog to find out which process is using that port.
I found that it was another system process, so I should change my listener port. Since listener was already created all I needed was to change the port. Here is the T-SQL I have used to change the port to 2433.
USE [master] GO ALTER AVAILABILITY GROUP [BO1AG] MODIFY LISTENER N'AGListener' (PORT=2433); GO
This time, command was completed without any error. I confirmed from the Errorlog that there was no error related to the listener.
2017-09-11 11:34:35.460 Server Started listening on virtual network name ‘AGListener’. No user action is required.
Have you encountered any such error? Feel free to comment and share it with others.
Reference: Pinal Dave (https://blog.sqlauthority.com)