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, select the Use external database for card number lookup checkbox.

3. Select the Database type.

   • If using Oracle, you’ll need to download the database driver as documented in Configuring Oracle , if you’re not already using an Oracle database to run your PaperCut Application Server.
   • If using MySQL, you’ll need to download the database driver as documented in Configuring MySQL , if you’re not already using a MySQL database to run your PaperCut Application Server.
   • If using MS SQL Server version 2016 or later:
     • It’s recommended to update the driver for performance reasons. If you haven’t already done this because your PaperCut Application Server is running on an MS SQL database, see Upgrading the MS SQL JDBC Driver (Step 3 - Upgrade the JDBC Driver and auth dll) .
     • You’ll also need to use the advanced config editor to update the config key user-lookup.db-driver to com.microsoft.sqlserver.jdbc.SQLServerDriver.
       IMPORTANT: There is currently an issue where if you later hit Apply anywhere on Options > Advanced to save any other settings, it will overwrite this key with the default net.sourceforge.jtds.jdbc.Driver. If this happens you’ll need to re-update the config key with the SQLServerDriver string anytime you hit Apply.

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

   • 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 .

5. Enter the database connection username and password.

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

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.