Some large sites maintain an external database of user's and their card numbers. We usually recommend sites use the built-in card import (see here), or directly from Active Directory. However in some situations it is more convenient to use SQL to efficiently update the user's card numbers (and optionally card PINs) in PaperCut.

The approach involves creating and populating an import table and then running an SQL statement to update the card numbers on the PaperCut users.

This article describes how to achieve this using Microsoft SQL Server database.

1. Prerequisites

• PaperCut must be configured to run on Microsoft SQL Server as described here.
• A source of card numbers linking them to usernames is required.
• Familiarity with SQL Server to run and configure database scripts.

2. Create the card import table in the PaperCut database.

Create a table in the PaperCut database (in the SQL Server instance PaperCut is currently running on) called ext_user_card_number_import. This table should be defined with the following fields:

• user_name varchar(50) not null (primary key)
• card_number varchar(100) not null (unique index)

A script for creating this table can be downloaded here: create_user_card_table.sql. This should be run using the SQL Server Management Studio or equivilent.

3. Populate the card import table

The next step is to populate the import table (ext_user_card_number_import) with usernames and card numbers from the external system. How this is achieved is depends on where this data is stored its format. Examples of how the import table can be populated may include:

• Write a custom program or script to transform and import the data into the table.
• Use SQL Server integration services to import the data from another data source.
• If the data is in the same SQL server instance it may be possible to import it directly with a single cross-database SQL statement.

The following rules should be applied to imported data:

• user_name field should match a name in the the tbl_user.user_name field.
• card_number field must be unique, and should be normalized to lower-case and remove leading and trailing spaces.

This step should be scheduled to regularly when card numbers in the source system are updated.

4. Run SQL to update card numbers in PaperCut

Once the import table ext_user_card_number_import is populated/updated, the PaperCut user records can be updated with the new card numbers.

This is done with the following SQL statement:

 update tbl_user set 
     card_number = LOWER(LTRIM(RTRIM(eucn.card_number))),
     modified_date = GETDATE(),
     modified_by = '[external card number import]'
 from
     tbl_user inner join ext_user_card_number_import eucn
         on tbl_user.user_name = eucn.user_name
 where
     tbl_user.deleted = 'N'
     and tbl_user.internal = 'N'
     and tbl_user.card_number <> eucn.card_number

Manually run this SQL command and verify that numbers are updating as expected.

This statement is very efficient, only updating the records that require updating. Performance testing on database containing 350,000 users and 350,000 records in the ext_user_card_number_import table showed:

• Updating all 350,000 users' card numbers - 30 seconds.
• Updating 1 user's card number in 350,000 users - less than 1 second.

The above SQL will only update users that are in the ext_user_card_number_import table. If you would prefer to clear the card number field in PaperCut if the user doesn't exist in the ext_user_card_number_import table then you should also run the following SQL statement.

 update tbl_user set
     card_number = '',
     modified_date = GETDATE(),
     modified_by = '[external card number import]'
 where 
	tbl_user.deleted = 'N'
	and tbl_user.internal = 'N'
	and tbl_user.card_number <> ''
	and not exists (
		select user_name 
		from ext_user_card_number_import eucn 
		where 
			tbl_user.user_name = eucn.user_name
	)

5. Scheduling regular updates

To keep the PaperCut card numbers up-to-date with the source card system, steps 3 and 4 above should be scheduled to run on a regular basis.

If steps 3 and 4 are scheduled independently, ensure that step 4 is scheduled to start after step 3 has completed.

Note on data consistency

This method should be used to only update existing users in the database. PaperCut does not support adding new users via SQL.

Is is important to ensure no duplicate card numbers are imported into PaperCut. If two users are assigned the same card number, then neither user will be able to use the card number for authentication in PaperCut.

If card numbers are never reassigned to users, then it should not be possible to create duplicate card numbers in PaperCut using this technique.

To check that there are no duplicate card numbers in PaperCut, the following query can be run and should return no results.

 select card_number, count(*)
 from tbl_user 
 where deleted = 'N' and (card_number is not null and card_number <> '')
 group by card_number having count(*) > 1 

SQL Administrators may wish to run this command from time to time.

If you have any questions about this procedure, or need to make a modification to support custom requirements and would like ti checked, please feel free to contact our development team at support@papercut.com .

Categories: Scripting