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?

Solution

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