Skip to content

SQL Connector Security

What the SQL Connector's database account needs, how its connection is encrypted, and what an administrator who configures it is trusted with.

How each database's connection is protected A Microsoft SQL Server connection is encrypted with TLS by default, and fails rather than fall back to plain text. Its certificate is always validated, and a certificate from an authority JIM does not yet trust can be trusted from the card the failed connection test shows. It runs unencrypted only if Encrypt Connection is turned off. An Oracle Database connection uses Native Network Encryption by default (AES, with SHA-2 integrity checking), which needs no certificate. Over TCPS (TLS) instead, a certificate from an authority JIM does not yet trust can be trusted from the same card, by trusting the root of its chain. Its None setting sends data unencrypted, for a lab only. For both, the password is stored encrypted and never logged, and the account needs no db_owner, DBA or SELECT ANY TABLE. Microsoft SQL Server Oracle Database By default TLSnever falls back to plain text Native Network EncryptionAES, with SHA-2 integrity checking Certificate always validatedan unknown authority: trustit from the failed test's card none needed, except over TCPSTCPS: trust the chain's root from the card Unencrypted only with Encrypt Connection off None, for a lab only Both: the password is stored encrypted and never logged, and no db_owner, DBA or SELECT ANY TABLE is needed How each database's connection is protected A Microsoft SQL Server connection is encrypted with TLS by default, and fails rather than fall back to plain text. Its certificate is always validated, and a certificate from an authority JIM does not yet trust can be trusted from the card the failed connection test shows. It runs unencrypted only if Encrypt Connection is turned off. An Oracle Database connection uses Native Network Encryption by default (AES, with SHA-2 integrity checking), which needs no certificate. Over TCPS (TLS) instead, a certificate from an authority JIM does not yet trust can be trusted from the same card, by trusting the root of its chain. Its None setting sends data unencrypted, for a lab only. For both, the password is stored encrypted and never logged, and the account needs no db_owner, DBA or SELECT ANY TABLE. Microsoft SQL Server By defaultTLSnever falls back to plain text Certificatealways validatedan unknown authority: trust it from the failed test's card Unencryptedonly with Encrypt Connection off Oracle Database By defaultNative Network EncryptionAES, with SHA-2 integrity checking Certificatenone needed, except over TCPSTCPS: trust the chain's root from the card UnencryptedNone, for a lab only Both: the password is stored encrypted and never logged, and no db_owner, DBA or SELECT ANY TABLE is needed

Database account permissions

Create a dedicated database account for JIM and grant it only what the Run Profiles you configure will use:

  • For schema discovery and import
    SELECT on each table or view in the Object Types document, on each related table, and (for Change-Log Table Delta Imports) on each change-log table. JIM reads the database's own catalogue views, which show the account exactly the objects it can already read; no wider catalogue permission is needed.
  • For export
    INSERT, UPDATE and DELETE on each Object Type's table and each related table, in addition to SELECT.
  • For probing generated values
    SELECT on each table or view a generated value is exported to, which import already needs; on Microsoft SQL Server the same grant lets JIM read the column's collation from the catalogue. See Probing for values already in use.
  • Nothing else.
    JIM creates no objects and changes no schema. It does not need db_owner, DBA, or SELECT ANY TABLE.
Microsoft SQL Server: an import-only account
CREATE LOGIN jim_sync WITH PASSWORD = '<a strong password>';
CREATE USER jim_sync FOR LOGIN jim_sync;
GRANT SELECT ON SCHEMA::HR TO jim_sync;
Microsoft SQL Server: an account that also exports
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::HR TO jim_sync;
Oracle Database: an import-only account
CREATE USER jim_sync IDENTIFIED BY "<a strong password>";
GRANT CREATE SESSION TO jim_sync;
GRANT SELECT ON HR.V_EMPLOYEES TO jim_sync;
GRANT SELECT ON HR.EMPLOYEE_PHONES TO jim_sync;
Oracle Database: an account that also exports
GRANT SELECT, INSERT, UPDATE, DELETE ON HR.EMPLOYEES TO jim_sync;
GRANT SELECT, INSERT, UPDATE, DELETE ON HR.EMPLOYEE_PHONES TO jim_sync;

On Oracle, grant per object rather than SELECT ANY TABLE. The account then sees exactly what JIM should, and a schema import that reports a table "cannot be seen" means a grant is missing, not that something is wrong with JIM.

Encryption in transit

  • SQL Server connections are encrypted by default and fail rather than fall back to plain text. The server's certificate is always validated; JIM never accepts an unvalidated certificate.
  • Oracle connections use Native Network Encryption by default (AES, with SHA-2 integrity checking), or TLS over a TCPS listener if you choose it. Choose None only in a lab.
  • Nothing needs installing on the JIM host for either database, and JIM connects only to the host you name, so the connector works unchanged in an air-gapped deployment.

What an Oracle server needs for Native Network Encryption

Nothing, if its network settings are at Oracle's defaults: the server accepts encryption without requiring it, and JIM requires it, so the session is encrypted. JIM asks for AES256, AES192 or AES128 encryption with SHA512, SHA384 or SHA256 integrity checking, and refuses to connect rather than fall back to plain text.

The connection test fails if the server's sqlnet.ora:

  • sets SQLNET.ENCRYPTION_SERVER or SQLNET.CRYPTO_CHECKSUM_SERVER to REJECTED;
  • or limits SQLNET.ENCRYPTION_TYPES_SERVER or SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER to algorithms outside those lists, such as SHA1.

To make the server refuse any client that does not encrypt, set SQLNET.ENCRYPTION_SERVER and SQLNET.CRYPTO_CHECKSUM_SERVER to REQUIRED. JIM connects either way.

When the connection test fails on the certificate

For SQL Server, if the server presents a certificate JIM does not yet trust (one issued by an internal authority, or self-signed), the connection test fails and shows you the certificate: who issued it, what name it was issued for, its validity dates and its thumbprint, which check it failed, and the certificate chain JIM could find above it. Choose what to trust from the card: the root (recommended, since it survives the renewal of every certificate beneath it), an intermediate authority, or the server's own certificate, which needs trusting again when it is renewed. Any of them makes the connection work.

SQL Server sends only its own certificate, not the authorities above it. JIM fetches them from the download address the certificate names, where it names one, and stores any it had to fetch alongside the certificate you trust, so the connection does not depend on that address afterwards. Where the certificate names no address, the card offers the server's own certificate alone; to trust its authority instead, get the authority's certificate from whoever runs your PKI, add it under Admin > Certificates, and test again.

An expired certificate, or one issued for a different name from the host you connect to, is still refused; renew it, or connect by a name the certificate carries. So is a chain through a certificate authority that has expired or is not marked as one; that has to be fixed on the server.

For Oracle over TCPS, JIM shows the same detail and refuses a certificate issued for a different name in the same way, but only the root of the chain can be trusted: the Oracle driver accepts the chain only when it ends at a root JIM hands it, so trusting an intermediate or the server's own certificate would not make the connection work. The card still shows the whole chain, but marks the root as the one to trust and offers nothing else. Oracle usually sends its whole chain, root included; where it does not, get the root from whoever runs your PKI, add it under Admin > Certificates, and test again. Native Network Encryption needs no certificate at all, which is one reason it is the default.

Credentials and logging

The password is stored encrypted and is used only to open the connection. It does not appear in any log, Activity or configuration history, and the database driver's error messages never include it.

What you are trusting

The Object Types document, including any select statement, is administrator configuration: it decides which tables JIM reads and writes, just as a Synchronisation Rule decides what flows where. Everything JIM sends to the database as data is sent as a parameter, never pasted into a statement, and every table and column name is validated and quoted.