Skip to content

Database Troubleshooting

Section: Troubleshooting | Article 48
Audience: System Administrators, Database Administrators
Last Updated: 2026-04-07

Overview

This article covers database-related issues for both supported engines: Microsoft SQL Server (MSSQL) and PostgreSQL. For general troubleshooting, see General Troubleshooting.

Connection Refused

Symptoms

  • Health ping returns 500 with "database": "unhealthy"
  • Log shows ConnectionRefused or No connection could be made because the target machine actively refused it

Diagnosis

Step 1: Verify the database server is running.

MSSQL: 

# Check SQL Server service
Get-Service -Name "MSSQLSERVER"   # Default instance
Get-Service -Name "MSSQL$*"      # Named instances

PostgreSQL: 

sudo systemctl status postgresql

Step 2: Test connectivity from the RP-PAM server.

PowerShell (any database): 

Test-NetConnection -ComputerName "db-server.corp.local" -Port 1433  # MSSQL
Test-NetConnection -ComputerName "db-server.corp.local" -Port 5432  # PostgreSQL

Linux: 

# PostgreSQL
pg_isready -h db-server.corp.local -p 5432

# MSSQL or PostgreSQL (generic TCP check)
nc -zv db-server.corp.local 1433
nc -zv db-server.corp.local 5432

Step 3: Check firewall rules on the database server.

Engine Default Port Common Firewall Issue
MSSQL 1433 Windows Firewall blocking inbound TCP 1433
PostgreSQL 5432 pg_hba.conf not allowing the RP-PAM server IP

Migration Timeout (MSSQL)

Symptoms

  • rppam-migrate --apply fails with a timeout error
  • Log shows Execution Timeout Expired or SqlException: Timeout expired

Cause

Schema migrations on large tables can exceed the default command timeout, especially on busy MSSQL instances.

Solution

Step 1: Increase the migration timeout in rppam.config:

{
  "database": {
    "migrationTimeoutSeconds": 600
  }
}

The default is 120 seconds. Set it higher for large databases.

Step 2: Run migrations during low-activity periods. Heavy transactional workloads can cause lock contention.

Step 3: Check for blocking queries:

-- Run on MSSQL to find blocking sessions
SELECT
    r.session_id AS blocked_session,
    r.blocking_session_id AS blocking_session,
    r.wait_type,
    r.wait_time / 1000.0 AS wait_seconds,
    t.text AS blocked_query
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id > 0;

Step 4: If a specific migration is stuck, check its status:

# Windows
cd "C:\Program Files\Ravenphyre\RP-PAM\tools"
.\rppam-migrate.exe --status

# Linux
sudo /opt/rppam/tools/rppam-migrate --status

"relation does not exist" (PostgreSQL)

Symptoms

  • API returns 500 errors
  • Log shows PostgresException: relation "table_name" does not exist

Cause

The database schema is incomplete. Migrations may not have been applied, or the RP-PAM service is connected to the wrong database.

Solution

Step 1: Check the connection string in rppam.config — verify the Database parameter points to the correct database.

Step 2: Check which migrations have been applied:

sudo /opt/rppam/tools/rppam-migrate --status

cd "C:\Program Files\Ravenphyre\RP-PAM\tools"
.\rppam-migrate.exe --status

Step 3: If migrations are missing, apply them:

sudo /opt/rppam/tools/rppam-migrate --apply

Step 4: If migrations show as applied but tables don't exist, verify you are connected to the correct database:

# Check the database directly
sudo -u postgres psql -d rppam -c "\dt"

This lists all tables. If the list is empty, migrations did not apply to this database.

Step 5: Check the search_path (schema). RP-PAM uses the public schema by default:

sudo -u postgres psql -d rppam -c "SHOW search_path;"

Login Failed (MSSQL Mixed Mode)

Symptoms

  • Log shows Login failed for user 'rppam_admin'
  • SQL Server is configured for Windows Authentication only

Cause

RP-PAM connects to MSSQL using SQL Server authentication (username/password). If the SQL Server instance is configured for "Windows Authentication Mode" only, SQL logins are rejected.

Solution

Step 1: Check the current authentication mode:

-- Run in SSMS or sqlcmd
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS WindowsAuthOnly;
-- 1 = Windows only, 0 = Mixed mode

Step 2: Enable mixed mode authentication:

  1. Open SQL Server Management Studio (SSMS)
  2. Right-click the server instance > Properties
  3. Go to Security page
  4. Select SQL Server and Windows Authentication mode
  5. Click OK
  6. Restart the SQL Server service: 
    Restart-Service -Name "MSSQLSERVER"

Step 3: Verify the RP-PAM login exists:

SELECT name, type_desc, is_disabled
FROM sys.server_principals
WHERE name = 'rppam_admin';

If the login does not exist, create it:

CREATE LOGIN rppam_admin WITH PASSWORD = 'YourStrongPassword';
GO

USE RPPAM;
GO

CREATE USER rppam_admin FOR LOGIN rppam_admin;
GO

ALTER ROLE db_owner ADD MEMBER rppam_admin;
GO

Connection Pool Exhaustion

Symptoms

  • Intermittent database errors under load
  • Log shows The connection pool has been exhausted or Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool

Cause

All connections in the pool are in use and none are being returned. This can happen due to connection leaks or very high concurrency.

Solution

Step 1: Check connection pool settings in rppam.config:

{
  "database": {
    "maxPoolSize": 100,
    "minPoolSize": 5,
    "connectionLifetimeSeconds": 300
  }
}

Step 2: Check active connections on the database server.

MSSQL: 

SELECT
    DB_NAME(dbid) AS DatabaseName,
    COUNT(*) AS ConnectionCount
FROM sys.sysprocesses
WHERE DB_NAME(dbid) = 'RPPAM'
GROUP BY dbid;

PostgreSQL: 

SELECT
    datname,
    count(*) AS connection_count
FROM pg_stat_activity
WHERE datname = 'rppam'
GROUP BY datname;

Step 3: If connections are high, increase maxPoolSize or investigate why connections are not being returned (check for long-running queries).

PostgreSQL: Connection Limit Reached

Symptoms

  • Log shows FATAL: too many connections for role "rppam_admin"
  • Or: FATAL: sorry, too many clients already

Solution

Step 1: Check current connection limits: 

SHOW max_connections;
SELECT rolconnlimit FROM pg_roles WHERE rolname = 'rppam_admin';

Step 2: Increase the limits:

# Edit postgresql.conf
sudo nano /etc/postgresql/16/main/postgresql.conf

Change: 

max_connections = 200

Then restart PostgreSQL: 

sudo systemctl restart postgresql

For the role-specific limit: 

ALTER ROLE rppam_admin CONNECTION LIMIT -1;  -- -1 means unlimited

PostgreSQL: pg_hba.conf Issues

Symptoms

  • FATAL: no pg_hba.conf entry for host "10.0.1.50", user "rppam_admin", database "rppam"

Solution

Edit pg_hba.conf to allow the RP-PAM server:

sudo nano /etc/postgresql/16/main/pg_hba.conf

Add a line for the RP-PAM server: 

# TYPE  DATABASE  USER          ADDRESS         METHOD
host    rppam     rppam_admin   10.0.1.50/32    scram-sha-256

Or for a subnet: 

host    rppam     rppam_admin   10.0.1.0/24     scram-sha-256

Reload PostgreSQL (no restart needed): 

sudo systemctl reload postgresql

Database Backup Verification

If you suspect database corruption or need to verify your backup:

MSSQL: 

-- Verify backup integrity
RESTORE VERIFYONLY FROM DISK = 'C:\Backups\RPPAM.bak';

PostgreSQL: 

# Test restore to a temporary database
sudo -u postgres createdb rppam_test_restore
sudo -u postgres psql rppam_test_restore < /tmp/rppam-backup.sql
sudo -u postgres psql rppam_test_restore -c "\dt"
sudo -u postgres dropdb rppam_test_restore

Troubleshooting Summary

Problem Engine First Check
Connection refused Both Network connectivity and port
Migration timeout MSSQL Increase migrationTimeoutSeconds
Relation does not exist PostgreSQL Run rppam-migrate --status
Login failed MSSQL Check mixed mode authentication
Pool exhausted Both Check active connection count
Too many connections PostgreSQL Check max_connections
pg_hba.conf denied PostgreSQL Add RP-PAM server entry

Next Steps

RP-PAM v1.0.0 — Copyright 2026 Ravenphyre. All rights reserved.