TLS Security

By default, Syntra ODBC communicates over unencrypted connections on localhost. For production deployments, especially when clients connect over a network, you should enable TLS encryption to protect QuickBooks data in transit.

Overview

TLS (Transport Layer Security) encrypts all traffic between clients and the Syntra server. When enabled, Syntra presents a certificate to connecting clients, and all SQL queries and results are encrypted.

Generating Certificates

Option 1: Self-Signed Certificate

For internal use, generate a self-signed certificate using OpenSSL:

openssl req -new -x509 -days 3650 -nodes \
  -out syntra-server.crt \
  -keyout syntra-server.key \
  -subj "/CN=localhost"

Option 2: Domain Certificate

For production environments with a domain name, use a certificate from a trusted CA (e.g., Let’s Encrypt) or your organization’s internal CA.

Configuring TLS in Syntra

Add the following to config.toml:

[tls]
enabled = true
cert_file = "C:\\ProgramData\\SyntraODBC\\certs\\syntra-server.crt"
key_file = "C:\\ProgramData\\SyntraODBC\\certs\\syntra-server.key"

Restart the Syntra service after making changes.

Client Configuration

ODBC

Add SSLMode=require to your connection string:

Driver={PostgreSQL Unicode};Server=localhost;Port=5433;Database=quickbooks;Uid=syntra;Pwd=yourpassword;SSLMode=require;

PostgreSQL URI

postgresql://qbconnect:yourpassword@localhost:5433/quickbooks?sslmode=require

Power BI / Tableau

Check the Require SSL checkbox in the PostgreSQL connection dialog.

Python (pyodbc)

import pyodbc

conn = pyodbc.connect(
    "DRIVER={PostgreSQL Unicode};"
    "SERVER=localhost;PORT=5433;"
    "DATABASE=quickbooks;UID=qbconnect;PWD=yourpassword;"
    "SSLMode=require;"
)

Node.js (pg)

const client = new Client({
  host: 'localhost',
  port: 5433,
  database: 'quickbooks',
  user: 'syntra',
  password: 'yourpassword',
  ssl: { rejectUnauthorized: false }  // Set to true with a CA-signed cert
});

SSL Modes

Mode	Encryption	Certificate Verification	Use Case
disable	No	No	Local development
prefer	If available	No	Default
require	Yes	No	Encrypted, no verification
verify-ca	Yes	CA only	Trusted CA
verify-full	Yes	CA + hostname	Full security

Certificate File Permissions

Ensure the private key file is only readable by the Syntra service account:

icacls "C:\ProgramData\SyntraODBC\certs\syntra-server.key" /inheritance:r /grant:r "NT SERVICE\SyntraODBC:(R)"

Troubleshooting

• “SSL not supported”: Ensure [tls] enabled = true is set in config.toml and the service was restarted.
• Certificate errors: Verify the certificate file paths are correct and the files are readable by the service.
• Self-signed certificate warnings: Clients will warn about self-signed certificates. Use sslmode=require (not verify-full) to accept self-signed certificates.