RDS Connection Broker High Availability cannot connect to database

Question

Scenario

You want to configure Remote Desktop Services Connection Broker in High Availability mode, using (at least) Windows Server 2016.

You have completed and verified all prerequisites:

• database is accessible over network (all firewalls and routing OK),
• database permissions are OK (db_creator for the group containing all target RDS CB hosts),
• SQL driver is installed and connection string is correct.

You begin configuring High Availability either via Wizard:

or via Powershell:

Set-RDConnectionBrokerHighAvailability `
    -ClientAccessName "rds.contoso.com" `
    -DatabaseConnectionString "DRIVER=SQL Server Native Client 11.0;SERVER=db.contoso.com;Trusted_Connection=Yes;APP=Remote Desktop Services Connection Broker;DATABASE=rdscbha"

You omit the "Folder to store database" or -DatabaseFilePath because, well, it's optional and your MSSQL is already configured on where to store the new databases by default.

Problem

You get the following error message on GUI:

or the following error on PowerShell:

The database specified in the connection string DRIVER=SQL Server Native Client 11.0;SERVER=db.contoso.com;Trusted_Connection=Yes;APP=Remote Desktop Services Connection Broker;DATABASE=master is not available from the RD Connection Broker server RDS-01.CONTOSO.COM. Ensure that the database server is available on the network, the SQL Server Native Client is installed on all RD Connection Broker servers, and the computer accounts of the RD Connection Broker servers are members of the RDS Management Servers group on the database server.
    + CategoryInfo          : NotSpecified: (:) [Write-Error], RDManagementException
    + FullyQualifiedErrorId : SetRdmsHighAvailability,Microsoft.PowerShell.Commands.WriteErrorCommand
    + PSComputerName        : localhost

Diagnostics

You are really sure literally everything is OK. You can start PowerShell as SYSTEM user using psexec -is powershell and create the database to your liking using PowerShell SQL tools and the correct driver.

Nothing is wrong.

One thing is odd, though. You check your firewall using GUI wizard or PowerShell command and you see absolutely no SQL server connection attempt.

What's wrong?

Answer 1

Despite what Microsoft claims in the UI or the PowerShell command...

...the database path is not optional!

Yep. Once you add the database path, everything starts working!

Magically, if though you only specify your database path, eg.:

C:\ClusterStorage\SQL Data\MSSQL13.MSSQLSERVER\MSSQL\DATA

if your logs have different default path, the newly created database will be created correctly:

C:\ClusterStorage\SQL Log\MSSQL13.MSSQLSERVER\MSSQL\DATA

Answer 2

This topic might be a bit old, but we only recently upgraded to 2016 RD Server Farm, and all other stuff I found while googling did not work, your answer finally pointed me to the solution. So I thought I'd share some extra info, if someone still plans to do an upgrade.

I would like to add, it also didn't work when I tried to use a different path than the default database root directory path. You can get your path in the options of the server in MS SQL Server Management Studio.

In that folder there's a service account user named MSSQLSERVER with Full Rights, the broker seems to use that to create the database. In every other folder on the server it showed the same error that was posted above.

Additional Info: When you want to add a second server to the HA Broker, you need to map the RDS Broker Security group as a dbowner on the newly created database first. This isn't done automatically.

Answer 3

Try adding like this. It should work.

DRIVER=SQL Server Native Client 11.0;SERVER=db.contoso.com,1434;Trusted_Connection=Yes;APP=Remote Desktop Services Connection Broker;Database=RDSDB

Answer 4

The thing is in permissions inside MS SQL instance. You should go into SQL → Security → Logins → RemoteAppBrokersGroup → Properties → Securables → Create any database (you dont get this checkbox automatically when assign dbcreate permissions)