Transitioning from Direct SQL to Azure Hybrid Connection Manager

Owned by
Theresa Klunk (Deactivated)
Last updated: Jun 25, 2021 by
Kai Vedanayagam

At EdSmart, we have been working to improve the efficiency and reliability of our data import processes. We have developed new infrastructure that allows us to process data much faster, with improved error handling and retry capabilities. Furthermore, we now have the ability to pull data without needing schools to open an inbound firewall exception for direct SQL connections.

Schools are able to install a small Microsoft program, ‘Hybrid Connection Manager’ (HCM), which acts as a relay between their SQL server and EdSmart in the cloud. After adding a connection string to the program, supplied by EdSmart and containing a secret key for authentication, the program makes an outbound HTTPS call to talk to our infrastructure and establish a persistent connection. This means that any time EdSmart needs to call approved views on schools’ SQL servers, it can do so via this connection.  Inbound SQL port exceptions are no longer required, and the only firewall exception needed is outbound HTTPS calls from the machine on which HCM is installed.

This solution has been in production usage for several months, and we are now looking at transitioning schools with existing direct SQL connections over to our Hybrid Connection infrastructure. There is a small cost we need to pass on to cover the cost of using Microsoft HCM, currently set at $200 per annum per school.

 

What to do if you would like to use HCM:

  1. Download the HCM software from https://go.microsoft.com/fwlink/?linkid=841308 and install it on a computer that is always running, can call the internet over HTTPS, and is able to make calls to your SQL server. It is strongly recommended that the software not be installed on the SQL server itself. This is because many Student Management System vendors also install web sites and other software on their database servers, and making changes to the machine (especially modifying TLS settings, as required in the next step) could adversely affect that software.

  2. Use the IISCrypto tool available at https://www.nartac.com/Products/IISCrypto/ to apply best practice settings to the machine you have just installed HCM on, and to set that machine’s TLS level to 1.2 only.

  3. Determine the SQL Server machine’s fully qualified internal network name (e.g. sql01.exampleschool.internal). This cannot be just the NetBIOS name of the machine; the fully qualified name is required for the HCM software to resolve the server’s address on your network.

     

  4. Contact EdSmart Support when the above steps are complete, providing the SQL server machine’s fully qualified network name. We will provide you with a connection string to enter into HCM. We will then run a test import, and once successful, your ongoing daily & ad hoc imports will run over the new infrastructure, and you may remove the inbound firewall exception allowing incoming SQL connections from EdSmart.