Configure SQL for RDS 2012

Installing SQL for RDS 2012 – RD HA Mode

For this example i am using SQL Express 2012 and a single instance.

For more information on high available SQL Solutions please see the following link: 







you can set a Service account, for this example i have used the defaults.


Enter the User names that can access SQL administrator and a SA password.


Select default settings for the reporting services.

Configure security permissions

Create a security group and add the RDCB servers as members.


Add all connection brokers into the security Group


Create a new user and add the Group name to the login name.


In the Server Roles page select DBCreator, Sysadmin and public.


Once you have configured the RDS Connection Broker login, you will see it appear under logins in the security folder for the SQL instance.

Configure a Static SQL Port


Open the SQL Server Configuration Manager, open the TCP/IP Properties under SQL Network Configuration and set the listen all option to NO.


Remove 0 from the TCP Dynamic Ports and add 1433 to the TCP port for all IP address’s.



Once you have changed the TCP setting from dynamic to Static you need to create a inbound firewall rule for TCP 1433 allow.

Configuring SQL Native Client on Connection Brokers

To allow the connection brokers to communicate with SQL you need to configure the SQL Native Client using ODBC.





15 thoughts on “Configure SQL for RDS 2012

Add yours

  1. Hi Ryan,

    Thank you for these awesome blogs. They have helped me a great deal with my RDS VDI deployment. However I hit a snag. I cannot seem to add a second connection broker to my HA setup. I have followed this article step by step, tested the connection to the sql database through ODBC and that works. However when trying to add the 2nd connection broker, I get this error

    “The database specified in the connection string DRIVER=SQL Server Native Client 11.0;SERVER=meda-sql01;Trusted_Connection=Yes;APP=Remote Desktop Services Connection Broker;Database=RDSQL is not available from the RD Connection Broker server 2012RDS-CB02.TEST.LOCAL. 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.”

    I am pretty stuck because I’ve tested the connection to the database from the 2nd connection broker, installed the native client, it is a member of the security group to manage the servers. Any help would be greatly appreciated. Thanks!!

    1. Hi,

      Can you confirm the following for me:

      The Firewall Rules have been created for SQL on all the Remote servers that require access.
      All Servers have been added to a RDS Server Pool or equivalent
      Are you using SQL 2008R2 or SQL 2012 ( ensure you are using the correct Client) Server Native Client 11.0/10.0
      can you connect to SQL from the new Connection broker via Management Studio ?
      Does the new Connection broker have the permissions to connect to the database ?

      Is there anything showing in the event logs ?

      Best Regards,

      1. Hi Ryan,

        Thank you for your prompt reply. I actually was just able to add it after I allowed the sysadmin role in login properties for my connection brokers. I read this in your blog about certificates, so thought I would give it a try and boom!! You may want to update this blog to include this role, however this does not make sense to me as my first connection broker was able to be added without the sys admin role. To answer your questions

        Firewall rules have been created following instructions of this blog

        All server were added to RDS server pool
        Using Sql 2012 Express
        New connection Broker does have permissions to connect to database.

        Thanks again and I can’t say enough about how much your posts have helped me with my job.

      2. Thanks for the comments and I am glad to help, let me know if you need anything else. Look forward to the Server 2012 R2 updates.

        Best Regards,

  2. Hi Ryan,
    Thanks for providing a step by step of this.

    I followed your steps, but I’m not able to get this to connect either. I get the “The database specified in the connection string DRIVER=SQL Server Native Client 11.0;SERVER=meda-sql01;Trusted_Connection=Yes;APP=Remote Desktop Services Connection Broker;Database=RDSQL is not available from the RD Connection Broker server 2012RDS-CB02.TEST.LOCAL. error message as well. My connection broker sees the SQL server in its list of SQL servers, but not the SQLSERVER\RDSQL database.

    I’m running SQL Express 2012, Native client 11 on my connection broker. I have the group created, and the firewall has a rule in place (and is completely off). I can telnet into port 1433 from the other computer. I have the server login with public, dbcreator, and sysadmin roles.

    Any insight you could provide would be great!


    1. Hi,

      Just to confirm you have installed SQL 2012 and configured a static tcp port 1433. You can connect to the port using telnet.

      Do you see the test successful when setting up the SQL Server ODB Data Source. It does sound like there is a permissions issue, try adding the RDCB to as a local administrator on the SQL Server and Vice Versa.

      Let me know how you get on and if you require some assistance.

      Best Regards,

  3. Hi Ryan,

    I am totally blocked about this, I followed the tutorial sql ports open, successfully connected to the SQL and database trough ODBC, I go the sql client installed confirmed by regedit, successfully connected to the sql server using the sql server management studio, the client is 2008R2 so will be version 10.0, basically followed your tutorial to the point and still having the error window:

    “The database specified in the database connection string is not available from the RD…”

    I reviewed the logs on the sql server agent and the following message is repetitive:

    “login failed for user domain\server$ reasonn token-based server access validation failed with an infrastucture error check for previous error [client: serverip]”

    “Error 18456 severity 14 state 11”

    Any given help will be very much appreciated


  4. Hi,
    Very informative article. I have to deploy windows 2012 remote desktop services at a customer in HA mode. The customer has 1500 users and my question is that will the SQL Express Edition be enough for connection broker HA and secondary how can i configure web access role HA.

    1. it all depends on connections, but i would recommend that you look at using SQL standard for this size of deployment. you will need to use DNS or a Load Balancer to make RDWeb HA

      1. Again thank you for your reply. One last thing can i use windows network load balancing for RDWeb ??

  5. Hi Ryan, Nice Article.

    just want to know how we check the data in the SQL server, like if any user save the data in the Desktop, will it save in SQL database, if yes than how can we check this??

  6. I have a customer that has 300 users do you think SQL express will work in this environment or should we use SQL standard? When using HA on the connection brokers. I understand with using express there are no always on functions.

    1. Hi Mark,
      Using SQL Express is used quite often but you do need to ensure that your design can support this. the RDCB DB is light weight but ensure you size correctly. Secondly Express does not support always on features. You will need to leverage features like Hyper-v Replica or other technologies to provide HA / resilience / redundancy.

  7. Ryan, first of all, thanks a lot for you blog. It’s awesome! I built an entire RDS farm using your posts, and it server 200+ clients with 20+ apps published.

    I used this article to build RDS HA, and it worked like a charm. The only thing I don’t understand is why we need the last part with SQL Client configuration. It is not needed. All you need is to have the native client installed on each broker. Configuring the DSN is not needed at all.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Create a website or blog at

Up ↑

%d bloggers like this: