AlwaysOn ReadOnly Routing

• rajpat13

  Old Hand

  Points: 357

  More actions

  November 16, 2015 at 11:12 am

  #305154

  Hi Folks,

  I am having some trouble setting up the AlwaysOn readonly routing. I'd appreciate if someone can help me solve this issue.

  Setup:

  3 Nodes in Primary Datacenter (NODE1, NODE2, NODE3)

  1 Node in DR Datacenter (NODE4DR)

  Availability Group Name: AG_NODE

  Listener Name: AGListNode

  Primary Node: NODE1

  Here is the script I ran to setup readonly routung:

  --READONLY ROUTING URLs

  ALTER AVAILABILITY GROUP AG_NODE MODIFY REPLICA ON N'NODE1'

  WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N'TCP://NODE1:1433'))

  ALTER AVAILABILITY GROUP AG_NODE MODIFY REPLICA ON N'NODE2'

  WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N'TCP://NODE2:1433'))

  ALTER AVAILABILITY GROUP AG_NODE MODIFY REPLICA ON N'NODE3'

  WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N'TCP://NODE3:1433'))

  ALTER AVAILABILITY GROUP AG_NODE MODIFY REPLICA ON N'NODE4DR'

  WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N'TCP://NODE4DR:1433'))

  --READONLY ROUTING LIST

  ALTER AVAILABILITY GROUP AG_NODE MODIFY REPLICA ON N'NODE1'

  WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(N'NODE3', N'NODE2', N'NODE1', N'NODE4DR')))

  ALTER AVAILABILITY GROUP AG_NODE MODIFY REPLICA ON N'NODE2'

  WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(N'NODE3', N'NODE1', N'NODE2', N'NODE4DR')))

  ALTER AVAILABILITY GROUP AG_NODE MODIFY REPLICA ON N'NODE3'

  WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(N'NODE2', N'NODE1', N'NODE3', N'NODE4DR')))

  ALTER AVAILABILITY GROUP AG_NODE MODIFY REPLICA ON N'NODE4DR'

  WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(N'NODE3', N'NODE2', N'NODE1', N'NODE4DR')))

  Issue:

  In my report, When I use Windows Authentication, it works fine. I see report connection to NODE3. No issue here.

  Here is the connection string:

  Data Source=AGListNode;Initial Catalog=MyDB; ApplicationIntent=ReadOnly; MultiSubnetFailover=True

  However, when I change the connection from Windows Authentication to SQL Authentication, it says "Login failed for user 'username'." I have verified that SQL user is valid user. I can loging using SSMS. Just to test, I made SQL user sysadmin, but it still doesn't work.

  Also, when I change the ApplicationIntent from ReadOnly to ReadWrite, I don't get "Login failed for 'username'." error, but, the server points to NODE1, which is expected.

  SQL logins on all servers were created with same SID too.

  Am I missing something in the readonly routung setup? Thanks in advance.

  Raj

• Perry Whittle

  SSC Guru

  Points: 234013

  More actions

  November 17, 2015 at 4:52 am

  #1840265

  rajpat13 (11/16/2015)

  ---

  it says "Login failed for user 'username'." I have verified that SQL user is valid user.

  Whats the full error message you receive?

  The routing scripts look ok apart from the lack of FQDNs for the node names

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs" 😉

• rajpat13

  Old Hand

  Points: 357

  More actions

  November 18, 2015 at 7:40 pm

  #1840872

  Hi Perry,

  Thanks for the reply. That is the only error I get. I have attached ReadOnlyRoutingError.png file for your review.

  Thanks.