Securing On-Prem Database Connectivity

One of the defining advantages of running AI Insights 365 — On-Prem Edition is that the platform lives inside your own network, right alongside the data it analyzes. There is no external service to authorize, no third-party relay to trust, and no extra moving part sitting between the application and your databases. The application connects directly to your data sources over your own private network, exactly the way any other internal application on your infrastructure would. This document explains why that direct model is both simpler and more secure, and it walks through how to design the network, lock down database accounts, protect credentials, encrypt traffic, and troubleshoot connectivity — all without introducing any intermediary component.

If you are coming from a SaaS analytics tool, you may be used to the idea that a hosted product needs some kind of agent or relay installed in your environment so it can reach databases that are otherwise unreachable from the public internet. The On-Prem Edition removes that need entirely. Because the application itself runs in your trusted zone, the database is already reachable. There is nothing to tunnel out to and nothing to call back in from. Connectivity is a plain, in-network TCP connection from the app host to the database host.

Why an On-Prem Deployment Connects Directly

In a hosted model, the analytics vendor's servers sit outside your perimeter. Your databases, correctly, do not accept connections from the public internet, so a hosted product has to find some way across the boundary. That boundary is the entire reason intermediary components exist in those architectures.

The On-Prem Edition collapses that boundary. You deploy the application onto a host that you own, in a network segment that you control, in the same trusted zone as your data. When the application needs to read from a database, it opens a connection to that database's private hostname or IP on its normal port — 1433 for SQL Server, 5432 for PostgreSQL, 3306 for MySQL, 1521 for Oracle, and so on. The packets never leave your network.

This has several concrete consequences for how you think about architecture:

• The trust boundary is your network, not a vendor's. Data sovereignty is preserved by default because query traffic and results stay entirely within infrastructure you operate.
• There is no outbound dependency for connectivity. The application does not need to reach any external endpoint to talk to your databases.
• Latency is minimal. App-to-database round trips happen at LAN or intra-VPC speeds rather than crossing the internet.
• The attack surface shrinks. Fewer components mean fewer credentials, fewer listening ports, and fewer things to patch and monitor.

The mental model to hold onto throughout this document: the application is just another in-network client of your databases. Everything you already know about securing internal application-to-database traffic applies directly.

Network Design

The goal of your network design is straightforward — place the application host where it can reach every database server it needs, and nowhere it does not need to reach. Start by inventorying your data sources and noting, for each, the hostname or IP, the port, and the network segment it lives in.

Placing the Application Host

For the simplest and most secure topology, put the application host in the same trusted zone or VLAN as the databases it serves. When the app and the database share a subnet, traffic between them stays local and the only firewall considerations are intra-segment rules.

In larger environments, databases are frequently spread across multiple subnets, data-center segments, or cloud regions. In that case, position the application host in a segment that has routed, controlled access to each database segment, and use one of the following to bridge them:

• Private network routing between subnets in the same data center.
• VPN (site-to-site or a private link) when a database lives in a remote office, a separate facility, or another cloud account.
• VPC peering or private connectivity when databases run in a separate VPC or region from the app host.

The principle is the same across all of these: keep the path private. Connectivity between the application and the database should ride over internal routes, peering, or encrypted tunnels — never over a public path.

Firewall Rules Between App and Database

Define narrow, explicit allow rules so the application host can reach only the database ports it actually uses. A typical rule set looks like this:

Scope the source of every rule to the application host's address (or a tightly defined host group), not to a broad subnet. Scope the destination to specific database hosts. The combination of a narrow source and a narrow destination means that even if another machine in the segment is compromised, it does not inherit the application's database reachability.

Never Expose Database Ports to the Internet

This rule is non-negotiable and it is worth stating on its own: database ports must never be reachable from the public internet. Because the On-Prem Edition connects from inside your network, there is never a reason to open 1433, 5432, 3306, 1521, or any other database port to the outside world to make the platform work. If a database is currently internet-reachable, close it. Cloud warehouses such as Snowflake, BigQuery, Redshift, and Databricks should be reached over private connectivity options where available, and access to them should be locked to your egress addresses.

The same applies to SaaS and API data sources: the application calls those endpoints outbound from inside your network over TLS. No inbound exposure of your own databases is ever required.

Least-Privilege Database Accounts

The application should authenticate to each data source with a dedicated account that has the minimum rights necessary to read the data being analyzed — nothing more. Do not reuse an administrative login, an application service account, or a shared analyst credential.

Follow these rules for every data source:

• Create one dedicated login per data source. A distinct credential per source makes auditing, rotation, and revocation clean and contained.
• Grant read-only access. The account should hold SELECT (or the equivalent) and no write, schema-modification, or administrative privileges.
• Grant only the schemas and tables that are needed. If only a handful of schemas are relevant to analysis, scope the grant to those rather than to the whole database.

The platform reinforces this on its side. Queries it issues against your data sources are read-only, row-limited, and sandboxed — they are designed to retrieve only what is needed for a result and are constrained so they cannot run unbounded or destructive operations. The combination of a read-only login on the database and read-only, bounded query behavior in the application gives you defense in depth: even a flaw on one side is contained by the constraint on the other.

Below are illustrative examples of creating a least-privilege read-only login. Adapt object names and scopes to your environment.

-- SQL Server: dedicated read-only login scoped to one database
CREATE LOGIN ai_insights_ro WITH PASSWORD = '<strong-random-secret>';
USE AnalyticsDB;
CREATE USER ai_insights_ro FOR LOGIN ai_insights_ro;
GRANT SELECT ON SCHEMA::sales TO ai_insights_ro;
GRANT SELECT ON SCHEMA::finance TO ai_insights_ro;

-- PostgreSQL: read-only role limited to specific schemas
CREATE ROLE ai_insights_ro LOGIN PASSWORD '<strong-random-secret>';
GRANT CONNECT ON DATABASE analytics TO ai_insights_ro;
GRANT USAGE ON SCHEMA sales TO ai_insights_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO ai_insights_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA sales GRANT SELECT ON TABLES TO ai_insights_ro;

-- MySQL: read-only user scoped to one schema, from the app host only
CREATE USER 'ai_insights_ro'@'10.20.0.5' IDENTIFIED BY '<strong-random-secret>';
GRANT SELECT ON analytics.* TO 'ai_insights_ro'@'10.20.0.5';

Note the MySQL example pins the account to the application host's address, adding a network-level constraint on top of the privilege scope. Where your database engine supports host-scoped accounts, use them.

Credential Protection

Connection details for each data source — including hostnames, ports, usernames, and passwords — are stored by the platform, but they are never stored in clear text. Connection strings and passwords are encrypted at rest using AES-256-CBC. The encryption key is held separately from the data it protects, so the stored ciphertext is not usable on its own.

Just as important for day-to-day operations, the encryption key is rotatable from System Settings without requiring a restart. Key rotation is a routine part of a healthy security posture, and being able to rotate without downtime means you can do it on schedule — or immediately, if a rotation is ever warranted — without disrupting analysts who are mid-session.

A few practical guidelines around credentials:

• Use long, random, unique passwords for each read-only database login.
• Rotate database passwords and the platform encryption key on a regular cadence, and update the stored connection details when you rotate a database password.
• Restrict who can view and edit data-source configuration to a small set of administrators.

Connection Testing and Schema Introspection

When you add or edit a data source, the platform tests the connection before saving it. A successful test confirms that the application host can reach the database on its port, that the credentials authenticate, and that any required transport encryption negotiates correctly.

Immediately after a connection succeeds, the platform performs schema introspection — it reads the catalog of schemas, tables, and columns that the read-only account is permitted to see. This is exactly why scoping the account's grants matters: introspection reflects the account's privileges, so a tightly scoped login produces a tightly scoped, relevant view of the data model. There is no need to manually describe your schema; the platform discovers it within the bounds you have set.

If a connection test fails, work through the likely causes in order:

• Firewall / routing — Can the app host actually reach the database host and port? Confirm the allow rule exists and that the path (routing, peering, or VPN) is up.
• DNS — Does the database hostname resolve correctly from the app host? A stale or wrong record will fail before a connection is ever attempted.
• Authentication — Are the username and password correct, and does the account have permission to connect to the target database from the app host's address?
• TLS — If the database requires encrypted connections, is the certificate chain trusted by the app host? An untrusted or expired certificate will cause the handshake to fail even when everything else is correct.

A quick way to isolate where a failure sits is to test reachability at the network layer first, before involving credentials:

# Confirm the app host can reach the database port (TCP reachability)
nc -vz db.internal.example 5432

# Confirm DNS resolution from the app host
nslookup db.internal.example

# For PostgreSQL, a direct client connection test with TLS required
psql "host=db.internal.example port=5432 dbname=analytics user=ai_insights_ro sslmode=require"

If nc cannot reach the port, the problem is firewall or routing — not the platform and not your credentials. If reachability succeeds but the client login fails, the problem is authentication or TLS.

TLS and Encryption in Transit

Even though connections stay inside your network, you should still encrypt them in transit. Internal does not mean unobserved, and encrypting database traffic protects against any actor that gains a foothold on the network segment. The platform connects to databases over TLS where the database supports it, and you should configure your databases to require encrypted connections rather than merely allow them.

Certificate trust is the piece that most often needs attention:

• The application host must trust the certificate the database presents. For databases using a private or internal certificate authority, install that CA's certificate into the app host's trust store so the chain validates.
• Prefer verifying the certificate (validating the chain and hostname) over disabling verification. Turning verification off removes the protection TLS is meant to provide and should not be a standing configuration.
• Keep an eye on expiry. An expired database certificate will break previously working connections; rotate certificates ahead of expiration.

For cloud warehouses and SaaS/API sources, TLS is the default and the providers manage their own certificates; your responsibility there is to keep the app host's trust store and time synchronization current so validation succeeds.

Hardening Checklist

Use this checklist when standing up or reviewing a deployment:

• Application host is in the same trusted zone or has private, controlled routing to every database it serves.
• Firewall rules allow only the app host to reach only the specific database ports it uses; everything else is default-deny.
• No database port is reachable from the public internet.
• Remote or cross-region databases are reached over VPN, private routing, or VPC peering — never a public path.
• Each data source uses a dedicated, read-only login scoped to only the needed schemas and tables.
• Database passwords are long, random, and unique per source.
• Stored connection strings and passwords are encrypted at rest (AES-256-CBC).
• The encryption key has an owner and a rotation schedule; rotation is performed from System Settings without a restart.
• Database connections use TLS, and the app host trusts the database certificate chain.
• Connection tests and schema introspection succeed for every configured source.
• Access to data-source configuration is limited to a small set of administrators.

Troubleshooting Reference

By keeping the application inside your network, connecting directly to your databases, and applying ordinary in-network security discipline — narrow firewall rules, least-privilege read-only accounts, encrypted credentials, and TLS in transit — you get a deployment that is both simpler to reason about and easier to secure. There is no intermediary to harden, monitor, or trust, because there is no intermediary at all.