Does this site look plain?

This site uses advanced css techniques

WARNING: This procedure is absolutely not supported by iSystems, and they will rightly object to bypassing of normal system security. It's possible to completely trash your Evolution service bureau file if you get this wrong, so take the most extreme caution. If not entirely sure, get help.

The Evolution payroll service-bureau software maintains several user security levels, with the highest being an administrator. Usually at least two accounts are of this status (admin and superuser), though more may be defined.

With the Franklin release (2004), iSystems introduced account lockout as a security measure, where too many incorrect passwords leads to the account being blocked; this happens with remarkable frequency. This includes the administrator account, and it's not unheard of that an installation can find themselves with no access to any of these accounts.

Table of Contents
  1. Using isql
  2. Unlocking a blocked account
  3. Resetting a lost password
  4. Doing this fropm Windows

In this document, inputs to the shell are in bold, while SQL inputs are in red. All SQL statements end in a semicolon, and it doesn't matter whether the statement is all one long line, or broken up into several (as long as the semicolon is at the end). We typically break up a statement so as to be most readable, but this is not necessary.


Using isql

Evolution uses the Firebird SQL database on the Linux servers, and we'll be using the isql command to talk directly to the S_BUREAU.gdb file which contains the list of all users.

The database files are typically found in the /db/evolution/ directory, and our work should be done as root. Make a backup of the S_BUREAU.gdb file, perhaps by copying to the /tmp/ directory. 

# cd /db/evolution

# cp S_BUREAU.gdb /tmp/

Once a copy of the file has been squirreled away, we're ready to ask the original some questions. Using the isql command, we ask for all the administrator accounts. Note that virtually all of the Evolution database files have change records, so we only want the current record of the given type (as identified by the ACTIVE_RECORD field): 

# isql -u EUSER -p password localhost:/db/evolution/S_BUREAU.gdb
Database:  /db/evolution/S_BUREAU.gdb
SQL> select USER_ID from SB_USER
SQL>  where SECURITY_LEVEL = 'A'
SQL>    and ACTIVE_RECORD = 'C';

USER_ID
================================================================
ADMIN            «--- the main administrator account
Super            «--- the superuser account

SQL> control-D   --- to exit

The names (including upper/lower case) will vary on different installations, but the roles will surely be found. Make a note of the name that's to be reset.

Unlocking a blocked account

When account lockout has occurred, it can be reset by setting the bad-password count back to zero for the given account. As with the above, the isql command is run as root on the S_BUREAU file. This time we use an update to modify the data rather than select which merely queries it.

WARNING: This command modifies data - mistakes here can be very painful.

Note that the username must be specified exactly as found in the previous query step: ADMIN, Admin and admin are all different user names, and a mismatch means that nothing gets updated. 

# isql -u EUSER -p password localhost:/db/evolution/S_BUREAU.gdb
SQL> update SB_USER set WRONG_PSWD_ATTEMPTS = 0
SQL>  where UPPER(USER_ID) = 'ADMIN'
SQL>    and ACTIVE_RECORD = 'C';

SQL> control-D    --- to exit

Resetting a lost password

it's not generally possible to recover a password that's been lost -- Evolution uses one-way hashing that is not reversible -- but it's possible to reset a password to a known value.

WARNING: This command modifies data - mistakes here can be very painful.

We set a user password to a known value - "evolution" - and then queried the database to find the hash: this is the long string that appears here in this update statement. We strongly recommend performing a cut-and-paste rather than try to type this long word manually. And of course, the name of the administrator account must match exactly. 

# isql -u EUSER -p password localhost:/db/evolution/S_BUREAU.gdb
SQL> update SB_USER
SQL>  set USER_PASSWORD = 'Pb1.FbOy/5IHqTLn15xxtYjf0ElKHI81',
SQL>      WRONG_PSWD_ATTEMPTS = 0
SQL>  where UPPER(USER_ID) = 'ADMIN'
SQL>    and ACTIVE_RECORD = 'C';

SQL> control-D   --- to exit

Once this command has run, the ADMIN user will find an unlocked account with the password of evolution; the user should login and change the password to something much more suitable.

This can be packaged into a simple shell script that can be kept in the /usr/local/bin/ directory, but we'd hope that this would not be needed so often so that this step was necessary.

/usr/local/bin/evo-reset-admin
#!/bin/sh
#
# resets "admin" password to "evolution"
#

isql -u EUSER -p password localhost:/db/evolution/S_BUREAU.gdb <<END
update SB_USER
 set USER_PASSWORD = 'Pb1.FbOy/5IHqTLn15xxtYjf0ElKHI81',
     WRONG_PSWD_ATTEMPTS = 0
 where UPPER(USER_ID) = 'ADMIN'
   and ACTIVE_RECORD = 'C';
END

Doing this from Windows

Our instructions so far have been based on running isql directly on the Linux database server, but since Evolution requires the Firebird client on the middle-tier machines, we can do so from Windows as well. The SQL commands are identical: the only difference is how we connect to the database (over the network versus on the local machine).

This work is done at the MS-DOS command prompt (via the cmd command), and though the Firebird bin\ directory ought to be in the command %PATH%, it's not always the case. We'll change to the directory holding the isql.exe command directly to obviate this issue. 

C> C:

C> cd \Program Files\Firebird\Firebird_2_0\bin

C> isql -u EUSER -p password dbserver:/db/evolution/S_BUREAU.gdb

SQL> enter your SQL here

SQL> control-Z --- to exit

Here, dbserver should be replaced with the hostname or IP address of the Linux database server, and the EUSER password is typically pps97.

Otherwise, the SQL commands should be run exactly as they are found in the previous sections.

We cannot emphasize strongly enough how dangerous this program is in the hands of one not entirely certain of what he is doing: don't try this without being sure, and never do it without a backup of the S_BUREAU.gdb file handy.

This Evo Tip is positively not endorsed, approved, or supported by iSystems, LLC.

First published: 2005/08/05