Connect SQL Server, OLE DB, and ODBC#

Reportworq reads relational databases through three built-in connectors in the datasource catalog: SQL Server, OleDB Provider, and ODBC Provider. All three take a connection string, expose tables, views, schemas, and columns, and back either a table pick or a raw SQL query in a data model and the =RWSQL worksheet function. This page helps you choose the right connector, lists what each one requires, walks through the shared connection editor, and gives an example connection string for each connector and for the most common databases behind them.

Two related connectors have their own pages: Connect SQLite for a file-backed SQLite database, and Connect CData sources for the API and SaaS systems (Salesforce, Jira, NetSuite, and others) that reach Reportworq through a CData driver.

Choose a connector#

The three connectors differ in what they can reach, what has to be installed on the Reportworq server, and where they run. The table below compares them.

Recommendation. For Microsoft SQL Server and Azure SQL, use the SQL Server connector: there is nothing to install, it runs on both platforms, and its connection string is the simplest. For any other database, use the ODBC Provider with the vendor's 64-bit driver; this is also the only generic route on a Linux server. Use the OleDB Provider only when a source is reachable solely through a Windows OLE DB provider, such as the Microsoft ACE provider for Excel and Access files or a vendor-specific provider, or when your organization standardizes on the Microsoft OLE DB Driver for SQL Server.

Before you begin#

• You are an administrator, working in Settings > Integrations > DATASOURCES. See Connect a data source for the general add-and-test flow.
• The database is reachable from the Reportworq server, not only from your workstation. Check the firewall and the port before you start.
• For OleDB Provider and ODBC Provider, the 64-bit provider or driver is installed on the Reportworq server. See Windows and Linux servers for what works where.
• You have a database login for Reportworq. A dedicated account with read permission on the tables you report on is the safest choice. For Windows or trusted authentication, the Reportworq service account must have the database permissions instead.
• You know the exact server name, instance or port, and database name. For ODBC, you also know the exact driver name or the System DSN name.

Configure the connection#

The three connectors share the same editor, so the procedure is the same. Only the connection string and, on the ODBC connector, one extra timeout field differ.

1. Add the SQL Server, OleDB Provider, or ODBC Provider connector and open its editor.
2. Keep Enable datasource connection selected.
3. Enter a Datasource Name. Report authors reference the connection by this name, for example in the connectionName argument of =RWSQL, so choose a durable name.
4. Enter the Connection String. Take a starting point from Example connection strings below. To insert a credential placeholder, select the %USERNAME% or %PASSWORD% link above the box; it copies the token to the clipboard.
5. Enter the login in the %USERNAME% Variable field and the password in the %PASSWORD% Variable field. Both fields are masked and cannot be revealed after you save.
6. Leave Validation Query at SELECT 1 unless the database rejects it. Oracle needs SELECT 1 FROM DUAL and IBM DB2 needs SELECT 1 FROM SYSIBM.SYSDUMMY1. Test connection runs this query.
7. On the OleDB Provider and ODBC Provider, set Limit Query Type to the row-limit dialect of the database: Top for SQL Server and Access, Limit for PostgreSQL and MySQL, or Fetch First for Oracle and IBM DB2. Do not select Unavailable; a connection set to it cannot import fields or preview. The native SQL Server connector does not read this setting, so leave it at Top. See Advanced Options and Limit Query Type.
8. If a query needs longer than 30 seconds, expand Advanced Options and raise Command Timeout. On the ODBC Provider only, Connection Timeout sets how long to wait for the connection to open.
9. Save, then select Test connection. A reachable, correctly configured database returns "Success." If the test fails, see Troubleshooting.

Keep credentials out of the connection string#

Do not type a username or password into the connection string. Put the placeholders %USERNAME% and %PASSWORD% in the string and enter the real values in the masked %USERNAME% Variable and %PASSWORD% Variable fields. Reportworq substitutes them when it opens the connection, so the secrets never appear in the visible string. The connection string and both variables are stored encrypted. If a variable field is empty, its placeholder is replaced with an empty value.

The same placeholders work in every keyword position, so User ID=%USERNAME%, Uid=%USERNAME%, and PRIV_KEY_FILE_PWD=%PASSWORD% are all valid.

Example connection strings#

Every example uses the credential placeholders. Replace the server, database, and path values with your own; keywords are not case-sensitive and each setting ends with a semicolon. Paste the string into the Connection String box as one line.

SQL Server#

The SQL Server connector opens the connection with the Microsoft SQL client (Microsoft.Data.SqlClient), so it accepts every keyword that client documents. Its strings start with Data Source=.

SQL authentication, the default database instance on the standard port:

Data Source=DBSERVER;Initial Catalog=Finance;User ID=%USERNAME%;Password=%PASSWORD%;

Windows (integrated) authentication. The connection runs as the Reportworq service account, so that account needs the database permissions; leave the two variable fields empty:

Data Source=DBSERVER;Initial Catalog=Finance;Integrated Security=True;

A named instance:

Data Source=DBSERVER\FINANCE;Initial Catalog=Finance;User ID=%USERNAME%;Password=%PASSWORD%;

An explicit port, which also avoids the SQL Server Browser service lookup a named instance otherwise needs:

Data Source=DBSERVER,1433;Initial Catalog=Finance;User ID=%USERNAME%;Password=%PASSWORD%;

A server that presents a self-signed or internally issued certificate. Use this on a private network only; the connection is still encrypted, but the server's identity is not verified:

Data Source=DBSERVER;Initial Catalog=Finance;User ID=%USERNAME%;Password=%PASSWORD%;TrustServerCertificate=True;

Azure SQL Database with a SQL login:

Data Source=tcp:<server>.database.windows.net,1433;Initial Catalog=<database>;User ID=%USERNAME%;Password=%PASSWORD%;Encrypt=True;

Azure SQL Database with a Microsoft Entra ID user. Put the user's sign-in address in %USERNAME% Variable and its password in %PASSWORD% Variable:

Data Source=tcp:<server>.database.windows.net,1433;Initial Catalog=<database>;Authentication=Active Directory Password;User ID=%USERNAME%;Password=%PASSWORD%;Encrypt=True;

Azure SQL Database with a Microsoft Entra ID service principal. Put the application (client) ID in %USERNAME% Variable and the client secret in %PASSWORD% Variable:

Data Source=tcp:<server>.database.windows.net,1433;Initial Catalog=<database>;Authentication=Active Directory Service Principal;User ID=%USERNAME%;Password=%PASSWORD%;Encrypt=True;

To change how long the connector waits for the connection to open, add Connection Timeout=60 (seconds) to the string; the SQL Server editor has no separate Connection Timeout field. The full keyword list is on Microsoft's SQL client connection-string reference, and the Entra ID options on Microsoft Entra authentication with the SQL client.

OleDB Provider#

The OleDB Provider connector passes the string to the OLE DB provider named in Provider=. That provider must be installed, in its 64-bit build, on the Windows server that runs Reportworq.

SQL Server through the Microsoft OLE DB Driver 18 for SQL Server, which registers as MSOLEDBSQL and does not encrypt by default:

Provider=MSOLEDBSQL;Data Source=DBSERVER;Initial Catalog=Finance;User ID=%USERNAME%;Password=%PASSWORD%;

SQL Server through the Microsoft OLE DB Driver 19, which installs side by side with 18 and registers as MSOLEDBSQL19. Version 19 encrypts by default, so add Trust Server Certificate=True when the server's certificate is not trusted, or Use Encryption for Data=Optional on a private network:

Provider=MSOLEDBSQL19;Data Source=DBSERVER;Initial Catalog=Finance;User ID=%USERNAME%;Password=%PASSWORD%;Trust Server Certificate=True;

The older Provider=SQLOLEDB and Provider=SQLNCLI11 providers are deprecated by Microsoft. Install the Microsoft OLE DB Driver for SQL Server and use MSOLEDBSQL or MSOLEDBSQL19 instead.

An Excel workbook through the Microsoft ACE provider. The path is on the Reportworq server, and HDR=YES treats the first row as column names; each worksheet then appears as a table named Sheet1$. This route is Windows-only; the dedicated Excel connector reads a workbook on Windows and Linux alike and also accepts a repository: path to a workbook shipped inside the repository:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Reportworq\data\Budget.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

An Access database through the same provider:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Reportworq\data\Finance.accdb;

A folder of delimited text files. Data Source is the folder, and each file is queried as a table, for example SELECT * FROM [sales.csv]. Read the result with =RWSQL so the report re-pulls the file on every run:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Reportworq\data\;Extended Properties="text;HDR=YES;FMT=Delimited";

The ACE provider ships with the 64-bit Microsoft Access Database Engine redistributable, which must be installed on the server. Depending on the installed version the provider registers as Microsoft.ACE.OLEDB.12.0 or Microsoft.ACE.OLEDB.16.0; use whichever name the server has.

ODBC Provider#

The ODBC Provider connector passes the string to the ODBC driver manager, which routes it to the driver named in Driver={...} or to the DSN named in DSN=. On Windows the driver name must match the Drivers tab of the 64-bit odbcad32.exe exactly; on Linux it must match the name registered in /etc/odbcinst.ini.

A 64-bit System DSN, the recommended form because the DSN can be tested in the operating system before Reportworq uses it:

DSN=FinanceWarehouse;Uid=%USERNAME%;Pwd=%PASSWORD%;

SQL Server through the Microsoft ODBC Driver 18. Version 18 encrypts by default, so include TrustServerCertificate=yes when the server's certificate is not trusted, or use Encrypt=no on a private network:

Driver={ODBC Driver 18 for SQL Server};Server=DBSERVER;Database=Finance;Uid=%USERNAME%;Pwd=%PASSWORD%;Encrypt=yes;TrustServerCertificate=yes;

PostgreSQL through the psqlODBC driver. On Windows the 64-bit driver registers as PostgreSQL Unicode(x64); on Linux it is usually PostgreSQL Unicode:

Driver={PostgreSQL Unicode(x64)};Server=dbhost;Port=5432;Database=finance;Uid=%USERNAME%;Pwd=%PASSWORD%;

MySQL through MySQL Connector/ODBC. The driver name carries the connector version, so match it to the installed driver:

Driver={MySQL ODBC 8.0 Unicode Driver};Server=dbhost;Port=3306;Database=finance;User=%USERNAME%;Password=%PASSWORD%;

Oracle Database through the Oracle ODBC driver installed with an Oracle client. The driver name is the Oracle home name, for example Oracle in OraClient19Home1. Set Validation Query to SELECT 1 FROM DUAL and Limit Query Type to Fetch First:

Driver={Oracle in OraClient19Home1};Dbq=//dbhost:1521/FINANCE;Uid=%USERNAME%;Pwd=%PASSWORD%;

IBM DB2 through the IBM Data Server Driver. Set Validation Query to SELECT 1 FROM SYSIBM.SYSDUMMY1 and Limit Query Type to Fetch First:

Driver={IBM DB2 ODBC DRIVER};Database=FINANCE;Hostname=dbhost;Port=50000;Protocol=TCPIP;Uid=%USERNAME%;Pwd=%PASSWORD%;

For Snowflake through its native ODBC driver, including the DSN and key-pair forms, see Connect Snowflake via ODBC.

More connection strings#

The examples above cover the common cases. For any other database, driver, or option, ConnectionStrings.com keeps a reference of connection strings for every major provider and driver, organized by database. The table below links straight to the pages that match the Reportworq connectors.

When you copy a string from a reference site, replace the literal username and password with %USERNAME% and %PASSWORD% before you paste it into Reportworq.

Windows and Linux servers#

Reportworq runs on Windows and on Linux, and the three connectors are not equally available on both. The table below shows what works where.

On Linux, the way you install the ODBC driver depends on how Reportworq was deployed:

• Tarball or VM install. Install the packages on the host, for example unixodbc plus Microsoft's msodbcsql18 for SQL Server or the distribution's PostgreSQL or MySQL ODBC package, then restart the Reportworq service. See Deploy on an Azure Linux VM for the install layout and Microsoft's Linux ODBC driver instructions for SQL Server.
• Docker image. The shipped image does not include unixODBC or any ODBC driver. Build a derived image from the Reportworq image that installs unixodbc and the driver you need, and deploy that image instead.

Windows (integrated) authentication works differently on the two platforms. On Windows the connection runs as the Reportworq service account, which must have permissions in the database. On Linux, integrated authentication needs a Kerberos configuration on the host, so prefer a SQL login or, for Azure SQL, Microsoft Entra ID authentication.

Advanced Options: timeouts and Limit Query Type#

The collapsible Advanced Options section exposes the timeouts:

• Command Timeout, in seconds, on all three connectors. It bounds each query, and defaults to 30. A value of 0 means wait indefinitely. Raise it for a long-running warehouse query; set a non-zero value wherever a runaway query should be bounded.
• Connection Timeout, in seconds, on the ODBC Provider only, defaulting to 15. It bounds how long the connector waits for the connection to open. The SQL Server and OleDB Provider connectors have no separate field; for SQL Server, use the Connection Timeout= keyword in the connection string.

Limit Query Type, in the main section of the editor, selects the row-limit clause the connector emits when a data model or preview caps the number of rows, and when a field import samples the first rows of a table. The native SQL Server connector applies the row cap with SET ROWCOUNT and ignores this setting; the table below applies to the OleDB Provider, ODBC Provider, and SQLite connectors. Choose the dialect of the database behind the connection:

The fourth value, Unavailable, is reserved. A connection set to it cannot import fields or preview, because every field import and preview applies a row cap and the connector has no clause to emit; the request fails with "Preview is unavailable because top/limit/fetch query is not supported". A wrong dialect fails with a syntax error the first time a row cap is applied, so set the value when you create the connection.

Query the source in a data model#

Once the connection tests, a model author builds views over it on the model's Connection tab, choosing a Query type:

• Table or View picks a table or view from a filterable list. Table mode can select many tables at once to create several views in one action. A table pick is schema-qualified (schema.table) whenever the provider reports a schema for the table, on every connector; file-based ACE sources such as Excel, Access, and text folders have no schema, so their tables stand alone.
• SQL Query writes raw SQL in the editor, with a Format action. A SQL script can reference fields from any tables in the datasource, and supports {{variable}} double-brace tokens. Preview it before applying it to the model.

Text variables in SQL need single quotes: a {{var}} is substituted as a raw text replace with no automatic quoting, so a value that must become a SQL string literal has to be wrapped, for example '{{state}}' resolving to 'Maine'. Numeric values are bare.

Report authors can also query the connection directly from a worksheet with =RWSQL; see Relational database functions.

Troubleshooting#

Find the symptom that matches what Test connection or a job reports, then read across to the cause and the fix.

Related pages#

• Connect a data source for the general add-and-test flow.
• Connect SQLite for a file-backed SQLite database.
• Connect Excel for an Excel workbook read through the cross-platform Excel connector.
• Connect CData sources for the API and SaaS systems reached through a CData driver.
• Connect Snowflake via ODBC for the Snowflake ODBC driver, DSN, and key-pair setup.
• Connector catalog for every connector Reportworq ships.
• Relational database functions for =RWSQL and the other worksheet functions that read these connections.

Going deeper. To curate columns into governed business fields, set filters, and publish a view for report authors, see Data models.