Configuration and Operation¶

Proxy Config Manager¶

This is a web interface application that is part of the delivered package and is intended to help to configure values on config.properties file -- sensitive values are managed directly using the CipherTrust Manager with the domain credentials --, by providing a web interface it aims to prevent writing typos or bad values on such file.

Open the Proxy Config Manager web interface¶

To access the Proxy Config Manager web interface, you have to open the Tomcat Manager at http://localhost:8080/manager

Tip

Provide the manager-gui admin credential set at tomcat-users.xml

Warning

Remember to enforce the security of credentials at tomcat-users.xml by configuring an external AD/LDAP.

Click on the link "/proxy-config-manager" and then provide the password for the proxyadmin account that is in tomcat-users.xml

Explanation of each parameter¶

Parameter	Description
`SQL_SERVER_HOST`	SQL Server host/IP where the data will be encrypted.
`REMOTE_PORT`	SQL Server port to which BOP/BIP Proxy SQL forwards packets.
`LOCAL_PORT`	Listener port exposed by the proxy to SQL clients.
`JDBC_URL`	DB JDBC URL for asynchronous insertions (credential must allow inserts/updates).
`DB_USER`	Database username used by the JDBC connection.
`CADP_PROPERTY_FILE`	Filesystem path to the CADP property file.
`CADP_CREDENTIALS.user`	CADP credentials username (e.g., `domain\|org\|realm\|user`).
`CADP_CREDENTIALS.password`	CADP credentials password.
`CADP_KEK_ID`	CADP Key Encryption Key identifier (e.g., `sqlproxykey`).
`ENCRYPT`	`YES` or `NO`. Default = YES if incorrect/omitted. If `YES` → all `TABLEFIELDSTOPROTECT` entries are encrypted; if `NO` → all are decrypted.
`ENCRYPT_MODE`	`NORMAL` or `FPE` (uppercase). With BouncyCastle, only `FPE` is supported.
`ENCRYPTUSERALLOWED`	List of users allowed to encrypt (e.g., `produccion#GoDynasty707`).
`DECRYPTUSERALLOWED`	List of users allowed to decrypt (e.g., `produccion#GoDynasty707`).
`TABLEFIELDSTOPROTECT`	Tables/columns to protect. Format: `<schema>.<table>.<column>.<type>` separated by `#`. Type: `A` = Alphanumeric, `N` = Numeric.
`HTTP_SRV_PARAM_UDATES_PORT`	Tiny HTTP server port for in-memory parameter updates.
`FILE_CHECK_UNENCRYPTED_DATA`	File path used to check if a table contains unencrypted data.
`LIVE_CHECK_UNENCRYPTED_DATA`	Start a background thread to periodically check for unencrypted data (`TRUE`/`FALSE`).
`LIVE_CHECK_FILE_UNENCRYPTED_DATA`	File path used by the live check process for findings.
`LIVE_CHECK_TIMER`	Interval (ms) for periodic unencrypted data checks.
`LIVE_BATCH_SIZE`	Records per iteration in the live checker.
`PARAMETER.KEY`	Name of the parameter key used to decrypt passwords/other sensitive values.
`MAXIMUMPOOLSIZE`	HikariCP: maximum SQL connection pool size.
`MINIMUMIDLE`	HikariCP: minimum number of idle SQL connections.

Warning

To take effect any change made, Tomcat restart is necessary, except for the parameters of the next section

List of parameters with immediate change effect¶

Following parameters once change takes effect

Parameter	Effect
`DECRYPTUSERALLOWED`	Adding/removing users applies immediately. Removed users lose clear-data visibility at once.
`ENCRYPTUSERALLOWED`	Adding/removing users applies immediately. Removed users can no longer run direct queries that trigger encryption.
`TABLEFIELDSTOPROTECT`	Adding new `<schema>.<table>.<column>.<type>` entries starts encrypting those fields immediately.
`ENCRYPT`	Switching YES → NO triggers immediate decryption of protected fields; NO → YES triggers immediate encryption.

Proxy Config Manager -- application.properties file¶

The file application.properties located in \<TOMCAT PATH>webapps\proxy-config-manager\WEB-INF\classes\, below the table of parameters and explanation of each one

Property	Description
server.port	Default port for serving the Config Manager web application.
spring.thymeleaf.cache	Controls HTML template caching. Set to `false` for dynamic reloading during development. (Do not modify in production unless necessary.)
app.config.path	Absolute path to the `config.properties` file used by the BIP SQL Proxy. Updates made through the Config Manager UI are written to this file.
app.update.enabled	Enables or disables on-the-fly parameter updates that take effect immediately without restarting the proxy.
app.update.url	Local URL endpoint exposed by the BIP SQL Proxy to receive live configuration parameter updates. (Managed locally only.)
app.update.connectTimeout	Connection timeout (in milliseconds) for parameter update requests sent to the SQL Proxy.
app.update.readTimeout	Read timeout (in milliseconds) for parameter update responses.
ciphertrust.cdsm.baseUrl	Base URL for the CipherTrust Data Security Manager (CDSM). (Reserved for future implementation of KMS-managed secrets.)
ciphertrust.cm212.baseUrl	Base URL for CipherTrust Manager (CM 2.12 or later). (Future support for centralized key management.)
ciphertrust.http.connectTimeout	Connection timeout (in milliseconds) when connecting to CipherTrust Manager APIs.
ciphertrust.http.readTimeout	Read timeout (in milliseconds) for responses from CipherTrust Manager APIs.
----

Sensitive parameters management¶

There are some sensitive parameters handling that must be managed through the KMS (CipherTrust Manager) -- created during the installation stage --, which are DB_PASSWORD, CADP_PASSPHRASE_CACHE_KEY, CADP_IV and TWEAK.

Modifying sensitive parameters¶

Currently CipherTrust Manager secret vault does not allow modification of the value -material- of a secret, BIP Proxy SQL only reads the secret version-0, because that the procedure to update the value -material-, is to delete the secret and then create again.

The above four secrets will be created in the API Playground using the bellow instructions

Delete the secret that will be created with the new value¶

In CipherTrust Manager web interface go to "Keys"
Select the three dots (...) to the right of the secret that will be deleted, click on "Delete"

Tip

Do the same with the other secrets that have sensitive value if an update is needed

Re-create the secret¶

Go to API Playground and in the left search box write \"secrets\"
Click under the Section \"Secrets\" in the option \"create\" under /v1/vault/secrets
In the center area of the page paste the below json

{
  "name": "DB_PASSWORD",
  "dataType": "seed",
  "material": "password123!"
}

Info

Do the same for the other three parameters.

Warning

Not adding the parameters will have the effect of BIP SQL server will not be able to start the SQL Pool threads and the encryption will no work.

Assign permissions to use the secret objects to the group "Key Users" (where APP user belongs to)¶

Tip

Go to "Keys" and click on each of the secrets objects name recently created, go to "KEY ACCESS" area, in tab "General" area select the toggle on the right "Show All Groups" and in the search box above write "Key Users" and check the right check button called "All" and press the blue button "Update" and move to the left the toggle button, that way, both Admin and "Key Users" group has access to the Secret.

Initial Memory Size for Tomcat¶

By default, the compressed Tomcat deliverable has set the below heapsize

-Xms1g -Xmx4g

If you need to increase the memory availability for Tomcat when running the BIP Proxy SQL then follow the following instructions:

Edit the file setenv.bat located at \<TOMCAT PATH>\apache-tomcat-11.0.9\bin\, and set the proper value that meets your system

set "CATALINA_OPTS=%CATALINA_OPTS% -Xms2g -Xmx8g -XX:+DisableAttachMechanism -Djdk.attach.allowAttachSelf=false -Dcom.sun.management.jmxremote=false"
set "CLASSPATH=%CATALINA_HOME%\lib\tomcat-juli.jar;%CLASSPATH%"

Restart Tomcat.

Logging & Diagnostics Codes (BIP SQL Proxy)¶

Use this table to interpret proxy logs. Columns map to your log fields: ID, JAVA LEVEL (logger level used in code), LEVEL (human-friendly), CATEGORY (module), CONCEPT (what’s being logged), VALUE (main payload), SQL User (if present), and an Explanation.

Note: Items marked “debug only / not for production” should be enabled only during troubleshooting to avoid leaking sensitive data.

ID	JAVA LEVEL	LEVEL	CATEGORY	CONCEPT	VALUE	SQL User	Explanation
ENC-001	FINER	DEBUG	ENCRYPTION	Encrypted table found in select query	Table name	(if applies)	Shows table name that has encryption applied.
ENC-002	FINEST	DEBUG	ENCRYPTION	Column value for encrypt	Column value for encrypt	(if applies)	Debug only: shows column name/value to be encrypted. Do not enable in production.
ENC-003	SEVERE	ERROR	ENCRYPTION	Encryption for querying error	Short Java exception	(if applies)	Encryption of a value used in a `WHERE` failed. No value is shown; diagnose with DBA using SQL tool and Tomcat logs.
ENC-004	FINEST	DEBUG	ENCRYPTION	Modified SQL query with encrypted data in WHERE	Query modified	(if applies)	Debug only: shows modified SQL (encrypted values in WHERE). Do not enable in production.
ENC-005	FINER	DEBUG	ENCRYPTION	Encrypted table found in update	Table name	(if applies)	Shows table name that has encryption applied.
ENC-006	FINEST	DEBUG	ENCRYPTION	Modified UPDATE with encrypted data in WHERE	Query modified	(if applies)	Debug only: shows modified UPDATE with encrypted WHERE.
ENC-007	FINER	DEBUG	ENCRYPTION	Encrypted table found in update	Table name	(if applies)	Shows table name that has encryption applied.
ENC-008	FINEST	DEBUG	ENCRYPTION	Modified DELETE with encrypted data in WHERE	Query modified	(if applies)	Debug only: shows modified DELETE with encrypted WHERE.
ENC-009	FINER	DEBUG	ENCRYPTION	Encrypted table found in RPC call	Table name	(if applies)	Shows table name that has encryption applied in RPC call.
ENC-010	SEVERE	ERROR	ENCRYPTION	Duplicate violation (clear detected)	N/A	N/A	While encrypting, clear data duplicates an already-encrypted value. Remove clear duplicates; review insert/update flow with DBA.
ENC-011	SEVERE	ERROR	ENCRYPTION	User not allowed to decrypt data	DB username	(if applies)	User attempted to decrypt data (SELECT/UPDATE/DELETE) without permission.
ENC-012	WARNING	ERROR	ENCRYPTION	Not unique key found in table for encryption	table.schema.column	N/A	Table intended for numeric encryption lacks PK or identifiable index—encryption halted.
ENC-013	WARNING	ERROR	ENCRYPTION	Invalid crypto provider selected — returning clear values	Provider name	N/A	Invalid crypto provider; must be Thales CADP or Bouncy Castle. Review configuration.
LIC-001	SEVERE	ERROR	LICENSING	Licensing verification failed	Specific object error	(if applies)	One of: unexpected algorithm / license expired / missing `fp_fields` / bad signature.
LIC-002	SEVERE	ERROR	LICENSING	License expiration	License will expire in < N days	(if applies)	Critical threshold (≤ 5 days remaining).
LIC-003	WARNING	WARNING	LICENSING	License expiration	License will expire in < N days	(if applies)	Early warning threshold (≤ 10 days remaining).
PRX-001	FINER	DEBUG	PROXY ACTIVITY	SQL BATCH 0x01	OutboundChannel id	(if applies)	Batch packet `0x01` detected.
PRX-002	FINER	DEBUG	PROXY ACTIVITY	PRE-LOGIN DATA	DB username	N/A	Pre-login detected; shows username.
PRX-003	FINE	DEBUG	PROXY ACTIVITY	Closing connection	Client connection closed	(if applies)	Client closed TCP connection.
PRX-004	FINEST	DEBUG	PROXY ACTIVITY	Successful login	DB username	(if applies)	TDS login success detected.
PRX-005	SEVERE	DEBUG	PROXY ACTIVITY	Login error	DB username	N/A	TDS login error detected.
PRX-006	FINEST	DEBUG	PROXY ACTIVITY	Property parameter updated	List of parameters changed	N/A	Notification of parameters updated via proxy-config-manager.
PRX-007	INFO	INFO	PROXY ACTIVITY	Fetching authenticated key from KMS with BC FIPS mode ON	Fetching key OK	N/A	Key fetched from KMS for Bouncy Castle use.
PRX-008	SEVERE	ERROR	PROXY ACTIVITY	Fetching authenticated key from KMS	Error + exception message	N/A	KMS key fetch failed—check connectivity and key presence.
PRX-009	INFO	INFO	PROXY ACTIVITY	Update server port service started	Port number	N/A	In-memory parameter update service is running.
PRX-010	SEVERE	ERROR	PROXY ACTIVITY	Update server not started	Exception details	N/A	Update service failed—port in use or configuration error.
PRX-011	INFO	INFO	PROXY ACTIVITY	Loading sensitive parameters from KMS	DB_PASSWORD / CADP_PASSPHRASE_CACHE_KEY / CADP_IV / TWEAK loaded	N/A	Sensitive parameters loaded from KMS successfully.
SQ-001	FINEST	DEBUG	SQL DATA	Extra treatment	SQL content	(if applies)	Query with transmission errors detected; attempting fix.
SQ-002	FINEST	DEBUG	SQL DATA	Treatment executed	SQL content	(if applies)	Treatment action applied (e.g., removed corrupted character).
SQ-003	FINER	DEBUG	SQL DATA	Treatment result succeeded	SQL content	(if applies)	Treatment succeeded and validated.
SQ-004	FINER	DEBUG	SQL DATA	Treatment succeeded with LevenshteinDistance	SQL content	(if applies)	Treatment validated using Levenshtein distance.
SQ-005	FINER	DEBUG	SQL DATA	Treatment not succeeded	SQL content	(if applies)	Treatment failed after validation.
SQ-006	FINEST	DEBUG	SQL DATA	Select query incoming	SQL content	(if applies)	Debug only: shows incoming SELECT query.
SQ-007	FINEST	DEBUG	SQL DATA	Columns in TDS ColumnMetadata	Column names detected	(if applies)	Shows column names in resultset `ColumnMetadata`.
SQ-008	FINEST	DEBUG	SQL DATA	Positions (columns) in ColumnMetadata to encrypt	Column numbers	(if applies)	Shows column positions slated for encryption.
SQ-009	FINEST	DEBUG	SQL DATA	Show original query with data encrypted	SQL content	(if applies)	Debug only: full original query that returns/contains encrypted data.

Production logging hygiene

Avoid enabling any entries that print SQL text, modified queries, column values, or usernames in production environments. Use them only during incident debugging and roll back to minimal INFO/ERROR levels afterward.

In addition to file-based logging via apache-tomcat-11.0.9\conf\logging.properties, you can forward proxy logs over the network using Java’s built-in SocketHandler. This is handy when you want to stream diagnostics (e.g., FINER/FINEST) to a remote collector. Note that Java’s SocketHandler uses TCP, while classic syslog on port 514 is usually UDP—ensure your receiver is listening on TCP 514 or adjust the port accordingly. Enable high verbosity only during debugging and roll it back afterward to avoid exposing sensitive details.

Example: add a network socket handler and elevate specific classes temporarily

# ------------------------------------------------------------
# Handlers & global level
# ------------------------------------------------------------
handlers = java.util.logging.ConsoleHandler, java.util.logging.SocketHandler
.level = INFO
java.util.logging.ConsoleHandler.level = INFO

# ------------------------------------------------------------
# Socket handler (Built-in Java Syslog Alternative)
# ------------------------------------------------------------
java.util.logging.SocketHandler.level = FINEST
java.util.logging.SocketHandler.host = 192.168.0.213
java.util.logging.SocketHandler.port = 514
java.util.logging.SocketHandler.formatter = java.util.logging.SimpleFormatter

# ------------------------------------------------------------
# Raise verbosity for targeted components (DEBUG ONLY)
# ------------------------------------------------------------
jm.sq.pi.proxy.ENCRYPTION.level = FINER
jm.sq.pi.proxy.SQL.level        = FINEST
jm.sq.pi.proxy.LICENSING.level  = FINE

Production hygiene

FINEST/FINER may include SQL text or metadata during troubleshooting. Use them only for short windows, forward to a secured destination, and revert to INFO/WARNING/SEVERE when finished.