MSSQL Database Setup

Section: Database Configuration | Article 09
Audience: IT Administrators, Database Administrators
Last Updated: 2026-04-07

---

Overview

RP-PAM requires a dedicated database to store user accounts, vault entries, audit events, workflow state, and module data. This article walks you through creating the database and service account on Microsoft SQL Server, then running the RP-PAM database migrations.

Complete this guide before running the Setup Wizard.

---

Prerequisites

Requirement	Details
SQL Server Version	SQL Server 2019 or later (Standard, Enterprise, or Developer edition)
Access	sysadmin or securityadmin + dbcreator role on the SQL Server instance
Network	The RP-PAM server must be able to reach the SQL Server on port 1433 (or your custom port)
Tools	SQL Server Management Studio (SSMS), sqlcmd, or PowerShell SqlServer module

---

Database Sizing

Choose your initial database size based on your expected deployment:

Deployment Size	Users	Initial Data File	Initial Log File	Growth Estimate (1 Year)
Small	Up to 50	500 MB	200 MB	2 - 5 GB
Medium	51 - 500	2 GB	500 MB	10 - 25 GB
Large	501 - 5,000	5 GB	1 GB	25 - 100 GB
Enterprise	5,000+	10 GB	2 GB	100+ GB

Note: Session recordings and audit logs are the largest consumers of space. If you enable session recording, plan for additional storage. Audit log retention policy can be configured to manage growth.

---

Step 1 — Create the Database

Option A: SQL Server Management Studio (GUI)

1. Open SSMS and connect to your SQL Server instance.
2. Right-click Databases in Object Explorer and select New Database.
3. Set Database name to rppam.
4. On the General page, adjust initial sizes per the sizing table above.
5. On the Options page:
6. Set Recovery model to Full (recommended for production).
7. Set Compatibility level to SQL Server 2019 (150) or higher.
8. Click OK.

Option B: T-SQL

Connect to the SQL Server instance with sqlcmd or SSMS and execute:

-- Create the database
CREATE DATABASE [rppam]
ON PRIMARY (
    NAME       = N'rppam_data',
    FILENAME   = N'C:\SQLData\rppam_data.mdf',
    SIZE       = 500MB,
    FILEGROWTH = 100MB
)
LOG ON (
    NAME       = N'rppam_log',
    FILENAME   = N'C:\SQLLogs\rppam_log.ldf',
    SIZE       = 200MB,
    FILEGROWTH = 50MB
);
GO

-- Set recovery model to Full
ALTER DATABASE [rppam] SET RECOVERY FULL;
GO

Tip: Adjust the FILENAME paths to match your SQL Server data and log directories.

Option C: PowerShell (SqlServer Module)

PowerShell:

# Install the SqlServer module if not already installed
Install-Module -Name SqlServer -Scope CurrentUser -Force

# Import the module
Import-Module SqlServer

# Create the database
Invoke-Sqlcmd -ServerInstance "your-sql-server.example.com" -Query @"
CREATE DATABASE [rppam]
ON PRIMARY (
    NAME       = N'rppam_data',
    FILENAME   = N'C:\SQLData\rppam_data.mdf',
    SIZE       = 500MB,
    FILEGROWTH = 100MB
)
LOG ON (
    NAME       = N'rppam_log',
    FILENAME   = N'C:\SQLLogs\rppam_log.ldf',
    SIZE       = 200MB,
    FILEGROWTH = 50MB
);
ALTER DATABASE [rppam] SET RECOVERY FULL;
"@

Bash (using sqlcmd):

sqlcmd -S your-sql-server.example.com -U sa -Q "
CREATE DATABASE [rppam];
ALTER DATABASE [rppam] SET RECOVERY FULL;
"

---

Step 2 — Create Service Accounts

RP-PAM uses a three-role service account model for defence in depth. Each account has only the permissions it needs.

Account	Purpose	Permissions
rppam_read	Read-only queries, reporting, health checks	db_datareader
rppam_write	All application operations (read + write)	db_datareader, db_datawriter, db_ddladmin
rppam_audit	Audit log writes (append-only)	INSERT on audit tables only

T-SQL

USE [master];
GO

-- Create logins (SQL Authentication)
CREATE LOGIN [rppam_read]  WITH PASSWORD = 'ReadP@ssw0rd!2026',  DEFAULT_DATABASE = [rppam];
CREATE LOGIN [rppam_write] WITH PASSWORD = 'WriteP@ssw0rd!2026', DEFAULT_DATABASE = [rppam];
CREATE LOGIN [rppam_audit] WITH PASSWORD = 'AuditP@ssw0rd!2026', DEFAULT_DATABASE = [rppam];
GO

USE [rppam];
GO

-- Create users and assign roles
CREATE USER [rppam_read]  FOR LOGIN [rppam_read];
CREATE USER [rppam_write] FOR LOGIN [rppam_write];
CREATE USER [rppam_audit] FOR LOGIN [rppam_audit];
GO

-- Read account: read-only
ALTER ROLE [db_datareader] ADD MEMBER [rppam_read];
GO

-- Write account: read + write + DDL (for migrations)
ALTER ROLE [db_datareader] ADD MEMBER [rppam_write];
ALTER ROLE [db_datawriter] ADD MEMBER [rppam_write];
ALTER ROLE [db_ddladmin]   ADD MEMBER [rppam_write];
GO

-- Audit account: insert-only on audit tables (tables created after migrations)
-- Run this AFTER the setup wizard completes migrations:
-- GRANT INSERT ON [dbo].[audit_events] TO [rppam_audit];
-- GRANT INSERT ON [dbo].[audit_sessions] TO [rppam_audit];

Important: Replace the example passwords with strong, unique passwords. Store them securely in a password manager or vault.

SSMS (GUI)

1. In Object Explorer, expand Security > Logins.
2. Right-click Logins and select New Login.
3. Set Login name to rppam_write.
4. Select SQL Server authentication and enter a strong password.
5. Set Default database to rppam.
6. On the User Mapping page, check rppam and assign the roles db_datareader, db_datawriter, and db_ddladmin.
7. Click OK.
8. Repeat for rppam_read (with db_datareader only) and rppam_audit.

PowerShell

$server = "your-sql-server.example.com"

# Create logins and users
$sql = @"
USE [master];
CREATE LOGIN [rppam_read]  WITH PASSWORD = 'ReadP@ssw0rd!2026',  DEFAULT_DATABASE = [rppam];
CREATE LOGIN [rppam_write] WITH PASSWORD = 'WriteP@ssw0rd!2026', DEFAULT_DATABASE = [rppam];
CREATE LOGIN [rppam_audit] WITH PASSWORD = 'AuditP@ssw0rd!2026', DEFAULT_DATABASE = [rppam];

USE [rppam];
CREATE USER [rppam_read]  FOR LOGIN [rppam_read];
CREATE USER [rppam_write] FOR LOGIN [rppam_write];
CREATE USER [rppam_audit] FOR LOGIN [rppam_audit];

ALTER ROLE [db_datareader] ADD MEMBER [rppam_read];
ALTER ROLE [db_datareader] ADD MEMBER [rppam_write];
ALTER ROLE [db_datawriter] ADD MEMBER [rppam_write];
ALTER ROLE [db_ddladmin]   ADD MEMBER [rppam_write];
"@

Invoke-Sqlcmd -ServerInstance $server -Query $sql

---

Step 3 — Verify Connectivity from the RP-PAM Server

Before running the setup wizard, verify that the RP-PAM server can connect to the database.

PowerShell:

# Test TCP connectivity
Test-NetConnection -ComputerName "your-sql-server.example.com" -Port 1433

# Test SQL authentication
Invoke-Sqlcmd -ServerInstance "your-sql-server.example.com" `
    -Database "rppam" `
    -Username "rppam_write" `
    -Password "WriteP@ssw0rd!2026" `
    -Query "SELECT 1 AS ConnectionTest"

Bash:

# Test TCP connectivity
nc -zv your-sql-server.example.com 1433

# Test SQL authentication
sqlcmd -S your-sql-server.example.com -U rppam_write -d rppam -Q "SELECT 1 AS ConnectionTest"

---

Step 4 — Run Database Migrations

Database migrations are applied automatically during the Setup Wizard (Step 5). You do not need to run them manually unless you are upgrading an existing installation.

Manual Migration (Upgrades Only)

If you are upgrading RP-PAM and need to apply new migrations outside of the wizard:

Windows (PowerShell):

& "C:\Program Files\Ravenphyre\RP-PAM\rppam.exe" migrate --apply

Linux (Bash):

sudo /opt/rppam/rppam migrate --apply

Docker:

docker exec -it rppam /opt/rppam/rppam migrate --apply

To preview pending migrations without applying them:

sudo /opt/rppam/rppam migrate --list

---

Recommended SQL Server Settings

Setting	Recommended Value	Why
Max memory	70-80% of server RAM (if dedicated)	Prevents SQL Server from consuming all available memory
Max degree of parallelism	4 (small) / 8 (large)	Prevents excessive parallelism on complex queries
TempDB files	1 per CPU core (up to 8)	Reduces TempDB contention
Backup strategy	Full daily + transaction log every 15 min	Enables point-in-time recovery
TLS encryption	Enabled (Force Encryption = Yes)	Encrypts data in transit between RP-PAM and SQL Server

---

Troubleshooting

Symptom	Cause	Resolution
Connection refused on port 1433	SQL Server not listening or firewall blocking	Verify SQL Server is running and the firewall allows TCP 1433
Login failed for user	Wrong password or login disabled	Verify the password and that the login is enabled in SSMS
"Cannot open database" error	Database does not exist or user not mapped	Verify the database exists and the user is mapped to it
Migration fails with permission error	Service account lacks db_ddladmin	Add the rppam_write user to the db_ddladmin role
Migration timeout	Slow disk or large migration	Increase migrationTimeoutSeconds in rppam.config
TLS certificate error	SQL Server using self-signed cert	Add TrustServerCertificate=True to the connection string (development only) or install the CA certificate on the RP-PAM server

---

Next Steps

• Setup Wizard — Run the setup wizard and provide your database connection details
• Connection String Encryption — Encrypt database credentials in the configuration file
• Backup and Restore — Configure backups for your RP-PAM database

---

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