Skip to end of banner
Go to start of banner

Connecting Microsoft Query to a Hosted Synergetic Database

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

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.

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. Click Next.

    image-20240721-084646.png

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

    image-20240721-080704.png

  • 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.

    image-20240721-081125.png


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

    image-20240721-081446.png

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

    image-20240721-081646.png

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

    image-20240721-082300.png

  • 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:

    image-20240721-082457.png

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

  • Now go to Excel > Data tab > Get Data > From Other Sources > From ODBC

    image-20240721-082634.png

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

    image-20240721-082810.png

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

    image-20240721-083620.png

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

    image-20240721-084045.png

  • No labels