Encrypted data support

Check how each source and mode handles encryption at rest, encrypted columns and connections, and how SQL Flow encrypts its own traffic and data.

Encryption affects SQL Flow in two places: what it can read from an encrypted source, and how it protects the data it moves. This page sums up both. The capture-mode pages hold the details, the exact refusal messages and the setup steps.

Sources

SQL Server and Azure SQL

• TDE. DirectLog and Reconstruction read the raw transaction log, which TDE encrypts on disk, so they refuse a TDE database. They also refuse while TDE is being switched off and log files written under TDE are still active. For a TDE database, choose Polling or Native CDC, which read the log through the engine.

• Always Encrypted. Every mode refuses a selected table with an Always Encrypted column:

  always_encrypted_column: SQL Server capture refuses selected column <schema>.<table>.<column> because it is Always Encrypted; decryption and ciphertext pass-through are not supported.

  DirectLog alone can copy the ciphertext unchanged into a SQL Server or Azure SQL target that uses the same column encryption keys. Set alwaysEncrypted=ciphertext on the source and the target with the CLI. The copy isn't available over relay. See DirectLog: Always Encrypted.

• Connections. For an on-premises server with a self-signed certificate, set Encryption to Prefer: the connection stays encrypted, but the certificate isn't checked. A host name under .database.windows.net is always validated, also under Prefer.

Oracle

DirectLog has no decryption step, so it refuses a selected table in a TDE-encrypted tablespace, with encrypted columns or with encrypted SecureFile LOBs, before the initial load. Capture those tables with LogMiner, and keep the database's TDE keystore open while the pipeline runs: with it closed, capture stops by name.

Under Require (default), the agent checks that the server certificate's DN matches Server certificate DN, which the connection form requires. Wallet path on agent is optional and supplies only a client certificate. See Oracle: Connection encryption.

PostgreSQL and MySQL

Both engines decode their own log before they send it, so encryption of the server's storage doesn't change what SQL Flow receives. Values that an application encrypts before it writes them are copied as stored.

Under Require (default), the agent validates the server certificate against the agent host's trusted root certificates. For a private certificate authority, install its certificate on the agent host, or name a PEM file that holds it with the connection option sslRootCertificate, set with the CLI.

Sybase ASE

DirectLog and Reconstruction read raw device bytes and have no decryption step, so both refuse an encrypted database at start, and a selected table with an encrypted column before the initial load. Leave such tables out of the pipeline. Under Require (default), the agent validates the server certificate and host name against the agent host's trust store, or against the PEM file in Trusted certificate file on agent. See Sybase ASE: Encrypted data.

Targets

To encrypt into Always Encrypted columns, set alwaysEncrypted=encrypt on the target and give the apply agent access to the column master key, in a Windows certificate store or Azure Key Vault. Encrypted columns need ordered, row-by-row apply. See SQL Server: Always Encrypted targets.

Connection encryption

Every SQL Server, PostgreSQL, MySQL, Oracle and Sybase ASE connection, source or target, has an Encryption setting in the connection form (--encryption with the CLI, option encryption):

• Require (default) encrypts the connection and validates the server certificate. Use it for every production server.
• Prefer doesn't validate the certificate. For PostgreSQL, MySQL, Oracle and Sybase ASE, it can also connect unencrypted when the server offers no TLS or no TLS setting is given. Choosing it shows a warning in the form, and the pipeline's Source and Target cards show Encryption: Prefer.

Azure SQL and SQL database in Fabric connections always validate the server certificate, so they have no setting, and an encryption option is refused for them. No process-wide setting relaxes certificate validation.

A connection saved with the retired option trustServerCertificate or sslMode shows Retired option, edit and save on the Connections page; open it, choose Encryption and save it. See Connections commands: Encryption.

SQL Flow's own encryption

SQL Flow encrypts what it moves and stores, with no setting that turns this off:

• Its own connections, between the control plane, agents, log brokers and relay agents, use HTTPS or TLS, with each certificate pinned to the control plane's authority or to the exact peer.
• Every agent's spool, including large values it keeps on disk, is encrypted with AES-256-GCM under a key that only that agent's data directory holds. A spool copied to another machine can't be decrypted.
• Database passwords that you enter are stored encrypted on the control plane. Secret references keep them on the agent host only.

See Security considerations for the details.

See also

• Security considerations
• SQL Server sources
• Oracle sources
• SQL Server target
• Diagnostics for support