Configuring Ignition Database User Sources for 400+ Users

Where does a login go once the users live in a SQL table?

Follow the packet. An operator types credentials into a Vision client or a Perspective session. The client sends them to the gateway. For Vision, the project's user source handles the login. For Perspective, the project's identity provider handles it, and an Ignition-type identity provider points at a user source. The user source then runs queries through a gateway database connection against your tables. Any hop that is misconfigured or down produces the same symptom: "invalid username or password".

Ignition ships a database-backed user source type, so you can take user management off the gateway's internal list. The type has two modes, and the choice decides who owns the table:

Use manual mode when user registration is automated outside Ignition, or when the ticket UI writes user rows itself. You maintain every insert, update, disable, and password change in your own database. Manual mode never writes back.

Check before moving on: decide on the mode and write down which application owns user CRUD. If that owner is unclear, the queries you build in the next steps will drift away from the data.

Is the database connection up and the schema ready?

Start at layer one. The user source can only be as healthy as the gateway database connection under it. Create or reuse a connection under the gateway's database connections page. Confirm its status reads Valid before you touch the user source. A connection that goes faulted takes every database-backed login down with it.

Next, build a minimal schema. The names below are illustrative. Map them to your own tables:

-- illustrative schema
CREATE TABLE app_users (
  user_id       INT PRIMARY KEY,
  username      VARCHAR(64) UNIQUE NOT NULL,
  password_hash VARCHAR(128) NOT NULL,
  first_name    VARCHAR(64),
  last_name     VARCHAR(64),
  enabled       BIT NOT NULL DEFAULT 1
);
CREATE TABLE app_roles (
  role_id   INT PRIMARY KEY,
  role_name VARCHAR(64) UNIQUE NOT NULL
);
CREATE TABLE app_user_roles (
  user_id INT REFERENCES app_users(user_id),
  role_id INT REFERENCES app_roles(role_id)
);

Store hashes, not plaintext. The manual user source has a password-hashing setting on its edit page. Read the available algorithms there, then configure your registration app to write the password_hash column with that same algorithm and encoding, including hex case.

Check: run SELECT COUNT(*) FROM app_users from the Designer Database Query Browser against the same connection name the user source will use. A result proves that the connection, the credentials, and the table visibility all work.

How do the manual-mode queries map to your schema?

In manual mode, Ignition knows nothing about your columns. Each function of the user source is a SELECT statement that you supply. If a statement returns the wrong shape, users fail to log in or come through without roles. The exact field labels and the expected result columns are shown on the user source edit page and in the Ignition user manual. Build each query to that contract:

-- authentication (placeholders bound by Ignition)
SELECT user_id FROM app_users
 WHERE username = ? AND password_hash = ? AND enabled = 1;

-- roles for one user
SELECT r.role_name FROM app_roles r
  JOIN app_user_roles ur ON ur.role_id = r.role_id
  JOIN app_users u ON u.user_id = ur.user_id
 WHERE u.username = ?;

Put the enabled filter in the authentication query. That lets your registration app disable an associate without deleting the row or the ticket history tied to it.

Check: paste each query into the Query Browser with literal values substituted for the placeholders. The authentication query must return exactly one row for good credentials and zero rows for a wrong hash. Then open the user source's user list in the gateway and confirm it matches your table's row count.

How does the project hand logins to the new user source?

Creating the user source does nothing until a project references it. Choose the path by client type:

Role names returned by your roles query must match, character for character, the role or security-level names used in component and view security. A role spelled differently in the table than in the project grants nothing.

Check: log in with a test account that holds one role, then with an account that holds none. Confirm that secured components show and hide as expected for each.

How do you migrate the existing 400 gateway users?

Profile data moves with set-based SQL. If you already have a staging table, INSERT INTO ... SELECT ... FROM copies users and role assignments in one pass:

INSERT INTO app_users (user_id, username, first_name, last_name, password_hash, enabled)
SELECT id, login, fname, lname, '<reset-required>', 1
  FROM staging_users;

Passwords do not migrate. The internal user source stores them hashed, and the hash will not match your chosen algorithm. Force a reset for every account through your registration app, or issue temporary passwords that your app hashes correctly.

Check: compare row counts between the source list and app_users. Then count role assignments per role before and after the migration.

What breaks after cutover, and how do you prove the full path?

Keep gateway configuration login on an internal user source with at least one admin account. If the gateway config login depends on the database, a database outage locks you out of the gateway you need to fix it.

1. Confirm the database connection status is Valid.
2. In your registration app, register a brand-new associate. Do not create the account in Ignition.
3. Confirm the row appears in app_users with a hashed password.
4. Confirm the user appears in the gateway's user list for the database source.
5. Log in from a Perspective session or Vision client with that account and open the ticket UI.
6. Create a ticket and confirm it records the logged-in username.
7. Set enabled = 0 on that user and confirm the next login attempt is rejected.

FAQ