November 30, 2020 at 10:26 am
Hello
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 !
J
November 30, 2020 at 10:34 am
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.
November 30, 2020 at 10:41 am
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
November 30, 2020 at 10:55 am
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
November 30, 2020 at 1:12 pm
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 ?
November 30, 2020 at 1:25 pm
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
November 30, 2020 at 1:54 pm
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 🙂
November 30, 2020 at 3:45 pm
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
November 30, 2020 at 4:03 pm
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)
November 30, 2020 at 5:48 pm
Yes so in each listener set the port to be different to the others
Listener1 port 1433
Listener2 port 1435
Listener3 port 1436
Etc
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
December 1, 2020 at 8:14 am
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 🙂
December 1, 2020 at 9:19 am
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.
December 1, 2020 at 10:44 am
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
listeners-client-connectivity-application-failover
Johan
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 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply