RD Connection Broker HA – SQL Permissions

RD Connection Broker HA – SQL Permissions

In a previous Wiki article, we ran through the process of setting up HA for the RD Connection Broker in Windows Server 2012.
http://social.technet.microsoft.com/wiki/contents/articles/10390.deploying-rd-connection-broker-high-availibilty-in-windows-server-2012.aspx

In this wiki we'll dive a little deeper in the SQL permissions part.

The prerequisite that refers to the SQL permissions is explained by the setup as: “A Microsoft SQL Server with write permissions granted to all RD Connection Broker servers that will be part of the deployment.” But what exactly does that mean?

When you would not prepare any SQL permissions except opening port 1433 for your RD Connection Broker, you will receive the following error in the wizard:

“The database specified in the database connection string is not available from the RD Connection Broker server <servername>…”

image

Assuming that, 1. you did install the SQL Native Client on the RD Connection Broker and 2. traffic on port 1433 to the SQL Server is possible, you will see a log entry generated on the SQL Server that looks something like this:
image

The wizard to configure HA is trying to connect to the SQL Server using the computer account of broker.

So, basically what we need to do is create a group in Active Directory, and place all RD Connection Broker computer objects in there.

image

Then we add this group as a SQL Login in SQL Server manager.

image

 

 



If we then try the wizard again, it will try to configure HA. However, without any further preparations, the following error will be raised

image

 

 



The wizard is unable to create a database. Why? Because we didn’t assign the AD group any roles or permissions. We open up SQL Server manager again, open the group and specify the role dbcreator.

image

 

 



When try the wizard again, it will succeed.

image

 

 



The database is created and exists in the folder we specified.

image

 

 

image

 

 



Note that when we add a second (or any new) RD Connection Broker to the HA setup, that new server also needs permissions to the database. If those permissions are not in place you will receive the following error in the wizard:image

And the SQL Server log will raise the following events:

image

 

 



We open up SQL Server Manager again, open up the properties of the group we created select server mappings, select our database and we (although datareader and datawriter will probably also be enough) give our group owner permissions on our database.
image
This will allow us to successfully add new RD Connection Brokers to out HA environment, as long as they are a member of the created group.

In short:
  • create a new AD group that contains your RDCB servers participating in the HA
  • Give that group dbcreator permissions to be able to create the database during the wizard
  • Give that group owner permissions (or at least dbwriter and dbreader) on the newly create database to ensure all RDCB servers are able to contact the database.

A final FYI to be aware of, if you created a database before starting the RDCB HA wizard the wizard will delete this database and create a new one during the setup.

Original source: http://microsoftplatform.blogspot.com/2012/04/rd-connection-broker-ha-sql-permissions.html

Sort by: Published Date | Most Recent | Most Useful
Comments
  • Freek Berson edited Original. Comment: Added a source link

  • I can connect to my SQL Cluster instance via powershell from the RDCB VM fine using this connection string::  DRIVER=SQL Server Native Client 11.0;SERVER=DFWWHDB102\APPLICATION;Trusted_Connection=Yes;APP=Remote Desktop Services Connection Broker;DATABASE=VDI

    I've deleted the database and told the RDCB HA wizard that same string but it yields the error message stated above.  If I remove the instance name, it will clear that screen and go one, but fail to create the database after clicking "configure".  Does RDCB HA not support named instances?