I've received some questions via e-mail on one of my previous posts on how set up High Availability on the Connection Broker in Windows Server 8 (Beta)
http://microsoftplatform.blogspot.com/2012/04/how-to-configure-high-availability-for.html
The questions were related to preparing the correct (SQL) permissions to be able to enable High Availability using the Server Manager wizard. In this blog post I’ll dive a little deeper in the SQL permissions part. But before we get started, since we’re dealing with beta product and since there is no official Microsoft documentation on this exact configuration yet, this blog post contains my personal thoughts on what I think the configuration should be.
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. 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 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.
Then we add this group as a SQL Login in SQL Server manager.
If we then try the wizard again, it will try start to configure HA. However, without any further preparations, the following error will be raised
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 try 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 place you will receive the following error in the wizard:
And the SQL Server log will raise the following events:
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.
Bottom-line:
- 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.
I have followed the steps shown here, and yet I still get the "Could not create the database" error message. I have verified that firewall is off, SQL is listening on 1433 and I can telnet to it, security is set as above yet there are no events in SQL or Windows.
ReplyDeleteHi Damian,
ReplyDeleteDid you check if the SQL Server Clients tools are installed on the RD Connection Broker and that you refer to the correct version on this installed SQL Server Clients tools inside the specified connection string?
Hi Freek Berson,
ReplyDeleteThanks for taking the time out to post this.
I have setup a cluster in 2008 R2 (SQL-CLUSTER\CLUSTER) and I cant seem to get it to work. I used Native Client 10.0 in my connection string. When testing on a 2012 standalone server (default instance) it worked first time with Native Client 11.0 in my connection string. I can't figure out what is wrong! Any ideas? Both Native tools are installed (for 2012 and 2008 R2 on the Connection Broker servers) and when I try to connect to 2008 R2 cluster nothing shows up in the logs. Just give that error that the database specified is not available. Can you give me an example of a connection string with my names (including instance)? and if you know of anything else I can look at? SQL using static default port not dynamic
I had the same issue. I created an ODBC connection system DSN for the connection broker to the SQL server. That then allowed the first CB server to connect. I then did the same to the second CB but specified the new RDS database in the ODBC connection. I then changed the permissions on the SQL server for the CB's to have sysadmin permissions. That fixed everything.
DeleteI need to do Virtual machine based RDS with HA. So i tried the steps mentioned above and done collection creation with one desktop. Collection alone can see in other machine when one shutdown but the desktop in that collection is not shown under collection. But i can see that desktop in Hyperv manager and failover cluster manager (because of cluster i hope). Please let me know whether the above setup will support Virtual machine based desktop deployment or need to do anthing additionally to achieve.
ReplyDeleteReboot the RDSH servers so that they know they are a member of the group, that is usually the problem.... which I found out after 2 hours of going around in circles because the docs don't say to do that!
ReplyDeleteI spent much more than 2 hours on this. Thanks for letting me know the fix!
DeleteThis instruction didn't work unless I also added a login for "NT AUTHORITY\NETWORK SERVICE" as well.
ReplyDelete