Skip to main content

Database Client Tools — Connecting to SQL Backends

Document Type: Reference / Setup Guide | Audience: DTC Engineers (T2 / T3 / L3) | Last Updated: April 2026 | Version: 1.0

This book documents 853 tables and 17,624 columns of HaloPSA's SQL schema. To make use of that reference, an engineer needs to actually open the database and run queries. This page covers the access path — tools, connection patterns, credential handling — for HaloPSA MSSQL and the other SQL backends DTC engineers touch (dental client SQL instances, Postgres on thedock, occasional MariaDB).

This is engineer access, not application access. For application-side patterns (the API, the proxy), see HaloPSA API Reference (book 1298) and API Testing & curl Patterns at DTC (page 3296).

Tool Selection

Tool Best for Pros Cons
DBeaver Community Edition Cross-database engineer default Free, supports MSSQL/Postgres/MySQL/MariaDB/Oracle/SQLite, SSH tunnels built in, ER diagrams, query history Heavier startup than SSMS
SSMS (SQL Server Management Studio) Deep MSSQL admin work, query plans Microsoft-native, best query plan visualization, T-SQL IntelliSense MSSQL only, Windows only
Azure Data Studio Cross-platform MSSQL with notebook-style queries Cross-platform, notebooks, Git integration Slow for large schemas; unfocused product direction
DataGrip (JetBrains) Power-users in JetBrains ecosystem Best-in-class refactoring, version control diffs Paid; license check before deploying widely
sqlcmd / psql Scripted access, automation Universal, scriptable, fast No GUI, no autocomplete by default

DTC default recommendation:

  • DBeaver CE for general engineer database exploration — Halo, dental client SQL instances, Postgres, MariaDB all in one tool with consistent UX
  • SSMS when you need query plan inspection, profiler, or anything specifically MSSQL-tuning-related
  • sqlcmd / psql for scripted access from PowerShell or shell

For installation: winget install dbeaver.dbeaver or download from https://dbeaver.io. SSMS via winget install Microsoft.SQLServerManagementStudio.

Read-Only Credential Pattern

Always exploration with read-only credentials. Never with sysadmin / root / superuser.

The database is the source of truth for live operations — a DELETE typed into the wrong window can take a client offline. Read-only creds make catastrophic mistakes literally impossible. The marginal effort of logging out and back in for the rare case where you need write access is worth it.

For HaloPSA: provision a dedicated dtc_readonly SQL login with db_datareader role on the Halo database. Store credentials in 1Password under the Halo infrastructure entry. Never use the sa account for exploration.

For dental client SQL instances: confirm the read-only credential pattern with the client environment per visit. If only sa is available, write operations require a Halo ticket reference and explicit "I'm about to write" confirmation in the ticket before running.

For Postgres on thedock: use a per-engineer read-only role (grant connect, usage and select on schemas you need). The postgres superuser stays in the vault.

Connection Profiles

DBeaver stores connection profiles locally. Profile content (host/port/database) is not sensitive; passwords are.

For passwords:

  • Set DBeaver to prompt for password each time (Connection settings → uncheck "Save password locally")
  • Or use 1Password's auto-fill via the desktop app — supported via clipboard or directly via the 1Password CLI integration
  • Never check the "save password locally" box on production database connections

For multi-tenant or multi-environment work, use connection groups in DBeaver. Group production connections separately from dev/staging. Color-code production red — DBeaver supports per-connection color tags (Right-click connection → Edit Connection → Connection Settings → "Connection Color"). It's the cheapest visual safety mechanism available.

DBeaver connection group structure (suggested):
├── DTC Internal
│   ├── HaloPSA — PROD (red)              # readonly credentials
│   ├── HaloPSA — Sandbox                  # write OK
│   └── thedock — Postgres
├── Client Dental SQL
│   ├── <Client A> — DEXIS_DATA
│   ├── <Client A> — ROMEXIS
│   └── <Client B> — Open Dental MySQL
└── Personal / Lab
    └── localhost

Halo MSSQL — Connection Pattern

Server type: SQL Server (MSSQL)
Host: <halo-mssql-host>
Port: 1433 (default) — confirm with infrastructure team
Database: <halo-database-name>
Authentication: SQL Server (username/password)
User: dtc_readonly
Password: [from 1Password]

Schema discovery starting points (after connecting):

-- Table count by schema
SELECT TABLE_SCHEMA, COUNT(*) AS table_count
FROM INFORMATION_SCHEMA.TABLES
GROUP BY TABLE_SCHEMA ORDER BY table_count DESC;

-- Find tables matching a pattern (e.g., "ticket")
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%ticket%'
ORDER BY TABLE_SCHEMA, TABLE_NAME;

-- Inspect columns on a specific table
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'FAULTS'
ORDER BY ORDINAL_POSITION;

-- Recent tickets (sanity check that you're connected to live data)
SELECT TOP 10 FAULTID, DATEOCCURED, SUMMARY
FROM FAULTS
ORDER BY DATEOCCURED DESC;

For the per-domain reference, every chapter in this book has a "Table Index" and "Key Tables" page covering its functional area. Start there before exploring tables blind.

Halo Schema Quirks

  • Field naming. API field names don't always match DB column names. The DB is authoritative; the API translates. Reference the schema book chapter for the domain you're querying.
  • Soft deletes. Most "deleted" Halo records are flagged INACTIVE = 1 rather than removed. Default queries should filter WHERE INACTIVE = 0 unless investigating deletions.
  • Custom fields. Most domains have a _CFIELDS companion table or a CUSTOMFIELD<n> column pattern. See the Custom Fields & Configuration chapter.
  • Date columns. DATEOCCURED, LASTMODIFIED, DATECREATED patterns. Most are SQL datetime2. Halo stores in UTC server time; render with timezone awareness in reports.
  • AUDIT_* tables and the Audit & Logging chapter capture historical changes. Useful for "who changed this" questions.
  • No foreign keys (mostly). Halo's schema is relationally consistent but doesn't always enforce constraints at the DB level. Joins work; cascading deletes won't.

Dental Client SQL Instances

DTC's dental practice clients run various SQL backends. For engineer-side investigation of an issue:

Software Engine Default Instance Name Database Name
DEXIS / CS Imaging SQL Server Express (embedded) localhost\CSISSERVER CSISSERVER (typical)
Sidexis 4 SQL Server localhost\<custom> per-instance config
Romexis PostgreSQL (modern), Oracle (legacy) varies Romexis_db (typical)
Open Dental MySQL / MariaDB localhost:3306 opendental (typical)
Dentrix Enterprise SQL Server Express various various
Eaglesoft Sybase Adaptive Server Anywhere n/a (proprietary client) requires Sybase tooling
Softdent various (Server 2025 supported per memory) varies varies

Connection notes:

  • CSISSERVER is the consistent named instance for DEXIS / CS Imaging across DTC clients. SQL Server Express by default — connection string is Data Source=localhost\CSISSERVER;. Mixed-mode auth required for DBeaver/SSMS access; the server-side credential is in IT Glue under the client.
  • Open Dental MySQL uses a static root password historically; modern installs may have rotated. Confirm in IT Glue per client.
  • Eaglesoft uses Sybase ASA — DBeaver can connect with the Sybase JDBC driver, but Eaglesoft's data model is opaque. Most investigations route through Patterson's vendor support, not direct queries.

Postgres on thedock

Server type: PostgreSQL
Host: <thedock-fqdn>
Port: 5432 (or per-service if non-default)
Database: <service-database-name>
Authentication: postgres password
User: <your-readonly-user>
SSH Tunnel: enabled — host = thedock, key from your SSH config

DBeaver supports SSH tunneling natively (Connection settings → SSH tab). Fill in the SSH host/user/key, and DBeaver opens the tunnel before connecting to Postgres. Cleaner than running ssh -L manually.

For thedock specifics, see Docker Infrastructure (thedock) (page 1249).

Query Hygiene

Don't Commit Query History

DBeaver and SSMS save query history locally by default. Files often contain:

  • Live ticket numbers
  • Client names and identifiers
  • Queries against PHI-containing tables
  • Sample data from queries that hit gov-contractor tables

Don't sync query history files to git, OneDrive, or similar. Verify the local-only path. For DBeaver, history lives in ~/.local/share/DBeaverData/ (Linux/WSL) or %APPDATA%\DBeaverData\ (Windows).

Sanitize Before Sharing

If you paste a query result into a ticket or BookStack:

  • Replace identifiers (ticket IDs, user emails, client names) with placeholders unless they're the specific subject of the ticket
  • Truncate row counts to representative samples
  • For PHI/CUI: don't paste at all. Reference the query in the ticket and store the actual output securely (IT Glue under client, or local-only)

Limit Row Returns During Exploration

-- Always include a TOP / LIMIT during exploration
SELECT TOP 100 * FROM FAULTS WHERE ...;        -- MSSQL
SELECT * FROM faults WHERE ... LIMIT 100;      -- Postgres / MySQL

A SELECT * against a wide Halo table on production hardware can lock you out of the connection while it serializes 100k rows you didn't need. TOP / LIMIT is the seatbelt.

Use sp_helptext and Schema Tools, Not Stack Overflow

Halo's schema is documented in this book chapter-by-chapter. Use it. For SSMS specifically:

-- View a stored procedure's text
EXEC sp_helptext 'usp_SomeStoredProcedure';

-- View constraints on a table
EXEC sp_helpconstraint 'FAULTS';

Security & Compliance

  • Halo data is sensitive by definition. Tickets contain client communications, sometimes PHI for HIPAA-covered clients, occasionally CUI-adjacent content for gov-contractor clients. Treat any query output accordingly.
  • For HIPAA-covered clients, dental client SQL instance contents are PHI. Don't run queries you don't need; don't keep query results locally beyond the immediate investigation.
  • For gov-contractor clients under CMMC L1/L2, technical configuration data from their SQL instances may be CUI-adjacent. Same handling as Halo data.
  • Never share live database credentials. If you discover a credential committed somewhere it shouldn't be (git, a ticket note, a chat log), rotate it immediately and update IT Glue.
  • Per-engineer read-only logins (rather than a shared dtc_readonly) make audit trails cleaner. Worth proposing if multiple engineers query the same database regularly.

Ticket Documentation

When SQL queries are part of a ticket investigation, follow the Ticket Documentation Standard (page 1025):

  • Record the exact query under [ACTIONS] (sanitized — replace credentials with <from 1Password>)
  • Record sanitized result excerpts under [FOUND] (row counts, key indicators — not full data)
  • Reference the schema book chapter that covers the domain you queried

Changelog

Date Change By
2026-04-26 Initial publication Zach Boogher
Back to top