---
title: "Salesforce (ODBC) Data Source Template"
slug: "salesforce-odbc-data-source-template"
updated: 2026-08-13T18:42:47Z
published: 2026-08-13T18:42:47Z
canonical: "docs.validatar.com/salesforce-odbc-data-source-template"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.validatar.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Salesforce (ODBC) Data Source Template

## Overview

The Salesforce (ODBC) data source template connects Validatar to a Salesforce org through a CData ODBC driver, which presents Salesforce as a SQL-92 database and translates queries to SOQL.

**Platform:** Salesforce (all editions with API access) **Connection Category:** Database **Connection Type:** ODBC **Driver:** CData ODBC Driver for Salesforce, 64-bit **Query language:** SQL-92

This provides three capabilities SOQL does not offer: genuine joins across objects, unrestricted aggregates, and transparent paging. A referential integrity check that requires three round trips through the REST API is a single statement:

```
select c.Id, c.External_Account_Id__c
from Validatar_Demo__c c
left join Account p on p.Id = c.External_Account_Id__c
where c.External_Account_Id__c is not null and p.Id is null
```

The trade-off is a driver installation, a license, and a Data Agent to host them. The [Salesforce (REST API) Data Source Template](/docs/salesforce-rest-api-data-source-template) requires none of those. [Salesforce: REST API or ODBC](/docs/salesforce-rest-api-or-odbc) compares the two.

## Requirements

| Requirement | Detail |
| --- | --- |
| Driver | CData ODBC Driver for Salesforce, 64-bit, installed on the Data Agent host |
| License | CData Desktop or Server license. A 30-day trial is available, and the driver is free for development use. |
| Runtime | Data Agent only. Validatar Cloud cannot host a third-party ODBC driver. |
| Salesforce | An External Client App configured for the OAuth JWT bearer flow |
| Host access | Local administrator on the Data Agent host, for a machine-wide installation |
| Catalog fields | The **Salesforce CRM Catalog Fields** marketplace item, applied before the first ingestion |

> **Note:** This template requires a Data Agent. A third-party ODBC driver is a native binary installed on a host, and the Validatar Cloud runtime does not host customer binaries. Salesforce connectivity without an agent is provided by the REST API template.

## Setting Up Salesforce Access

The Salesforce configuration is identical to the REST API template's, except that this template uses the JWT bearer flow exclusively. Steps 1, 2, and 3b of **Setting Up Salesforce Access** in the [Salesforce (REST API) Data Source Template](/docs/salesforce-rest-api-data-source-template) article apply.

That configuration produces three values used by this template: a consumer key, a pre-authorized username, and a `salesforce.pem` private key.

## Obtaining the Driver

The 64-bit Windows driver is available as a 30-day trial with no payment details required, and is free for development use.

- [Download the CData ODBC Driver for Salesforce](https://www.cdata.com/drivers/salesforce/download/odbc/)
- [Licensing and pricing](https://www.cdata.com/drivers/salesforce/order/odbc/)
- [CData driver documentation](https://cdn.cdata.com/help/RFH/odbc/)

License tier depends on deployment. In this configuration the driver is queried by a Windows service running as LocalSystem on the Data Agent host, which is the detail that determines whether a Desktop or Server license applies. Establishing this with CData before purchase avoids a reclassification at renewal.

## Installing the Driver

The driver must be installed as administrator on the Data Agent host so that it registers machine-wide.

### LocalSystem considerations

The Validatar Data Agent runs as the `LocalSystem` service account. Three consequences follow, each of which produces failures that present as authentication errors:

1. **A DSN-less connection string or a System DSN is required.** A User DSN created under an interactive login is not visible to the service. The connection string below is DSN-less and avoids the question entirely.
2. **The certificate must be readable by LocalSystem**, not stored in a user profile.
3. **The OAuth token cache must be writable by LocalSystem.** The CData default location is per-user, and the driver cannot persist its token there when running as a service.

```
New-Item -ItemType Directory -Force C:\ProgramData\CData
Copy-Item .\salesforce.pem C:\ProgramData\CData\salesforce.pem

icacls C:\ProgramData\CData\salesforce.pem /grant "NT AUTHORITY\SYSTEM:(R)"
icacls C:\ProgramData\CData /grant "NT AUTHORITY\SYSTEM:(OI)(CI)(M)"
```

### Connection string

```
DRIVER={CData ODBC Driver for Salesforce};
AuthScheme=OAuthJWT;
InitiateOAuth=GETANDREFRESH;
OAuthJWTIssuer={consumer key};
OAuthJWTSubject={pre-authorized username};
OAuthJWTCertType=PEMKEY_FILE;
OAuthJWTCert=C:\ProgramData\CData\salesforce.pem;
OAuthSettingsLocation=C:\ProgramData\CData\salesforce-oauth.txt;
UseSandbox=False
```

> **Note:** CData documentation describes creating a Connected App. Salesforce no longer permits new Connected Apps as of the Spring '26 release. An External Client App is required, and the driver has been verified to authenticate against one.

## Catalog Structure

The driver reports a single schema, `Salesforce`, which the template uses unmodified.

This differs from the REST API template, which derives `standard`, `custom`, and per-namespace schemas. The REST template is free to define its own schema names; here the schema must be one the driver recognizes, or schema-qualified references do not resolve. The standard-versus-custom distinction is preserved as the **Custom Object** catalog field.

## What's Included

### Catalog enrichment

The driver exposes Salesforce-aware metadata beyond a generic ODBC catalog:

| Catalog field | Driver source |
| --- | --- |
| Object label, API name, description | `DisplayName`, `TableName`, `Description` |
| Custom object | `IsStandardTable` |
| Queryable, createable, updateable | `IsQueryable`, `IsInsertable`, `IsUpdateable` |
| Field label, API name | `DisplayName`, `ColumnName` |
| Custom field | `IsStandardColumn` |
| Formula field | `IsCalculated` |
| External ID | `IsExternalId` |
| Required | `IsNullable`, `DefaultedOnCreate`, `IsCreateable` |

Column data types are read from `SalesforceType`, which reports Salesforce's own type names — `id`, `picklist`, `reference`, `currency` — rather than the driver's ANSI approximation. This is the same vocabulary the REST describe API returns, so a given column resolves to the same Validatar data type through either template.

Ingestion is filtered to queryable objects and excludes platform objects (`*Share`, `*History`, `*Feed`, `*ChangeEvent`, `*Tag`, `*Permission*`, `Apex*`, `Auth*`, `Flow*`, `Setup*`). On a reference Developer Edition org this reduces 1,333 objects to 799, and 23,412 columns to 14,845.

### Macros

Standard set: `object_data`, `row_count`, `row_count_filtered`, `list_of_objects`, `list_of_objects_in_a_schema`, `field_metadata_for_an_object`, `distinct_values_in_a_column`, `row_count_grouped_by_column`, `standard_profile_of_a_column`, `key_value_list`.

SQL-specific:

| Macro | Purpose |
| --- | --- |
| `sql_query` | Arbitrary SQL-92, including joins |
| `orphaned_lookups` | Child records whose reference has no matching parent, as a single anti-join |
| `cross_object_comparison` | Row counts across two objects |
| `duplicate_check_on_field` | `GROUP BY … HAVING COUNT(*) > 1` |
| `field_fill_rate` | Populated and empty counts for a field |
| `recently_modified` | Records modified since a given date |
| `null_report_all_fields` | Field inventory with type and nullability |
| `record_count_by_recordtype` | Record volume per record type |

## Data Profiles

Profiles are typed. Minimum and maximum are three distinct profiles by result type, so that numeric values sort numerically and dates render as dates.

Enabled by default: record count; null count and null percent; numeric minimum, maximum, mean, standard deviation, zero count, zero percent, negative count, and negative percent; string minimum, maximum, and lengths, with blank count and blank percent; date minimum and maximum.

Percent profiles are calculations derived from their prerequisite counts and the record count, rather than separate queries.

**Distinct count ships disabled.** The driver computes it client-side by retrieving rows, which on a large object can transfer the entire table across the REST API and consume the org's daily API allowance in a single profiling pass. It can be enabled for objects of known size.

Blank count is always zero. Salesforce normalizes empty text to null, so no blank value is distinct from a null.

> **Note:** The template uses `len()` for string length rather than `length()`. On the driver build tested, `length()` does not return an error — it terminates the host process with a stack overflow, which on a Data Agent host stops the agent service.

## Known Limitations

- **Geolocation fields are not exposed.** Measured on the reference org, the REST describe API reports 29 fields on a test object and the driver reports 28; the difference is a Geolocation compound field, which the driver does not expose at all, including its latitude and longitude components. Geolocation data requires the REST API template.
- **Record types.** The `record_count_by_recordtype` macro joins on `o.RecordTypeId`, a column that exists only on objects where record types are configured. On other objects the statement fails to parse with `Invalid column name 'RecordTypeId'`. The REST API template inspects the describe API first and returns an empty result instead; SQL provides no equivalent guard.
- **API consumption is determined by the driver.** The driver decides what to push down to SOQL and what to compute client-side by retrieving rows. `COUNT(DISTINCT …)` and unfiltered joins can be expensive.
- **Ingestion duration.** An unfiltered column scan takes approximately four minutes on a Developer Edition org; the filtered scan is proportionally faster.
- **Data Agent only**, as described under Requirements.
- **Field-level security applies.** The catalog reflects the visibility of the integration user.

## Verification

Verified against a live Salesforce Developer Edition org with the CData driver on a Windows Data Agent: 20 elements passed, 1 skipped, 0 failed.

The skipped element is `record_count_by_recordtype`. No object in the verification org has record types configured, so no case exists to assert against, and the SQL form provides no guard for that condition.

Where both Salesforce templates ship the same macro against the same fixture, both return identical results — 200 records, 5 category groups, 4 distinct categories, 5 duplicate email groups, 15 orphaned references, and 200 recently modified records. Agreement across two independent access paths is the primary evidence of correctness for both.