Skip to content

ODBC driver & DSN

A Data Source Name (DSN) holds the connection information required for a data client on Windows to connect with a data source – such the Compass or ELDB server. It specifies the ODBC driver to use and the server address. Both MS Access and Power Query (Excel and Power BI) use a DSN to connect to SQL Server.

Note: The DSN specifies the connection to the server. The same DSN can be reused in different applications and instances of an application that links to differing databases on the server. (eg MS Access files setup for differing ELDB databases can use the same DSN, amended within the MS Access instance to link to a specified database).

Install the ODBC driver for SQL Server

The ODBC Driver for SQL Server enables applications to connect and query data on a SQL Server. To check if it is installed on your PC:

Open the ODBC Data Source Administrator (64-bit) App by either

  • Control Panel > Administrative Tools > OBDC Data Source Administrator (64-bit)
  • Programs > Windows Administrative Tools > OBDC Data Source Administrator (64-bit)
  • Search for ODBC > OBDC Data Source Administrator (64-bit)

Select the Drivers tab and look in the list for ODBC Driver [xx] for SQL Server

If it is not listed, download and install the ODBC driver from:

https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server

Set up a User DSN

A [Machine] User DSN stores the connection information in the Windows Registry of the computer making it only available to the creating user and not able to be copied for one PC to another. For these reasons, it is best to set up a User DSN, rather than a System or File DSN.

In the ODBC Data Source Administrator (64-bit) app select the User DSN tab.

Click Add and in the Pop Up choose the ODBC Driver [xx] for SQL Server and click Finish.

In the new Pop Up, add the following information:

Name: - a suitable reference name eg "ELDB Server"

Description: a suitable description eg "ELDB Server connection"

Server: the endpoint address for the server eg "tcp:eldb.qmul-ceg.net"

Click Next

In the next screen, select With SQL Server authentication using a login ID… and enter your login ID and password.

Click Next.

For the next few screens, ODBC Driver 18 for SQL Server has a slightly different setup to ODBC Driver 17 for SQL Server and earlier. Please follow the appropriate instructions.

ODBC Driver 18 for SQL Server

Click Next.

Click Next again to move on 2 screens.

Tick Trust server certificate

Click Back to navigate to the previous screen.

Change the default database to the database you will usually use. You will get a list of databases present on the server to choose from. Ignore any databases with suffixes (eg eldb2020_DEV).

Note: this is the default database for the DSN. Other databases can be specified where it is used in an application.

Change the Application intent to READONLY.

Click Next to return to the Trust server screen.

Click Finish.

ODBC Driver 17 for SQL Server and earlier

Click Next.

Change the default database to the database you usually use . You will get a list of databases present on the server to choose from. Ignore any databases with suffixes (eg eldb2020_DEV). Change the Application intent to READONLY

Click Next.

Tick Use strong encryption for data and Trust server certificate

Click Finish.

Check the connection

For either Driver version, you should now reach a preview of the DSN configuration.

Click Test Data Source.

If the connection fails, check back through the settings and re-enter your password – the password needs re-entering every time the DSN is edited or reviewed.

Once the Test works, Click OK.

The newly created DSN should be listed in the User DSN list.

Click OK to close the window.

Backlinks:

None

This method requires that you have an ODBC DSN set up for the server for use in ../connection/clients/ms_access.md.

ELDB authentication

This method requires that you have an ODBC DSN set up for the server for use in ../connection/clients/ms_access.md.

Microsoft Access

Before continuing with this setup, make sure you have completed the ODBC driver setup and created a User DSN on your laptop or PC.

PowerQuery (Excel & Power BI)

Before continuing with this setup, make sure you have completed the ODBC driver setup and created a User DSN on your laptop or PC.