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: http://msdn.microsoft.com/en-us/library/ms190202.aspx 

SQL RDS1

SQL RDS2

SQL RDS3

SQL RDS4

SQL HA-RDCB6

SQL RDS6

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

SQL RDS7

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

SQL RDS8

Select default settings for the reporting services.

Configure security permissions

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

SQL RDS9

Add all connection brokers into the security Group

SQL RDS10

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

SQL RDS11

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

SQL RDS12

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

SQL RDS13

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

SQL RDS14

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

SQL RDS15

SQL RDS16

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.

http://www.microsoft.com/en-gb/download/details.aspx?id=29065

RD-HACB_SQLC1 RD-HACB_SQLC2

RD-HACB_SQLC3 RD-HACB_SQLC4

RD-HACB_SQLC5

RD-HACB_SQLC6

15 thoughts on “Configure SQL for RDS 2012

  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!

    Thanks

    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

    Juan

  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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s