Windows Server 2012 RDS: RD Connection Broker HA - SQL Permissions
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>…”
http://lh3.ggpht.com/-LlggFHOBE1I/T4wusaLlkaI/AAAAAAAAAqo/_R7_GgAcPxc/image_thumb%25255B33%25255D.png?imgmax=800
Assuming that,
- 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:
http://lh6.ggpht.com/-2_gmp_CD594/T4wuvLpZuhI/AAAAAAAAAuA/FQXWvOKExxc/image16_thumb.png?imgmax=800
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.
http://lh6.ggpht.com/-AJExH_QWUIo/T4wuw5NuDDI/AAAAAAAAArI/MHKF0bjCzm0/image_thumb%25255B15%25255D.png?imgmax=800
Then we add this group as a SQL Login in SQL Server manager.
http://lh5.ggpht.com/-ctEIz-csn_c/T4wuzHnQGcI/AAAAAAAAArU/k4v8tsjm02s/image_thumb%25255B17%25255D.png?imgmax=800
If we then try the wizard again, it will try to configure HA. However, without any further preparations, the following error will be raised
http://lh5.ggpht.com/-4n2TVHeybKs/T4wu08aNdvI/AAAAAAAAArk/phvt9L-u6fw/image_thumb%25255B19%25255D.png?imgmax=800
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.
http://lh5.ggpht.com/--8jgRFIfYFI/T4wu2vYG13I/AAAAAAAAAr4/V_XLy-XFv7s/image_thumb%25255B21%25255D.png?imgmax=800
When trying the wizard again, it will succeed.
http://lh4.ggpht.com/-eLk0QmA_75o/T4wu4egiPXI/AAAAAAAAAsE/Hr3zCqe8QNE/image_thumb%25255B23%25255D.png?imgmax=800
The database is created and exists in the folder we specified.
http://lh4.ggpht.com/-my3HM_JCQsM/T4wu6bxqGBI/AAAAAAAAAsU/sqsm5giFcGA/image_thumb%25255B24%25255D.png?imgmax=800
http://lh4.ggpht.com/-fJ0jcOEWG5g/T4wu79_YHvI/AAAAAAAAAso/Q-2lavcbgc8/image_thumb%25255B26%25255D.png?imgmax=800
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:
http://lh3.ggpht.com/-ZoUNM4f8zEY/T4wu-IvWDiI/AAAAAAAAAs0/Y-JYgj1Yxxc/image_thumb%25255B30%25255D.png?imgmax=800
And the SQL Server log will raise the following events:
http://lh3.ggpht.com/-YhFQPG1mi8U/T4wu_S0pHsI/AAAAAAAAAtE/vtdE4wKhjcE/image_thumb%25255B28%25255D.png?imgmax=800
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.
http://lh5.ggpht.com/-NC3OfaPxZck/T4wvBKNcubI/AAAAAAAAAtY/g7xkPhFRpL0/image_thumb%25255B32%25255D.png?imgmax=800
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