Connect SQL Server to Exasol Database using Linked Server

SQL Server database administrators and developers can connect Exasol databases by creating Linked Server using Exasol ODBC driver. SQL Server Linked Servers provide an easy way of connecting remote data sources including Exasol Data Warehouse databases by using ODBC connection. This SQL database tutorial shows where to download Exasol ODBC Driver, how to install Exasol ODBC driver and create Linked Server object which connects these two data platforms.

To summarize, in this SQL Server tutorial, I will share Exasol ODBC driver download and installation details. Then SQL programmers will create Linked Server for accesssing Exasol Data Warehouse cluster.

Download Exasol ODBC Driver

The Exasol database which is being used as central Data Warehouse in my company's AWS Data Lake account is running version EXASolution 6.2.1
Data administrators can check the Exasol database version using EXASolution browser based GUI for DWH management.

After logging to EXASolution GUI application, go to Software Configuration page.
On Versions tab, Exasol database version can be found as seen in following screenshot of the EXASolution browser based management tool.

check Exasol database version on EXASolution GUI

Now SQL Server system administrators can go to Exasol portal for downloads section and by switching the Exasol version, they can find the relevant software downloads for their Data Warehouse platform version.

download

For example, here is the link for Exasol Download Section for Version 6.2.1
This section contains software downloads for Exasol client tools, JDBC and ODBC drivers for Exasol and other download packages and SDKs for Exasol database administrator and developers.

You can download Exasol ODBC Driver for Windows, EXASOL_ODBC-6.2.1-x86_64.msi installation file at referenced URL. It is also possible to download Exasol ODBC drivers for MacOS and Linux, etc on the same page.

Launch Exasol ODBC driver installation file EXASOL_ODBC-6.2.3-x86_64.msi

install Exasol ODBC Driver on Windows

ODBC Data Source Creation for Exasol Database

After ODBC driver for Exasol is installed on the server, you can check ExaSolution Driver at ODBC Data Source Administrator tool under Drivers tab.

ExaSolution Driver at ODBC Data Source Administrator

Now switch to System DSN tab. Then click on Add button to start ODBC data source creation.
Select EXASolution Driver from the list of drivers.

create DSN for Exasol database

After EXASolution Driver is selected, fill following connection properties for a successfull Exasol ODBC configuration.

create Exasol ODBC configuration

In Data source name, type a descriptive name to your Exasol database connection.

Connection string is in format like:
10.151.70.26:8563
Or if you have multiple nodes within the Exasol Data Warehouse cluster, you can use connection string syntax:
10.151.70.26,10.151.70.64:8563

Provide a valid User name and Password for authentication to target Exasol database.

If possible, you can set the Default schema for your connection.

Exasol ODBC connection string properties

Press "Test connection" button and if it returns successfull message, click OK to save ODBC connection configuration.

Create SQL Server Linked Server to Exasol Database

After ODBC DSN is created, SQL Server database administrators can create Linked Server to Exasol database using the system DSN for ODBC connection.

Launch SQL Server Management Studio and connect to the target SQL Server instance.
Linked Server which have been created previously can be displayed under the node SQL Server > Server Objects > Linked Servers within "Object Explorer" window.

create new Linked Server for Exasol database on SQL Server

On "New Linked Server" dialog screen on SQL Server, fill following properties on connection definition screen:
Linked server: Type a descriptive and short name to the Exasol Linked Server
Server type: Choose option "Other data source" instead of "SQL Server" option
Provider: Select Microsoft OLE DB Provider for ODBC Drivers from dropdown list
Product name: Type EXASOL for product name
Data source: Type the name you have given for the ODBC data source name for Exasol ODBC connection configuration in previous step.
Provider string: Type MSDASQL for the provider string

I have marked the fixed values for an Exasol Linked Server connection with red boxes.

SQL Server Linked Server configuration for accessing Exasol database

On Security tab, select option "Be made using this security context" and provide Remote login and Password for a valid Exasol database login.

Linked Server login credentials for Exasol database

On Server Options make sure that the RPC options are set to True.
By default, RPC and RPC Out options are "False"; change to True

SQL Server Linked Server RPC configuration

Click OK button to complete SQL Server to Exasol database Linked Server connection.
If a problem occurs during connection, an error message will probably be displayed at this step.
It is also possible to test the Exasol Linked Server connection as follows:

test Exasol Linked Server connection

Sample SQL Query for Exasol Linked Server Connection

Here is a SQL query that can be executed on Exasol database from SQL Server over Linked Server connection.


declare @sql nvarchar(max)

set @sql = N'select sql_text from EXA_STATISTICS.EXA_USER_PROFILE_LAST_DAY;'

exec (@sql) AT [EXASOL]

Code

If the RPC connection properties are not set to "True", SQL developers can experience following error:
Msg 7411, Level 16, State 1, Line 3
Server 'EXASOL' is not configured for RPC.

SQL query on Exasol database using SQL Server Linked Server

I hope this SQL Server tutorial is useful for database developers who want to create Linked Server to connect Exasol Data Warehouse databases.