Exasol provides an ADO.NET Data Provider to connect .NET application to Exasol. The Exasol ADO.NET data provider supports the ADO.NET 4.0 standard.
The data provider is tested with the following applications:
- SQL Server Integration Services: SQL Server Integration Services are supported for Visual Studio 2010 and later versions. The SQL Server Integration Services work with the generic ADO.NET and ODBC drivers and don't need any extensions to be installed.
- SQL Server Reporting Services (Data Processing Extension) and SQL Server Analysis Services (DDEX provider and pluggable SQL cartridge) are installed for:
- Visual Studio 2012 (v 11.0) with SQL Server 2012 (v 11.0)
- Visual Studio 2013 (v 12.0) with SQL Server 2014 (v 12.0)
- Visual Studio 2015 (14.0) with SQL Server 2016 (v 13.0)
- Visual Studio 2017 (15.0) with SQL Server 2017 (v 14.0)
- Visual Studio 2019 (16.0) with SQL Server 2019 (v 15.0)
The DDEX provider for SQL Server Analysis Services in Visual Studio 2017 and 2019 (v 14.0 and 15.0) are installed in the EXAExtension.vsix. This is installed by the Exasol ADO.NET setup.
You have to close all Visual Studio instances before the extension is installed.
- You should have system administrator rights for the system where you are installing the data provider.
- Microsoft .NET Framework 4.6.1™ should be installed on your system.
The data driver is tested on the following operating systems:
- Windows 10 (x86/x64)
- Windows 8.1 (x86/x64)
- Windows 7, Service Pack 1 (x86/x64)
- Windows Server 2012 R2 (x86/x64) Windows Server 2012 (x86/x64)
- Windows Server 2008 R2, Service Pack 1 (x86/x64)
- Windows Server 2008, Service Pack 2 (x86/x64)
Download and Install the ADO.NET Data Provider
- Go to the Exasol Downloads page and from Download ADO.NET Driver section, download the ADO.NET driver.
- Run the executable file downloaded.
- Follow the setup wizard to complete the installation process.
The data provider is installed in the global assembly cache and registered in the global configuration file of the .NET framework.
An additional tool Data Provider Metadata View ™(DPMV) is also installed besides the ADO.NET driver. You can use it to check the connectivity and run simple metadata queries.
To use the data provider, do the following:
- Select the data provider by doing one of the following:
- Select the entry Exasol Data Provider from the list of installed data providers.
- Enter an invariant identifier that selects the Data Provider. The identifier for Exasol data provider is Exasol.EXADataProvider.
- Connect to Exasol using a connection string with all the required information. The connection string is a sequence of keyword and value pairs separated by semicolons. For example, host=192.168.6.11..14/72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30:8563;UID=sys;PWD=exasol;Schema=test.
Exasol Data Provider supports the following keywords.
|server or host||
Defines the servers, fingerprint, and the port of the Exasol cluster (for example, 192.168.6.11..14/72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30:8563). If host names are specified there, they can have DNS entries with multiple IP addresses.
Port of Exasol. This port is used if you did not specify any port within the parameter host.
user id, username, or uid
|password or pwd||Password|
Schema to be opened on login.
Settings for autocommit: ON or OFF.
Default value is ON.
Settings for the automatic encryption: ON or OFF.
Default value is ON.
Log file for the driver (for example, logfile='C:\tmp\ado.log'). The size of this log file can get huge, we recommend to switch on this option only to analyze problems.
SQL string which is executed directly after the connection is established. If an error occurs, the connection will be aborted.
Maximum time in milliseconds the driver will wait to establish a TPC connection to a server. This timeout is required to limit the overall login time especially in case of a large cluster with several reserve nodes.
Default value is 2000
Defines how many seconds a statement may run before it is automatically aborted.
Default value is 0 (unlimited)
Enables the user to execute queries even if the limit for active sessions (executing a query) has been reached. The allowed values are ON and OFF.
Default value is OFF
Only the SYS user can set the parameter.
superconnection should only be used in case of significant performance problems where it is impossible to log in and execute queries within a reasonable time. By that parameter you can analyze the system and kill certain processes which cause the problem.
The parameter enables the snapshot transaction in a connection. The default value for the parameter is OFF.
Set the parameter to ON to enable Snapshot Mode for system tables.
Specifies how ADO.NET driver will treat the server certificate for the connection. If the connection has VerifyServer the driver verifies the certificate in the SSL connection. VerifyNone establishes an SSL connection, but does not verify the server certificate. You can also specify a self signed certificate file name here for SSL verification.
If the SSL certificate is not verified or a client does not have the certificate, the connection fails. To overcome this problem, you could use a fingerprint in the connection string that allows the connection. For fingerprint based verification, the connection string is Server=exadb1.example.com/<fingerprint>;Port=8563. The fingerprint is automatically added to the database connection string when you upload a TLS certificate. For more information about TLS certificate upload, see Upload TLS Certificate.
If it is set to ON the driver will not use SSL to encrypt the client-server communication, instead it will use the ChaCha algorithm.
Specifies the authentication method for OpenID connections.
If the value for the parameter is accesstoken, the connection string will be "Server=<host>;Port=<port>;AuthMethod=accesstoken;PWD=<access_token>" and the password will be your OpenID Access Token.
If the value for the parameter is refreshtoken, the connection string will be "Server=<host>;Port=<port>;AuthMethod=refreshtoken;PWD=<refresh_token>" and the password will be your OpenID Refresh Token.
For more information, see Authentication using OpenID.
Includes a fingerprint. For example:
To disable TLS encryption, use NOCERTCHECK. NOCERTCHECK is case insensitive. It can occur after the host or port, or within the fingerprint argument. For example:
If the TLS validation fails and you trust the server, use the server's fingerprint.
During a query the server sends feedback to the client at set intervals. This feedback:
Default value is one second.
A feedback interval set too high may cause a long lag time before a command is canceled.
An example for Exasol Data Provider (written in C#) is given below.
using System.Collections.Generic; using System.Text;
static void Main(string args)
DbProviderFactory factory=null; try
factory = DbProviderFactories.GetFactory("Exasol.EXADataProvider");
Console.WriteLine("Found Exasol driver");
DbConnection connection = factory.CreateConnection();
Console.WriteLine("Connected to server");
DbCommand cmd = connection.CreateCommand();
cmd.Connection = connection;
cmd.CommandText = "SELECT * FROM CAT";
DbDataReader reader = cmd.ExecuteReader();
Console.WriteLine("Schema SYS contains:");
catch (Exception ex)
If the example is successfully executed, following output is displayed.
Found Exasol driver Connected to server Schema SYS contains: EXA_SQL_KEYWORDS, TABLE DUAL, TABLE EXA_PARAMETERS, TABLE ...
Exasol ADO.NET driver supports the following Schema Collections:
Exasol Data Processing Extension
The EXADataProcessingExtension implements interfaces for Microsoft SQL Server Reporting Services to create reports with Exasol. In the following example Visual Studio 2005 is used to create a report for the Reporting Services:
- Start Visual Studio 2005 and create a new report server project.
- Open the Project sheet explorer.
- Create a new data source (EXADataSource) and insert the connection string, user, and passwords.
- In the Project sheet explorer, add a new report using the wizard. When selecting the data source, choose the ExaDataSource that was previously configured.
- Publish the report as soon as it is ready. The functions are contained in Create menu.
- Do the following to open the report in your browser:
- Open a browser.
- Enter the address of the report server. For example, http://localhost/Reports
Check with your system administrator if you don't have permission to run the reports on the server.
Here is an output of the report in browser.