none
Link server configured to connect Always on read only connection to secondary replica RRS feed

  • Question

  • Hi  we have always on server 2016 R2 configured and read only routing also configured . Basically "user1" connect from client server and display as secondary  server. That ensured the read only routing is working 

    sqlcmd.exe -S  LSNRname -d databasename -E -K ReadOnly

    1>     Go

    2>     Select @@servername

    3>     Go

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

    Question is on same client server(other 2016 R2 SQl server) I need to create a linked server to always on. and i created as follows. 

    EXEC sp_addlinkedserver 
    @server = N'Linkservername, 
    @srvproduct=N'SqlServer',
    @provider=N'SQLNCLI11', 
    @datasrc=N'Listenenrname ', 
    @provstr=N'ApplicationIntent=ReadOnly', 
    @catalog=N'DBname';

    .Linked server created. 

    then I run following command to see which server is accessing from clinet server 

    select * from OPENQUERY ( [Linkservername] ,'select @@servername' )

    Its returned the correct servername.

    Q1. I am not sure what user is use to connect which assess DB from the link. When select "Be made using the login's current security context"

    Q2 . Secondly when I try to add user on  , then I am getting error. But please note I connect  using SSMS  same user specifying   listenername + ApplicationIntent=ReadOnly  and connected correctly.  Not sure why. Any one has any idea would be great . 

     





    • Edited by ashwan Thursday, August 22, 2019 5:45 AM
    Thursday, August 22, 2019 5:33 AM

All replies

  • Hi ashwan,

     

    >> I am not sure what user is use to connect which assess DB from the link. When select "Be made using the login's current security context"

     

    This setting uses the same login connected to the local instance to connect to the remote instance. When using a SQL authenticated user, the username and password need to be the same on both instances. For Windows authenticated users, they can have direct access or be part of an AD group on the remote instance.

     

    >>Secondly when I try to add user on  , then I am getting error. But please note I connect  using SSMS  same user specifying   listenername + ApplicationIntent=ReadOnly  and connected correctly.  Not sure why. Any one has any idea would be great . 

     

    'Be made using this security context' setting uses a SQL authenticated login on the remote instance for all connections using the Linked Server. Did you add the remote login to the linked server?

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, August 23, 2019 6:18 AM
  • Dedmon, I able to setup user and now connect to secondary sever2(which is read only replica) . But when failover done server 1 to server 2(now server 2 is primary )  when I checked the link server , still connect server 2 which primary now. But it supposed to connect now server 1.  My routing is working correctly . When I check from sqlcmd. Any idea why server link server connection will not change depending on the role of the servers?

    many thanks 

    Saturday, August 24, 2019 6:44 AM
  • In your linked server configuration the data source is given as 'listenername' . You may want to try 'select host_name()' instead of select @@servername.
    Saturday, August 24, 2019 3:51 PM
  • Is your read-only routing list properly configured on both Node 1 and Node 2?

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, August 26, 2019 9:36 AM