authentication

Somepharm SQL Server authentication with two outputs

Package Information

Released: this month
Downloads: 0 weekly / 0 monthly
Latest Version: 1.0.0

Available Nodes

Documentation generating…

Somepharm Authentication icon
Somepharm Authentication
Validate an encrypted timestamp and trusted proxy username using SQL Server

Documentation

Somepharm Authentication

Private self-hosted n8n package: n8n-nodes-authentication, version 1.0.0.
Replaces the supplied SQL Authentication check and If Authentication nodes.
The default canvas name is Authentication, so an authorization expression such
as $('Authentication').item.json.REF_USER can reference it directly.

Settings

Select your existing Prod Microsoft SQL credential. Its Database MUST be the
application database containing dbo.DECRYPTBYPASSPHRASE_Somepharm. The user
lookup is explicitly against DROITSMK_LA.dbo.T_USERS.

Setting Default expression/value
Timestamp {{ $json.formQueryParameters?.randomValue ?? "" }}
Encrypted Value {{ $json.formQueryParameters?.crypt ?? "" }}
AD Username {{ $json.headers?.["x-forwarded-preferred-username"] ?? "" }}
Maximum Age 60 minutes

Place this node immediately after the form trigger, or change the expressions
to refer to the relevant upstream node. Missing fields evaluate to an empty
string and are rejected. Custom expressions that themselves throw errors stop
execution. The account needs access to the decryption function and user table.
The existing database function is required; this package does not create it or
contain its passphrase.

Outputs and wiring

  • Output 0 Authenticated: Auth_Result: 1, authenticated: true, and REF_USER.
  • Output 1 Rejected: Auth_Result: 0, authenticated: false, and REF_USER: "".

IMPORTANT: the original IF true output meant rejection. Connect the old IF false
branch to Authenticated, and the old IF true branch to Rejected.
Connect Authenticated to Somepharm Authorization; its User Reference can be
{{ $json.REF_USER }}. Connect Rejected to your authentication error response.
Incoming JSON and binary data are retained, with item pairing preserved. Existing
REF_USER, Auth_Result and authenticated fields are overwritten. Other incoming
fields, including any stale authorization fields, are retained: perform a new
authorization check before accessing protected operations.

Keep On Error = Stop Workflow and Always Output Data = off. Database outages,
SQL errors, bad node settings and unexpected SQL results stop the execution;
they are not successful logins and are not sent down the Rejected output.

Authentication rules

  1. randomValue is an integer UNIX timestamp in seconds, between 0 and 2147483647,
    matching the original SQL int constraint (through January 2038).
  2. crypt is passed as a SQL parameter to the existing decryption function.
  3. Decrypted text must convert to the same integer timestamp.
  4. Timestamp is within the inclusive window [SQL UTC now minus maximum age, now].
    Future timestamps are rejected. SQL Server supplies the time, not the browser.
  5. AD_USER matches exactly one database row, with a REF_USER length of 4–50.

Changes from the original: values are bound parameters; malformed inputs reject
without SQL; TRY_CONVERT rejects nonnumeric decrypted text; duplicate AD_USER
rows reject rather than choosing an arbitrary account. The username limit is
256 characters and crypt limit is 16384 characters. The code does not impose a
Base64 format because the supplied custom decryption function is not available.
If that function throws for malformed tokens, execution stops.

The header must be set/overwritten by a trusted authentication proxy. Prevent
clients from reaching this endpoint directly and supplying their own identity
header. This token proves a timestamp, not its binding to a particular user;
identity relies on the proxy. This preserves the provided scheme and does not
add replay prevention or single-use tokens.

Install locally

Extract the ZIP and copy its TGZ to the n8n host. Run as the n8n OS user:

mkdir -p ~/.n8n/nodes
cd ~/.n8n/nodes
npm install /path/to/n8n-nodes-authentication-1.0.0.tgz --omit=dev

Restart n8n. Install consistently for all main/worker instances and use persistent
storage for container deployments. Node.js 20+ and npm registry access are needed.
Meet your installed n8n version's Node.js requirement as well.

Publish, then install through the UI

This package has NOT been published. The npm name's availability has not been
checked. From the extracted source folder, after choosing to publish publicly:

npm login
npm pkg delete private
npm ci
npm test
npm publish --dry-run
npm publish --access public

Publishing exposes the code and database/function names. No passwords are
included. The example credential is a reference only and is excluded from the
npm tarball. Public npm publication requires your own account and authentication.
If another publisher owns the name, choose an available name and update the
example JSON node type prefix to match.

After publication, use n8n Settings > Community Nodes > Install and enter
n8n-nodes-authentication@1.0.0. Use an Owner/Admin account with community nodes
enabled. UI installation does not accept the ZIP as a workflow import.
The included examples/replacement-node.json can be pasted onto the canvas only
after the custom package is installed. Select your Prod credential if needed.

Validation and sources

Run npm ci and npm test. Nine tests use mocked SQL responses and real
n8n-workflow/mssql modules. They cover input validation, routing, pairing, bound
parameters, connection failures, malformed responses and cleanup. They do not
execute T-SQL or the private decryption function. Live n8n loading and SQL Server
integration were not available. Before production, verify valid/expired/future
and tampered tokens, unknown/duplicate users, and exact time boundaries in Dev.

Discussion