Configuring an external database for card/ID numbers

PaperCut NG/MF can look up Card/ID Numbers from an external database, such as one used to manage secure door access.

After external user lookups are enabled, PaperCut NG/MF does the following when looking up a user by card number:

1. Looks for a user with the matching card number in the PaperCut NG/MF database.
2. If not found, it looks up the card number in the external database.
3. If it finds a match, it uses the information returned to find the matching user in the PaperCut NG/MF database. If a user is found the lookup is successful.

Configure external database lookup

To enable external card number lookups:

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

2. In the External User Lookup area, check 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.
     • Our recommendation is 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 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 .
   • Oracle
     • 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 .
   • PostgreSQL

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 SQL to map card number in external database to option:
   
   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.

Site Server configuration

If you are using the External Database Lookup feature as above, and using Site Servers in your environment (and you have users logging into devices connected to those Site Servers), then you might need to perform additional configuration on your Site Servers.

• 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 from Step 3 above 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.

Testing database lookup

1. Click the Users tab.
   The User List page is displayed.
2. Pick a card number from your external database that maps to a user in PaperCut NG/MF.
3. In Quick Find, enter this card number, then click Go.
4. Verify that the matching user is displayed. If the expected user is not displayed check the App Log tab for errors.
5. [Optional] If you have Site Servers in your environment, make sure that you can successfully log into a device which is connected to a Site Server (not directly connected to the Application Server) with an ID / Card number which is stored in your external database.