Database architecture

UserLock relies on a central database to store activity logs. Understanding how this database works and which technologies are supported helps administrators make informed choices for performance, reliability, and long-term maintenance.

Published September 10, 2025

Why UserLock needs a database

UserLock continuously records information such as:

session and authentication events,
user status changes,
administrator actions,
configuration changes.

A central database ensures this information is preserved, queryable, and consistent across the whole deployment.

Default database: SQLite

Since UserLock 13.1, the default database is SQLite. A new installation stores its data in a single local SQLite file (UserLock.db) on the UserLock server and starts recording immediately.

This option is designed for simplicity:

zero configuration: no database server to install, no driver to deploy, no credentials to manage,
single local file: the whole database is one file on the UserLock server, easy to include in the server's backups,
access is controlled by the file system permissions of the UserLock server.

Use case

👉 Suitable for evaluation and for production in small and medium environments. For large volumes, shared access between the primary and backup servers or remotely access from administration consoles, see Choosing a database.

Supported databases types and requirements

UserLock supports five database types. The drivers they need are installed automatically with UserLock: no manual driver installation is required.

Database	Supported versions	Maximum size	Notes
SQLite (default)	Included with UserLock	Limited by disk space	Zero configuration, single local file.
Microsoft SQL Server	2005 and newer, 32/64-bit	No theoretical limit	Recommended for scalability, resilience and enterprise integration.
Microsoft SQL Server Express	2005 and newer, 32/64-bit	10 GB	Most SQL Server features, with CPU and RAM limitations. Cost-effective and robust enough for production in SMB environments.
MySQL	5.6 and newer	Depends on the MySQL server	Drivers installed automatically; MySQL is available in the database type list.
Microsoft Access (legacy)	`.mdb` file	2 GB	Kept for compatibility only. Migration to SQLite is recommended.

Note

LocalDB editions of SQL Server are not compatible with UserLock.

Microsoft Access (legacy)

In versions earlier than 13.1, the default database was a Microsoft Access file (UserLock.mdb). Access is now a legacy database type: it remains supported for compatibility, so existing installations keep working after the upgrade.

Access has structural limitations:

maximum file size of 2 GB,
limited performance and scalability,
not resilient for long-term or production use.

When the current database is an Access file, UserLock offers to migrate it to SQLite. Do not choose Access for new deployments.

Note

For the migration steps in the console, see Server settings ▸ Database.

Choosing a database

SQLite is suitable for production in trials and small environments. Choose SQL Server (or SQL Server Express, or MySQL) if you expect a high volume of events, if the primary and backup servers need to share a central database server, or if you need to access the database remotely from administration consoles.

Note

The primary and backup servers each need their own database. They can share the same database server, with a different database name for each UserLock server.

Event insertion engine and database outages

UserLock introduces a new .NET insertion engine (ADO.NET) that writes events to the database. With this engine, no event is lost when the database is temporarily unavailable:

Events generated while the database cannot be reached are kept by UserLock.
They remain kept even if the UserLock service restarts in the meantime.
As soon as the connection is restored, UserLock inserts them into the database.

Note

After an upgrade to 13.1, an existing database connection keeps using the previous ODBC insertion engine until its connection settings are regenerated with the database wizard in Server settings ▸ Database. This step activates the new .NET engine, which SQLite requires.

Migration scenarios

UserLock deployments frequently evolve over time. Common migration needs include:

moving from Access to SQLite (offered by the console when the current database is an Access file),
moving from SQLite or Access to SQL Server (when event volumes grow or the primary and backup servers need a central database server),
moving from one SQL Server to another (infrastructure change, upgrade, or disaster recovery).

Migrations between any supported database types are performed with the database wizard, keeping data and history intact. See Migrate the UserLock database.

If you need help to deploy SQL Express, follow the guide on how to install and configure SQL Express.

Best practices

Use SQLite (default) for small and medium environments, and SQL Server (or SQL Server Express, or MySQL) for large volumes or when the primary and backup servers rely on a central database server.
Do not use Microsoft Access for new deployments; migrate existing Access databases to SQLite.
Do not use SQLite if you plan to access the database remotely from administration consoles. We recommend using SQL Server (or SQL Server Express, or MySQL).
With SQLite, include the database file in the UserLock server's backups.
With SQL Server or MySQL, host the database on a backed-up server.
Monitor database growth and plan maintenance (archiving, purging old logs).
Test migrations in a staging environment whenever possible.
Work with your DBA team to align with corporate standards and backup policies.

Note

For detailed configuration options in the console, see Server settings ▸ Database.

Database schema

The UserLock database is composed of four tables:

the UserLogonEvents table to record session events,
the UserStatus table to record User status,
the AdminActions table to record administrator action,
the AdminActionResults table to record results related to administrator actions.

You can find below a list of the fields included in these tables, showing their type and their purpose.

This information is provided for transparency and integration purposes.

Note

The UserLock database is openly accessible to allow integration and reporting on collected data. However, we strongly recommend not altering the schema or content.

UserLogonEvents table

Field name	Data type	Field description
EventType	integer	Interactive sessions 0 logoff / 1 logon / 2 lock / 3 unlock / 4 logon denied by UserLock/ 5 reconnection / 6 disconnection 20: Interactive logon denied by Active Directory 420: RDP/terminal logon denied by Active Directory (See 'LogonInfo' for details) Wi-Fi / VPN sessions 200 logoff / 201 logon / 204 logon denied by UserLock 220 logon denied by Active Directory (see 'LogonInfo' for details) IIS sessions 100 logoff / 101 logon / 104 logon denied by UserLock 120: logon denied by Active Directory (see 'LogonInfo' for details) SaaS sessions 500 logoff / 501 logon / 504 logon denied by UserLock UAC events 601 logon / 604 logon denied by UserLock 620: logon denied by Active Directory (See 'LogonInfo' for details)
EventTime	date time	Date and time of the logon/logoff event
UserAccount	string	User account name
UserDomain	string	User domain
UserFullName	string	User full name
ComputerName	string	Logged on workstation or terminal server
ComputerSession	integer	Terminal session identifier
ClientName	string	Logged on workstation or terminal
ClientAddress	string	Workstation or terminal IP address (MAC address for Wi-Fi sessions).
SessionId	string	Session identifier
SubSessionId	integer	Sub session identifier. Increased by 1 after each unlock event (new sub session)
Status	integer	0: For all session events, except for the cases below. 1: For: Reset done through the console. Interactive session logoff events automatically recorded in the following cases: The computer is no longer in the network zone protected by the UserLock server for at least 7 days. The advanced setting "UnavailableTimeForRemove" is configured and UserLock can not contact the computer on which the session was running for at least the number of minutes configured in this parameter. The UserLock service has detected remotely that the session no longer exists on this computer. 3: For interactive session logoff events automatically recorded after a shutdown or crash of the computer on which the session was running. For values 1 and 3: If the UserLock Desktop desktop agent on the associated computer subsequently sends the "real" interactive logoff, the database record will be automatically updated with the date of the event and the "Status" field set to "0".
ErrorId	string	Not null only if the session event could not be notified in real time to the UserLock server. This can happen in the following cases: The network connection is not yet initialized The prerequisites are not in place The UserLock service is not started
LastError	integer	Different from 0 only if the session event could not be notified in real time to the UserLock server. See the documentation for the ErrorId field above for more details.
Param1 to Param6	string	Dynamic fields used by Wi-fi/VPN/IIS logon/logoff events
Id	integer	Record id (auto)
LogonInfo	integer	The meaning of this value depends on the value of the associated EventType value. Detailed reasons for Logon succeeded, when associated 'EventType' = 1, 3, 5, 101, 201 or 501: 128: MFA successful (unless the value of the "SkipReason" field is other than "-1", see the documentation for this field below). Detailed reasons for Logon denied by UserLock, when associated 'EventType' = 4, 104 or 204 1: Group restriction 2: Workstation restriction 4: Time restriction 8: Time Quota restriction 16: Session restriction 32: Initial Access Point restriction 64: The user account is blocked by UserLock 128: MFA failed (when a user types a wrong MFA code and their session is refused) 256: MFA cancelled (when a user clicks on the “Cancel” button of an MFA window) 512: MFA help request (when a user clicks on the “Ask for help” button of an MFA window). 1024: Geolocation restriction 2048: UserLock inaccessible Logon denied by UserLock can be caused by several reasons. Therefore the value recorded for logon denied by UserLock is a cumulative value. For example, 25 means that group, session and time quota restrictions were applied (1+16+8). Detailed reasons for Logon Denied by Active Directory, when associated 'EventType' 20, 220 or 420: 0: Unknown 1: Invalid user 2: Invalid password 4: Account locked out 8: Domain controller and workstation clocks are skewed 16: Password must be changed before login 32: Active Directory restriction 64: Account restriction 128: Logon hours restriction 256: Account disabled 512: Workstation restriction 1024: Account expired 2048: Password expired As logons denied by Active Directory have an exclusive reason, only a single value from this list will be stored. Detailed reason for Logon Denied by Active Directory, when associated 'EventType' 120: 2: Invalid password
SessionType	integer	Session Type: 1 = Workstation 2 = Terminal 4 = IIS 16 = VPN 32 = Wi-Fi 64 = SSO 256 = UAC
ServerAddress	string	The IP address of the computer where the session event took place.
TimeZoneShift	integer	Time difference with UTC (Coordinated Universal Time), in minutes. This field is only filled in for interactive session events.
SkipReason	integer	Related to the "Skip" button optionally displayed in the MFA enrollment dialog box. -1: Skip was not clicked. 1: Skip was clicked, then the following reason was selected: "I forgot my smartphone". 2: Skip was clicked, then the following reason was selected: "I don't have a smartphone". 3: Skip was clicked, then the following reason was selected: "Other technical issue".
Country	string	Country from which the connection was established

UserStatus table

Field name	Data type	Field description
Id	integer	Record id (auto)
Status	integer	Current status of the user: 0: Normal unprotected 1: Normal protected 2: Inactive 3: New user 4: Suspicious behavior 5: Bad behavior
Triggers	integer	Flags of remarkable conditions to help deduce the current status for the user. Values in hexadecimal: 0x00000001: Protected 0x00000002: First event 0x00000004: Event 0x00000008: Event after period 0x00000010: Inactivity 0x00000020: Access denied because account is locked by Active Directory 0x00000040: Sessions over limit 0x00000080: Sessions over a figure 0x00000100: Access denied by UserLock 0: Access denied by Active Directory
OldStatus	integer	Previous status of the user: 0: Normal unprotected 1: Normal protected 2: Inactive 3: New user 4: Suspicious behavior 5: Bad behavior
OldTriggers	integer	Flags of remarkable conditions to help deduce the previous status for the user. Values in hexadecimal: 0x00000001: Protected 0x00000002: First event 0x00000004: Event 0x00000008: Event after period 0x00000010: Inactivity 0x00000020: Access denied because account is locked by Active Directory 0x00000040: Sessions over limit 0x00000080: Sessions over a figure 0x00000100: Access denied by UserLock 0: Access denied by Active Directory
UserAccount	string	User account name
UserDomain	string	User domain
UserFullName	string	User full name
SessionId	string	Session identifier
SubSessionId	integer	Sub session identifier. Increased by 1 after each unlock event (new subsession)
EventType	integer	Interactive sessions 0 logoff / 1 logon / 2 lock / 3 unlock / 4 logon denied by UserLock/ 5 reconnection / 6 disconnection 20: Interactive logon denied by Active Directory 420: RDP/terminal logon denied by Active Directory (see the 'LogonInfo' field of the 'UserLogonEvents' table for details) Wi-Fi / VPN sessions 200 logoff / 201 logon / 204 logon denied by UserLock 220 logon denied by Active Directory (see the 'LogonInfo' field of the 'UserLogonEvents' table for details) IIS sessions 100 logoff / 101 logon / 104 logon denied by UserLock 120: logon denied by Active Directory (see the 'LogonInfo' field of the 'UserLogonEvents' table for details)
EventTime	date time	Date and time of the logon/logoff event

AdminActions table

Field name	Data type	Field description
Id	integer	Record id (auto)
ServerName	string	Server name
Kind	integer	0: action on the sessions 1: action on the machines 2: action on the users 3: action on the protected accounts (policies) 4: action on the server properties
Action	integer	0: install agent(s) 1: uninstall agent(s) 2: Restart machine(s) 3: Shutdown machine(s) 4: Wake up machine(s) 10: Logoff session(s) 11: Lock session(s) 12: Reset session(s) 13: Send a popup to session(s) 20: Reset Mfa Key 21: Reset Mfa Help Me Status 30: Create Policy 31: Update Policy 32: Delete Policy 33: Block a user 34: Unblock a user 35: Disable Mfa 40: Update Server Settings 41: Update Server version
DateStart	date time	Date time when admin action started
DateEnd	date time	Date time when admin action ended
UserAccount	string	User account name initiating the action
Priority	integer	0: Low 1: Medium 2: High
Severity	integer	0: Minor 1: Moderate 2: Major 3: Critical
DisplayState	integer	0: Unread 1: Read
UserAccountFullName	string	User account full name initiating the action
Domain	string	Domain name

AdminActionResults table

Field name	Data type	Field description
Id	integer	Record id (auto)
ServerName	string	Server name
AdminActionId	integer	AdminAction identifier
Item	string	The setting changed by the admin action
Param1	string	Dynamic fields
Param2	string	Dynamic fields
Param3	string	Dynamic fields
Param4	string	Dynamic fields
Param5	string	Dynamic fields
Param6	string	Dynamic fields
StateResult	integer	0: Succeeded 1: Canceled 2: Failed
Details	string	Details about the admin action