Table of Contents

Previous Part

In a previous Wiki article, we ran through the process of setting up HA for the RD Connection Broker in Windows Server 2012.

Deploying RD Connection Broker High Availability in Windows Server 2012

Introduction

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>…”

Assuming that,

  1. you did install the SQL Native Client on the RD Connection Broker and
  2. 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:

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

Steps

1. create a new AD group that contains your RDCB servers participating in the HA

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

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

 

 

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

 

2. Give that group dbcreator permissions to be able to create the database during the wizard

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.

When trying the wizard again, it will succeed.

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

 

 

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 a place you will receive the following error in the wizard:

And the SQL Server log will raise the following events:

3. Owner permissions

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.

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.

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.

Resume

  • 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.

Credits

Originally published at:  http://microsoftplatform.blogspot.com/2012/04/rd-connection-broker-ha-sql-permissions.html