How to configure an external database for card/ID number lookups

For PaperCut NG/MF Versions 26.0.5 and later, 25.0.13 and later, and 24.1.10 and later, configuration for the External Database User Lookup feature is managed exclusively via the security.properties configuration file. Configuring this feature through the Admin web interface has been retired.

Location of the security.properties configuration file

• Windows: C:\Program Files\PaperCut MF\server\security.properties

• Linux: /home/papercut/papercut/server/security.properties

• macOS: /Applications/PaperCut MF/server/security.properties

Operating system and file permissions

Ensure that the security.properties file has restricted file permissions. It must be readable and writable only by the PaperCut service user account and system administrators.

Editing the security.properties file

IMPORTANT: This procedure requires administrator access.

Due to operating system-level file protection, the security.properties file cannot be edited directly in its default directory on Windows, Linux, or macOS operating systems. To edit this file:

1. Go to the [app-path]/server/ directory.

2. Copy the security.properties file and paste it into an alternate location, ready for editing.

3. Open the file using a text editor with administrator privileges.

4. Refer to the Configuration keys and parameters table below and add the keys and parameters to the file. You can either type them or copy them from the security.properties.template file.

5. Copy the edited file back to [app-path]/server/, replacing the existing file when prompted by the operating system. (Requires administrator confirmation.)

6. Restart the Application Server.

Configuration keys and parameters

¹ security.card-number-lookup.db-driver
If you are using MS SQL Server and you upgraded the driver version in step 3, also review Upgrading the MS SQL JDBC Driver, Step 4 - Modify the PaperCut NG/MF SQL connection string.

² security.card-number-lookup.db-url
For examples, see Upsize to an external database (Change the PaperCut NG/MF connection details).

³ security.card-number-lookup.id-to-username-sql
For SQL formatting tips:

• Do not wrap the {cardnumber} placeholder in quotation marks in your SQL statement. PaperCut handles parameter binding automatically.

• Placeholders are case-insensitive and tolerant of whitespace: {cardnumber}, {CardNumber}, or { cardnumber } are all valid.

• Multiple occurrences of {cardnumber} in the query are supported.

⁴ security.card-number-lookup.id-to-username-sql.user-mapping
This allows you to choose what the card number in your external database maps to. Select the option that matches the mapping in your external database. The options include:

Database connection details and examples

Microsoft SQL Server

If you're using MS SQL Server version 2016 or later, we recommend updating the driver for performance reasons. If you haven't already performed these steps when migrating your PaperCut Application Server to run on MS SQL, then see Upgrading the MS SQL JDBC Driver (Step 3 - Upgrade the JDBC Driver and auth dll) to upgrade your MS SQL Driver.

security.card-number-lookup.enabled=Y
security.card-number-lookup.db-driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
security.card-number-lookup.db-url=jdbc:sqlserver://sql-server.local:1433;databaseName=DoorAccessDB;encrypt=true;trustServerCertificate=true
security.card-number-lookup.db-username=papercut_svc
security.card-number-lookup.db-password=StrongPassword!
security.card-number-lookup.id-to-username-sql=SELECT sAMAccountName FROM Badges WHERE CardNumber = {cardnumber}
security.card-number-lookup.id-to-username-sql.user-mapping=USERNAME

Oracle Database

If using Oracle, if you're not already using an Oracle database to run your PaperCut Application Server, download the database driver as documented in Configuring Oracle.

security.card-number-lookup.enabled=Y
security.card-number-lookup.db-driver=oracle.jdbc.driver.OracleDriver
security.card-number-lookup.db-url=jdbc:oracle:thin:@oracle-db.local:1521:ORCL
security.card-number-lookup.db-username=PAPERCUT_USER
security.card-number-lookup.db-password=SecretPassword
security.card-number-lookup.id-to-username-sql=SELECT user_name FROM door_access_records WHERE badge_id = {cardnumber}
security.card-number-lookup.id-to-username-sql.user-mapping=USERNAME

PostgreSQL

If you're not already using a PostgreSQL database to run your Application Server, download the database driver as documented in Configuring a PostgreSQL database.

security.card-number-lookup.enabled=Y
security.card-number-lookup.db-driver=org.postgresql.Driver
security.card-number-lookup.db-url=jdbc:postgresql://postgres-db.local:5432/identity_db
security.card-number-lookup.db-username=papercut_reader
security.card-number-lookup.db-password=PostgresPassword123
security.card-number-lookup.id-to-username-sql=SELECT email_address FROM card_mappings WHERE card_uid = {cardnumber}
security.card-number-lookup.id-to-username-sql.user-mapping=EMAIL

MySQL / MariaDB

If using MySQL, if you're not already using a MySQL database to run your PaperCut Application Server, download the database driver as documented in Configuring MySQL.

security.card-number-lookup.enabled=Y
security.card-number-lookup.db-driver=com.mysql.cj.jdbc.Driver
security.card-number-lookup.db-url=jdbc:mysql://mysql-db.local:3306/dooraccess?useSSL=true
security.card-number-lookup.db-username=papercut_lookup
security.card-number-lookup.db-password=MySqlPassword123
security.card-number-lookup.id-to-username-sql=SELECT login_name FROM staff_cards WHERE badge_num = {cardnumber}
security.card-number-lookup.id-to-username-sql.user-mapping=USERNAME

For PaperCut NG/MF Versions 26.0.3 and earlier, 25.0.11 and earlier, and 24.1.9 and earlier, configuration for the External Database User Lookup feature is managed through the PaperCut NG/MF Web admin interface.

1. Select Options > Advanced. The Advanced page is displayed.

2. In the External User Lookup area, select the Use external database for card number lookup checkbox.

3. Select the Database type. The options include:

   • Microsoft SQL Server

     • If you're using MS SQL Server version 2016 or later, we recommend updating the driver for performance reasons. If you haven't already performed these steps when migrating your PaperCut Application Server to run on MS SQL, then see Upgrading the MS SQL JDBC Driver (Step 3 - Upgrade the JDBC Driver and auth dll) to upgrade your MS SQL Driver.

   • Microsoft SQL Server (Legacy)

     • This option only appears if you're using PaperCut NG/MF version 24.1.7 or later.

     • Do not use this option unless there's some reason that you need to continue to use the very old net.sourceforge.jtds.jdbc.Driver (Sourceforge JTDS SQL driver).

     • If you have been using External User Lookup on previous versions of PaperCut NG/MF (using the old Sourceforge JTDS driver by default), when you upgrade to PaperCut NG/MF version 24.1.7 or later, you will see Microsoft SQL Server (Legacy) selected.

     • We recommend to move to the supported MS SQL JDBC Driver, as documented in Upgrading the MS SQL JDBC Driver (Step 3 - Upgrade the JDBC Driver and auth dll), select Microsoft SQL Server as the Database Type (see above), and then update your database connection URL accordingly.

   • MySQL

     • If you're not already using a MySQL database to run your PaperCut Application Server, download the database driver as documented in Configuring MySQL.

   • Oracle

     • If you're not already using an Oracle database to run your PaperCut Application Server, download the database driver as documented in Configuring Oracle.

   • PostgreSQL

     • If you're not already using a PostgreSQL database to run your Application Server, download the database driver as documented in Configuring a PostgreSQL database.

4. Enter the Database connection URL. For examples see Upsize to an external database (Change the PaperCut NG/MF connection details).

5. Enter the database connection username and password.

6. Configure the option: SQL to map card number in external database to.
   
   This option allows you to choose what the card number in your external database maps to. Select the option that matches the mapping in your external database. The options include:

   Lookup field	Use if your external database contains a mapping between card numbers and one of the following:
   username	Usernames
   user's identity number	User Card/IDs. The IDs must have been imported and stored in PaperCut NG/MF.
   username alias	username aliases. The username aliases must have been imported into PaperCut NG/MF and enabled. See Enable username aliasing.
   alias	Usernames OR aliases. A match with a username is tried first, and if no match is found, a second search looks for a match with the alias.
   email	Email addresses.

7. Enter an SQL select query that looks up the card number in your external database and returns either a username or user id as selected above. The query must return a single row with the first field being the username or user id (as found in PaperCut NG/MF). The SQL statement must contain {cardnumber}, which is replaced with the card number to find.
   
   Some example queries are:

   Lookup method	Query
   email	select email from users_table where card_number = {cardnumber}
   username alias	select user_alias from users _ table where card_number = {cardnumber}
   username / username or alias	select user_name from users_table where card_number = {cardnumber}

8. Restart the PaperCut NG/MF Application Server service.

Unlike legacy configurations set up in the admin interface, the security.properties configuration file and any custom JDBC database drivers do not automatically sync from the Application Server to Site Servers.

If you wish to configure external database card lookup on a Site Server level, you must manually copy your configured security.properties file from the Application Server [app-path]/server/ directory to the corresponding location on the Site Server. You must also manually copy your JDBC driver files to the [app-path]/server/custom/lib/ directory on each of your Site Servers.

Note that this is particularly important for MS SQL, where you should ensure that the database driver is upgraded as documented in Upgrading the MS SQL JDBC Driver (Step 3 - Upgrade the JDBC Driver and auth dll). Oracle and MySQL databases don't have a database pre-installed, so will need the driver to be installed as above too.

Configuration for the External Database User Lookup feature is managed through the PaperCut NG/MF admin interface.

The External database lookup configuration (configured through the steps on this page) will automatically sync to the Site Servers.

Other configuration items (such as database drivers) will not automatically sync, and will need to be applied as above. If you're using a database type that requires database drivers to be installed or updated, then you will need to perform these same steps on the Site Server.

Note that this is particularly important for MS SQL, where you should ensure that the database driver is upgraded as documented in Upgrading the MS SQL JDBC Driver (Step 3 - Upgrade the JDBC Driver and auth dll). Oracle and MySQL databases don't have a database pre-installed, so will need the driver to be installed as above too.