SQL Database Setup

SQL Database Setup & Data Access

Last updated 19 Jun 2026Owner: AlterspectiveFor: Client DBA & IT

PMS Sync reads matters, clients, and parties from the firm's on-premises Aderant Expert SQL Server. This page is for the DBA who provisions that access. The guiding rule is least privilege: a single dedicated read-only login, scoped to db_datareader on the Aderant database and nothing more. The agent never writes to Aderant on the import path.

1. Create the read-only login

Create one dedicated SQL login for the agent and grant it read-only access to the Aderant Expert database. Run this on the Aderant SQL Server as a DBA — adapt the names and password to your firm's standards.

-- 1. Server-level login (SQL authentication)
CREATE LOGIN pms_sync_ro WITH PASSWORD = N'<a-strong-unique-password>';

-- 2. Map it into the Aderant Expert database
USE [AderantExpert];   -- substitute your Aderant database name
CREATE USER pms_sync_ro FOR LOGIN pms_sync_ro;

-- 3. Grant read-only — db_datareader only, nothing else
ALTER ROLE db_datareader ADD MEMBER pms_sync_ro;

Why db_datareader. It grants SELECT across the database and nothing else — no insert, update, delete, execute, or DDL. The agent authenticates with a SQL login(SQL_USER / SQL_PASSWORD); integrated / Windows authentication is not currently supported.

2. Enable TCP/IP (SQL Server Express)

SQL Server Express ships with the TCP/IP protocol disabled, so the agent cannot connect until it is turned on. Standard/Enterprise editions usually have it enabled already.

Open SQL Server Configuration Manager → SQL Server Network Configuration → Protocols for [instance].
Enable TCP/IP.
In TCP/IP properties → IP Addresses → IPAll, set a static TCP Port (1433) and clear any dynamic port.
Restart the SQL Server service so the change takes effect.
Confirm the agent host can reach the server over the LAN on that port (typically TCP 1433).

3. Connection configuration

The agent reads its connection from environment variables (set in the local service environment file at install time; deployments can source them from Azure Key Vault). Provide these to the deployment engineer:

Variable	Meaning	Notes
SQL_SERVER	Server hostname or IP	e.g. SQLPROD01
SQL_PORT	TCP port	Default 1433 — a static port is the most reliable setup
SQL_INSTANCE	Named instance (optional)	For a named instance, e.g. EXPERT — an alternative to a static port
SQL_DATABASE	Aderant Expert database name	—
SQL_USER	Read-only login name	The db_datareader login below
SQL_PASSWORD	Read-only login password	Stored in the local service env / Key Vault, never in source
SQL_ENCRYPT	Use TLS for the connection	false on-prem (trusted internal cert) · true for Azure SQL
SQL_TRUST_SERVER_CERTIFICATE	Accept the server certificate without a CA chain	true on-prem · false for Azure SQL
SQL_CONNECT_TIMEOUT_MS	Connection timeout in milliseconds	Default 15000

TLS posture. For an internal on-premises server with a self-signed or internal-CA certificate, use SQL_ENCRYPT=false and SQL_TRUST_SERVER_CERTIFICATE=true. For Azure SQL, use SQL_ENCRYPT=true and SQL_TRUST_SERVER_CERTIFICATE=false so the certificate is properly validated.

4. What the agent reads

Reads are deliberately narrow. The agent queries the Aderant tables below for the import preview and lookups, and uses INFORMATION_SCHEMA to discover which tables and columns exist. The list is representative of the standard import path; the runtime discovery surface can expose additional read-only tables and views when a mapping needs them.

Data	Aderant table(s)	Used for
Clients	HBM_CLIENT	Client search, client code/UNO lookup, matter→client joins.
Matters	HBM_MATTER	Matter search, single-matter fetch, matters-for-client lists.
Parties / names	HBM_NAME, PMA_MATT_PARTY, PMA_PRTY_STATUS, PML_PRTY_STATUS	Matter parties and their roles/status for the import preview.
Addresses	HBM_ADDRESS	Client and party address details.
Employees	HBM_PERSNL	Resolving case-team members for participant mapping.
Schema (metadata)	INFORMATION_SCHEMA.TABLES / COLUMNS	Runtime discovery of readable tables, views, and columns.

5. How read-only is enforced

The agent is read-only by design at several layers, not just by the login's permissions:

Data reads are GET requests — there is no write route into the Aderant database on the import path.
Dynamic table reads whitelist columns against discovered metadata and parameterise all filter values, so no caller-supplied SQL is interpolated.
There is no generic / arbitrary-SQL endpoint. The one POST surface, stored-procedure execution (POST /api/db/:schema/:proc/exec), is fail-closed: it runs only procedures explicitly named in ALLOWED_PROCEDURES, with typed parameters. If that variable is unset, nothing is executable.
Defence in depth: a db_datareader login has no EXECUTE permission, so the proc executor stays inert unless a DBA both allow-lists a procedure and grants it EXECUTE. Granting EXECUTE confers whatever the procedure does (a procedure can write via ownership chaining), so allow-list only audited, read-only procedures.

6. Verify the connection

Once the agent is installed and pointed at the database, confirm the link from the agent host:

Run the preflight check: aderant-agent.cmd --check — exit code 0 means the SQL connection and configuration are valid.
Call the readiness probe: GET /api/health/ready returns 200 only when the SQL Server is reachable and the schema has been discovered (otherwise 503).

For the full agent install (MSI, Azure Relay, verification) see the Deployment Guide. For everything the client must provide, see the Client Requirements Checklist. For how data is protected end to end, see Security & Data Residency.