Error connecting to sql using listener name only



    I've a problem when i try to connect to an instance with Always On

    When i use a domain account, i can connect using listener_name\instance name or only listener_name

    When i use a sql account, i can only connect using listener_name\instance_name but not using only listener_name (error 18456)

    Is there a solution to avoid it or it is normal ?

    Many thanks !


  • Have you copied the sql based logins to all replicas and ensured they have the same SID etc using something like sp_help_revlogin or DBATools powershell module?


    Can you post the fill 18456 error out, the state number details which 18456 issue it is.

  • Hi

    Yes, i've replicate the account on the secondary using sp_help_revlogin

    the details are

    Error Number: 18456

    Severity: 14

    State: 1

    Line Number: 65536

    Many thanks

  • State1 doesnt give anything more to the problem, thats just the generic something went wrong state no detail.

    Do you have multiple instances on this setup?  Clutching at straws but I would guess so if using an instance name works, there must be a default instance which the account doesnt have access too.

    Would start with instances, then look at the listener configuration and see how its all hanging together and see where things are failing that way.

    LISTENER_NAME goes to an instance on 1433 where you have a domain credential but no sql credential

    LISTENER_NAME\INSTANCE_NAME goes to an instance on a random port, where you have a domain credential and a sql credential

  • There are several instances on the server but the sql account i use has been created just now ; just for the purpose of this test

    Created on node1 instance1 then export to node2 instance1, that's all !

    Normally, there is nothing more to do in dag or listener configuration no ?

  • I can't see your setup, so I can't say for certain, but with multiple instances, your best creating a listener per instance&replica&database set

    Eg Server1&Server2 on default instance is one listener  AGDefault

    Server1\Instance1 & Server2\Instance1 is one listener AGInstance1

    It will all come down to how the listener is configured and if your using the right listener for the right instance and how its all routing.


    LISTENER_NAME is going to 1433 where you dont have the sql account

    LISTENER_NAME\INSTANCE name is going to random port where you do have sql account.

    So its all in your configuration and you will need to work out how things are hanging together

  • You're right

    If i only give the listener name, it's toward the listener designed with the port 1433 that it's really transmit... the port of the listener of the first instance created !

    It works if i give the port : listener-name,port_number ...

    I don't know why it's like this because  in case we want to change the database location, all connection chains will have to be changed if the port number change, even if we've have created an dns alias toward the listener_name to avoid it ...

    Many thanks for your time 🙂


  • That is where the configuration of the listener, ports, instances, replicas all comes in and should be static.

    For the default instances, use port 1433 on the instance and the listener

    For named instances, set a static port at the instance level which is the same on all replicas, create a 2nd listener and use that same port number

    Rinse and repeat

    So if you have 10 instances you need 10 or more listeners (as you may want to only have certain DBS in a group/listener)

    10 instances with 1 listener to try and control them all is disaster waiting to happen

  • Sure !

    I've 6 instances, each one with a dag and unique listener name , unique ip and unique port number but when i use only the listener name to connect, it's always the 1433 one's which is connected to ....

    It means that the port number for the instance has to be fixe and the same of his listener ? (1435 for exemple)

  • Yes so in each listener set the port to be different to the others

    Listener1 port 1433

    Listener2 port 1435

    Listener3 port 1436



    if all the listeners are set to 1433 that’s the issue as they will all go to the default instance unless specifically said to go to an \InstanceName or ,Port

  • Hi

    i've 6 instances , each one with unique parameters.

    I mean :

    I1 DAG1 Listener1 listener_IP1 Listener_port 1433

    I2 DAG2 Listener2 listener_IP2 Listener_port 1434

    I3 DAG3 Listener3 listener_IP3 Listener_port 1435

    I4 DAG4 Listener4 listener_IP4 Listener_port 1436

    I5 DAG5 Listener5 listener_IP5 Listener_port 1437

    I6 DAG6 Listener6 listener_IP6 Listener_port 1438

    All parameters/config are uniques

    But all instances are not with a fixed port number on tcp/ip config but with dynamic port (56854 or 89564, etc.)

    My interrogation was about that and, if I wanted to use the listener name only, without instance name or port number, the only way was to change the TCP/IP properties at the instance lever and set a hard port number instead of a dynamic one ?

    Many thanks in all cases for your time  🙂

  • Something doesn't seem right then as they should route correctly if they are all different.

    I don't like dynamic ports for instances, its a networking headache, I would set them all to static and make them match the listener if they all tie to different instances.

    The way I have done it in the past is the same port for the listener as the instance which has worked.

  • Why did you configure that many Listeners ?

    A listener is a AD object, managed by your windows cluster.

    A single one could be sufficient to have you AG available




    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply