> For the complete documentation index, see [llms.txt](https://docs.balkan.id/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.balkan.id/getting-started/setting-up-your-tenant/integrate-employee-data/integration-with-hris-system/oracle-peoplesoft-integration.md). # Oracle PeopleSoft Integration ### Overview The BalkanID Oracle PeopleSoft Agent extracts employee data from an on-premise PeopleSoft HCM deployment and uploads it to BalkanID. It is **read-only** and never writes to PeopleSoft. The agent runs inside your network and makes only **outbound** connections — HTTPS to BalkanID and a database connection to PeopleSoft. **No inbound connectivity** is required. Each cycle uploads a full snapshot of the workforce, which replaces the previous one. *** ### Architecture ```mermaid flowchart LR subgraph CN["Customer network"] AGENT["Agent host
balkanid-psft-agent
Windows Server or Linux"] DB[("PeopleSoft HCM database
SQL Server or Oracle")] end subgraph BID["BalkanID"] API["balkanid.app"] end AGENT -->|"TCP 1433 / 1521 / 2484
SELECT on 6 tables, read-only"| DB AGENT -->|"HTTPS 443
outbound only"| API ``` The agent only ever initiates connections. Nothing from BalkanID reaches into your network, no port is opened inbound on the agent host, and the agent does not run on the PeopleSoft server. #### The extraction cycle Every `extraction_interval`, and once immediately on start: ```mermaid sequenceDiagram autonumber participant A as Agent host participant P as PeopleSoft DB participant B as balkanid.app Note over A: Validate credentials, fail the cycle if incomplete A->>P: SELECT current job rows (TCP 1433/1521/2484) P-->>A: Employees Note over A: Write employees.csv, refuse if empty A->>B: Request presigned upload URL (HTTPS 443) B-->>A: Presigned URL A->>B: PUT employees bundle (HTTPS 443) ``` Credentials are checked before the database is read, so a misconfigured agent fails in a second rather than after extracting the whole workforce. #### Where to install the agent Any host that can reach the PeopleSoft database listener. It does **not** need to run on the PeopleSoft application or database server. | Host | Notes | | -------------- | ----------------------------------------------------------------------------------------------------------------------------- | | Windows Server | **Required for AD authentication** (`auth_mode: windows`). Domain-joined member server; the service runs as a domain account. | | Linux | Suitable for SQL authentication. AD authentication is not usable on Linux — see below. | | Sizing | Negligible. One query per cycle; the process is idle between cycles. | | Placement | A management or jumpbox host is typical. Avoid the database server itself so agent restarts never touch PeopleSoft. | #### Network requirements | Source | Destination | Protocol | Port | Direction | Configurable | Purpose | | ---------- | ---------------------------------------------------------------------------- | ------------------- | ---- | --------- | ----------------------------- | ------------------------------------------------------- | | Agent host | PeopleSoft database (SQL Server) | TDS | 1433 | Outbound | Yes, via `port` | Read the six `PS_*` tables | | Agent host | PeopleSoft database (Oracle) | Oracle Net | 1521 | Outbound | Yes, via `port` | Read the six `PS_*` tables | | Agent host | PeopleSoft database (Oracle TCPS) | Oracle Net over TLS | 2484 | Outbound | Yes, via `port` and `use_tls` | Encrypted alternative to 1521 | | Agent host | `balkanid.app` | HTTPS | 443 | Outbound | No (fixed BalkanID endpoint) | REST API: request a presigned upload URL | | Agent host | Presigned upload host (`balkanid.app`, `api-integrators.balkanid.app` or S3) | HTTPS | 443 | Outbound | No | Upload the `employees.csv` bundle | | Agent host | `cdn.balkanid.app` | HTTPS | 443 | Outbound | No (fixed BalkanID endpoint) | **Installation and upgrade only** — not used at runtime | {% hint style="info" %} There is no inbound requirement. The agent has no listening port and cannot be called from outside your network. {% endhint %} The upload destination is a presigned URL issued by `balkanid.app`. The agent validates the host before sending and refuses redirects, so egress can be restricted to the destinations above. An air-gapped host does not need `cdn.balkanid.app` at all — install from a local bundle with `--file`. ### Requirements * **PeopleSoft HCM 9.2** on Microsoft SQL Server or Oracle. Db2 is not supported. * Network access as described above. * A read-only database account (see below). * The PeopleSoft table owner name — conventionally `SYSADM`. * The `peoplesoft` integration installed on your BalkanID tenant, and all four credentials from the console: **tenant ID, tenant key, tenant secret and integration ID**. * A Linux (systemd) or Windows host. PeopleTools, an Oracle client, ODBC and Java are not required. *** #### Required database privileges The agent reads six tables. Payroll, benefits, compensation and national IDs are not read. | Table | Supplies | | -------------------- | -------------------------------------------------------------------------------- | | `PS_JOB` | job record, department, job code, status, reporting line, effective date, action | | `PS_NAMES` | primary and preferred name | | `PS_EMAIL_ADDRESSES` | work email | | `PS_EMPLOYMENT` | hire and termination dates | | `PS_DEPT_TBL` | department description | | `PS_JOBCODE_TBL` | job title | **SQL Server** ```sql CREATE LOGIN [BALKANID_RO] WITH PASSWORD = N''; CREATE USER [BALKANID_RO] FOR LOGIN [BALKANID_RO]; GRANT SELECT ON [SYSADM].[PS_JOB] TO [BALKANID_RO]; GRANT SELECT ON [SYSADM].[PS_NAMES] TO [BALKANID_RO]; GRANT SELECT ON [SYSADM].[PS_EMAIL_ADDRESSES] TO [BALKANID_RO]; GRANT SELECT ON [SYSADM].[PS_EMPLOYMENT] TO [BALKANID_RO]; GRANT SELECT ON [SYSADM].[PS_DEPT_TBL] TO [BALKANID_RO]; GRANT SELECT ON [SYSADM].[PS_JOBCODE_TBL] TO [BALKANID_RO]; ``` For Windows authentication, create the login from the domain account instead and set `auth_mode: windows` in the config: ```sql CREATE LOGIN [DOMAIN\svc-balkanid] FROM WINDOWS; CREATE USER [svc-balkanid] FOR LOGIN [DOMAIN\svc-balkanid]; ``` **Oracle** ```sql CREATE USER BALKANID_RO IDENTIFIED BY ""; GRANT CREATE SESSION TO BALKANID_RO; GRANT SELECT ON SYSADM.PS_JOB TO BALKANID_RO; GRANT SELECT ON SYSADM.PS_NAMES TO BALKANID_RO; GRANT SELECT ON SYSADM.PS_EMAIL_ADDRESSES TO BALKANID_RO; GRANT SELECT ON SYSADM.PS_EMPLOYMENT TO BALKANID_RO; GRANT SELECT ON SYSADM.PS_DEPT_TBL TO BALKANID_RO; GRANT SELECT ON SYSADM.PS_JOBCODE_TBL TO BALKANID_RO; ``` If your PeopleSoft tables are owned by a schema other than `SYSADM`, set `schema:` in the config to match. Verify from the agent host with `balkanid-psft-agent --test-connection`, which probes each of the six tables and reports any the account cannot read. *** #### Windows authentication `auth_mode: windows` requires a **Windows** agent host. On Windows the agent authenticates as the process identity, with no credentials in the config. On Linux only the NTLM provider is available, which still requires `DOMAIN\user` and a password in the config — gaining nothing over `auth_mode: sql`. {% hint style="warning" %} The Windows service is registered as **LocalSystem**, which authenticates to SQL Server as the machine account. With `auth_mode: windows`, re-point the service at the domain account holding the `SELECT` grants (`services.msc` → BalkanID PeopleSoft Agent → Log On). {% endhint %} ### Installation #### Linux (systemd) ```sh curl -fsSL https://cdn.balkanid.app/files/balkanid/psft-agent/releases/latest/install.sh | sudo sh sudo editor /etc/balkanid/psft-agent/config.yaml sudo systemctl restart balkanid-psft-agent ``` The installer creates the `balkanid` service user, verifies the download checksum, installs the binary to `/usr/local/bin`, and registers and starts the systemd unit. | Path | Location | | ------ | ------------------------------------------------------------------------------------------------- | | Binary | `/usr/local/bin/balkanid-psft-agent` | | Config | `/etc/balkanid/psft-agent/config.yaml` | | Output | `/var/lib/balkanid/psft-agent/output/` | | Logs | journald (`journalctl -u balkanid-psft-agent`) + per-day files `/var/log/balkanid/YYYY-MM-DD.log` | #### Windows (service) From an **elevated PowerShell**: ```powershell Invoke-WebRequest -Uri "https://cdn.balkanid.app/files/balkanid/psft-agent/releases/latest/install.ps1" -OutFile "C:\temp\install.ps1" C:\temp\install.ps1 notepad "$env:ProgramData\BalkanID\psft-agent\config.yaml" Start-Service BalkanIDPSFTAgent ``` | Path | Location | | ------- | ---------------------------------------------------------------------- | | Binary | `C:\Program Files\BalkanID\psft-agent\balkanid-psft-agent.exe` | | Config | `C:\ProgramData\BalkanID\psft-agent\config.yaml` | | Logs | per-day files `C:\ProgramData\BalkanID\psft-agent\logs\YYYY-MM-DD.log` | | Service | `BalkanIDPSFTAgent` (auto-start, LocalSystem) | For an air-gapped host, download the bundle elsewhere and install from the file: ```sh sudo ./install.sh --file balkanid-psft-agent_linux_amd64.tar.gz ``` ### Configuration The agent uses `--config ` if given, otherwise Linux `/etc/balkanid/psft-agent/config.yaml` or Windows `C:\ProgramData\BalkanID\psft-agent\config.yaml`. If no file exists, a skeleton is written on first run. Get the tenant ID, tenant key, tenant secret and integration ID from your BalkanID administrator (Integrations → Add Integration → Oracle PeopleSoft HCM). All four are required. ```yaml server: heartbeat_mode: true # run the periodic extract-and-upload loop extraction_interval: 12h # Go duration; floored at 5m peoplesoft: instances: - name: PSFT-PROD # logical name; becomes the source system on rows driver: mssql # mssql | oracle host: psft-db.internal.example.com port: 1433 # 1433 for SQL Server, 1521 for Oracle database: HCM92 # SQL Server only: database holding the PeopleSoft tables auth_mode: sql # sql | windows (windows requires a Windows host) db_username: BALKANID_RO db_password: "CHANGE_ME" encrypt: true # SQL Server TLS trust_server_certificate: false # service_name: HCMPROD # Oracle only (or set `sid:` instead) # use_tls: true # Oracle TCPS, usually port 2484 # wallet_path: /etc/balkanid/psft-agent/wallet # Oracle TLS trust anchors schema: SYSADM # PeopleSoft table owner manager_source: auto # supervisor | position | auto email_type: BUSN # PS_EMAIL_ADDRESSES.E_ADDR_TYPE for the work address auth: # all four are required tenant_id: "01xxx" tenant_key: "" tenant_secret: "" integration_id: "" # the installed PeopleSoft integration on your tenant ``` {% hint style="warning" %} The agent refuses to run a cycle if any of the four credentials is missing, and names every missing key at once. `integration_id` is not resolved at runtime: a tenant can hold more than one PeopleSoft integration, and guessing would upload to the wrong one. `--test-connection` and `--dry-run` do not need these — neither uploads — so the database side can be verified before the credentials are issued. {% endhint %} The config file holds the database password and the tenant secret. It is created owner-only, and the agent logs a warning if the permissions later widen. **Transport encryption.** SQL Server connections are encrypted unless `encrypt: false` is set. For Oracle, set `use_tls: true` to connect over TCPS and point `wallet_path` at an Oracle wallet directory holding the trust anchors; server-certificate verification stays on. Most on-prem deployments use plain TCP on `1521`, in which case leave both unset. *** #### Settings that depend on your PeopleSoft configuration | Setting | Values | How to choose | | ---------------- | -------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `schema` | default `SYSADM` | The owner of the `PS_*` tables. A wrong value fails at the first query with a "table not found" error. | | `manager_source` | `supervisor`, `position`, `auto` | `supervisor` reads `PS_JOB.SUPERVISOR_ID`; `position` resolves `PS_JOB.REPORTS_TO` through the position's current incumbent; `auto` prefers `SUPERVISOR_ID` and falls back to `REPORTS_TO` per row. Sites running Position Management usually populate `REPORTS_TO`. | | `email_type` | default `BUSN` | The `PS_EMAIL_ADDRESSES.E_ADDR_TYPE` holding the work address. | To see which reporting-line column your site populates: ```sql SELECT SUM(CASE WHEN J.SUPERVISOR_ID IS NOT NULL AND J.SUPERVISOR_ID <> ' ' THEN 1 ELSE 0 END) AS has_supervisor_id, SUM(CASE WHEN J.REPORTS_TO IS NOT NULL AND J.REPORTS_TO <> ' ' THEN 1 ELSE 0 END) AS has_reports_to FROM SYSADM.PS_JOB J WHERE J.EFFDT = (SELECT MAX(J2.EFFDT) FROM SYSADM.PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <= GETDATE()); ``` Each cycle logs how many employees resolved a manager, and warns below 50%. *** #### What is extracted * **Current state only.** From `PS_JOB`, the row in force per `(EMPLID, EMPL_RCD)` — the latest `EFFDT` not in the future, and the last `EFFSEQ` on that date. * **All employees, including leavers.** There is no status filter. Leavers carry their `TERMINATION_DT` as the end date and report `suspended`. Everyone else reports `active`, including employees on a leave status (`L`, `P`, `S`, `W`). * **One row per person.** A person holding concurrent jobs is emitted once, preferring an active record and then the lowest `EMPL_RCD`. Run `--check-concurrent-jobs` to see how many people this affects. * **Job-change actions.** Each row carries the `PS_JOB.ACTION` and effective date behind its current state — `HIR` hire, `XFR` transfer, `PRO` promotion, `TER` termination. ### Running the agent | Flag | Mode | | ------------------------------------------- | ------------------------------------------------------------------------------------ | | `--test-connection` | Verify the connection and probe all six tables; exits non-zero if any is unreadable. | | `--check-concurrent-jobs` | Report how many people hold more than one job record. | | `--dry-run --output ` | Extract to `employees.csv` locally; do **not** upload. | | `--once` | Run one extract-and-upload cycle, then exit. | | `--headless` | Run the extraction loop in the foreground (used by the services). | | `--install-service` / `--uninstall-service` | Register / remove the OS service. | | `--config ` | Use a specific config file. | | `--version` | Print version and exit. | In service mode the agent starts on boot, extracts every `extraction_interval` (default 12 hours), and writes per-day log files. The first cycle runs immediately on start. Before enabling the service, run `balkanid-psft-agent --dry-run --output ./out` and check `employees.csv`. It uploads nothing, and confirms names, departments, titles, managers and termination dates are correct. A healthy cycle logs: ``` level=info msg="instance \"PSFT-PROD\": read 3512 employees as of 2026-07-31 (manager source \"auto\")" level=info msg="instance \"PSFT-PROD\": emitted 3512 employees (3488 with a manager, 3501 with a work email, 214 leavers with a termination date)" level=info msg="instance \"PSFT-PROD\": job-change actions on current rows — HIR=112 XFR=48 PRO=27 TER=214" level=info msg="uploaded employees bundle" ``` After the first successful cycle, the employees appear under **Users** in the **Configure** section of BalkanID. *** ### Troubleshooting | Message | Cause and fix | | ------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `no PeopleSoft instances configured` | The config is still the generated skeleton. Fill in `peoplesoft.instances[0]`. | | `auth.integration_id ... is not set` | One or more BalkanID credentials are missing. The message names every missing key; all four are required. | | `connect to :` | Network, not credentials. Check the firewall rule, the listener, and that SQL Server TCP/IP is enabled. | | `N of 6 tables are unreadable` | The read-only account is missing grants. The message names each failing table. | | `Invalid column name` / `Invalid object name` | `schema:` points at the wrong owner, or on SQL Server `database:` is wrong. | | `Login failed for user` with `auth_mode: windows` | The service is running as LocalSystem, authenticating as the machine account. Re-point it at the domain account. | | `no work email resolved for any employee` | `email_type` does not match this site. Run `SELECT E_ADDR_TYPE, COUNT(*) FROM SYSADM.PS_EMAIL_ADDRESSES GROUP BY E_ADDR_TYPE` to see which types are in use. | | `only N of M employees resolved a manager` | `manager_source` does not match how the site records reporting lines. See the query above. | | `refusing to emit an empty roster` | The query returned no employees. Check `schema:` and the grants. | | `peoplesoft integration not found for tenant` | `auth.integration_id` is wrong, or the integration is not installed on the tenant. | | `the tenant API key was rejected` | `auth.tenant_key` / `auth.tenant_secret` are wrong or rotated. | | Service starts then stops (Linux) | The `balkanid` service user does not exist. Run `useradd --system --no-create-home --shell /usr/sbin/nologin balkanid`. | | Service runs but nothing uploads | `server.heartbeat_mode` is `false`. Set it to `true` and restart. | --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://docs.balkan.id/getting-started/setting-up-your-tenant/integrate-employee-data/integration-with-hris-system/oracle-peoplesoft-integration.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.