none
Connection Broker HA, Can't connect to SQL. RRS feed

  • Question

  • I am at my wits end with trying to get our connection broker's into HA, hoping someone has additional ideas as I've sunk an obscene amount of time into this already. Feels like the HA piece of the RDS Connection Broker got about 20min of development.  

    Problem: Server Manager (Tried via PowerShell also) HA wizard refuses to connect to SQL instance.

    Configuration

    • Server 2019 RDS/Connection Brokers
    • SQL 2017 Failover Cluster, Named Instance
    • Connection Brokers security group exists with both RDCB's as members. Group has dbcreator writes on the SQL server.
    • Tried pre-creating the database and giving dbowner, no difference.
    • I've tried with the download SQL Native client, 2012, the latest. I've tried using the Client Tools included with SQL 2017 media. I've tried with the Client Tools included with SQL 2016 SP1
    • No windows firewalls
    • Every other application/connection test is successful to the SQL cluster.
    • Using a UDL file to test the connection string from the connection brokers to the cluster is successful. 
    • Tried "Dedicated database server" and "Shared Datababase server"
    • I've tried connection strings using Trusted Connection and user authentication.

    Absolutely nothing I do will allow the HA piece of RDS to recognize and connect to the SQL instance, it's absolutely poor code or a bug I just don't know how to get around it. 

    Wednesday, August 21, 2019 4:11 PM

All replies

  • Hi,

    May I know when the error message prompted up?

     

    Have you gave two RD CB servers db_owner permissions on the database so they both can write to it?

    RD Connection Broker HA – SQL Permissions

    https://social.technet.microsoft.com/wiki/contents/articles/10393.windows-server-2012-rds-rd-connection-broker-ha-sql-permissions.aspx

     

    Also here is a detailed guidance for you to deploy RDS HA.

    Installing SQL for RDS 2012 – RD HA Mode

    https://ryanmangansitblog.com/2013/03/30/configure-sql-for-rds-2012/

    Deploying RD Connection Broker High Availability in Windows Server 2012

    https://ryanmangansitblog.com/2013/03/30/deploying-rd-connection-broker-high-availability-in-windows-server-2012/

     

    Note: Since the websites are not hosted by Microsoft, the links may change without notice. Microsoft does not guarantee the accuracy of this information.

     

    Hope this would be helpful and please mark useful reply as Answer.

     

    Thanks,

    Jenny


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Thursday, August 22, 2019 6:24 AM
  • Thanks for the reply.

    Both servers are members of the RDS-Connection Brokers security group which has db_owner rights on the pre-created database.

    I've also tried adding the hosts directly, not as part of a sec group. 

    Guides aren't much help, I'm well aware of how to complete the process I just can't get Windows to actually connect and acknlowedge the database, despite everything else possible being able to. The error pops up after right clicking the connection broker in server manager, make HA, it asks for the connection string etc. which I provide. Will never connect. 

    I've tried both trusted and user based connection strings as shown below. 

     DRIVER=SQL Server Native Client 11.0;SERVER=SQL2.cvsweb.com\MSSQL02;Trusted_Connection=Yes;APP=Remote Desktop Services Connection Broker;DATABASE=RDCBHA

    DRIVER=SQL Server Native Client 11.0;SERVER=SQL2.cvsweb.com\MSSQL02;Trusted_Connection=No;APP=Remote Desktop Services Connection Broker;DATABASE=RDCBHA;user:"rdcb";password:"P@ssw0rd!!"

    Thursday, August 22, 2019 2:34 PM
  • Hi,

     

    Thanks for the update.

     

    1.Per searching, there are some users fixed this issue by recreate the security group or restart the servers.

     

    2. Please confirm with SQL server experts below on SQL Server Native Client <version number> for SQL server 2017.

     

    DRIVER=SQL Server Native Client 11.0;SERVER=SQL2.cvsweb.com\MSSQL02;Trusted_Connection=Yes;APP=Remote Desktop Services Connection Broker;DATABASE=RDCBHA

     

    SQL server forum:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=sqltools&filter=alltypes&sort=lastpostdesc

     

    Kindly verify if above info helpful and keep us posted if more assistance needed.

     

    Thanks,

    Jenny


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Friday, August 23, 2019 9:26 AM
  • Hi,

    Is there any update? Please feel free to let us know if more assistance needed.

     

    Have a nice day.

     

    Thanks,

    Jenny


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, August 27, 2019 1:40 AM