Connecting Microsoft Query to a Hosted Synergetic Database

Owned by Chris Savage
Last updated: Oct 31, 2024 by James Overell
4 min read

We are providing direct database connectivity over a whitelisted secure connection. For this secure connection to work, the SQL Server driver that is being used on the client machine must support Strict encryption mode.

Pre-requisites

  1. Whitelisted IP addresses (raise a case with support if required)

  2. ODBC driver 18 for SQL Server

  3. SQL user with appropriate permissions (individual or group, raise a case with support as required)

  4. Add a link to access MS Query in Excel

This is supported by ODBC Driver 18 for SQL Server.

Complete the following steps to set up MS Query over a secure direct database connection:

  • Provide Education Horizons with the IP addresses that will be connecting to your hosted database.

  • Education Horizons will whitelist these IP addresses and will provide you with authentication credentials.

  • On a machine that will be using the direct database connection and has has its IP address white-listed, go to ODBC Data Source Administrator. Add a new Data Source, select ODBC Driver 18 for SQL Server and click Finish.

    image-20240721-075521.png

 

  • Give your data source a name and for the Server, paste in the URL that you’ve been given. Note the comma before the port number. Click Next.

    image-20240815-124000.png

  • Select 'With SQL Server authentication…' and enter the Login ID and Password that you have been provided. Click Next.

 

  • Tick Change the default database to: and enter the database name that you have been provided. Don’t click on the down arrow as this will attempt to connect to the database server and will fail. Leave all other settings as their default value. Click Next.



  • Change Connection Encryption to Strict. Leave all other settings as their default value. Click Finish.

     

  • The following confirmation dialog will appear. Click on Test Data Source…

 

  • If the connection was successful, you will be presented with the following:

     

  • Click on OK and then OK again to save the new data source. Your data source should now appear in the list of User Data Sources:

     

The following instructions may differ depending on the version of Excel that you’re using.

Use of different tools:

MS Query (currently used in our training materials)

  1. Check the "All command in the Customize Ribbon?

  2. Click File > Option> Customize Ribbon> Select “All commands” 

  3. Search for “New Database Query” from the list, select it, and click on the “Add” Button to add under the “Get Data” in the Main Tabs

Power Query (optionally used by clients)

  • Go to Excel > Data tab > Get Data > From Other Sources > From ODBC

 

  • Select your Data source name from the list and click OK

     

     

  • Enter the same user name and password that you entered when creating the data source. Click Connect.

 

  • You should now be presented with the following page from which you can select database objects to construct your queries.