Today the person managing the finances where I work told me she needed to access old simply accounting (now Sage 50) files on the legacy Windows computer we keep for that kind of reason.
We switched to LibreOffice Calc for our accounting when I started working there, but the company used to use Sage 50.
The plan seemed simple: open the files and export the data to CSV. Sadly the file was password protected and no one could remember what damn password they used.
If you are reading this, please take a moment to reflect on your password policies. People like me have to deal with shit like this because people still don't use password managers. It's simple: USE A PASSWORD MANAGER! NO MORE FREAKING LOST PASSWORDS, YO!
I recommend using KeePass if you are using Windows and KeepassXC under Linux.
Anyway, back to the main topic: how the hell can one open a Sage 50 database when you lost the password?
There are some shady Windows executables you can buy online that claims they will recover your lost Sage 50 passwords, but do you really trust all your financial data to some random programmer on the web asking 60$? Best case scenario you lost 60$ and you get your password back. Worst case, some Russian hacker now has your employee's private informations.
So yeah, please don't download these things and read on.
Prerequisites
It turns out Sage 50 uses MySQL as a backend to save its data. To gain back access to that file we'll thus extract the database used by your file and replace the sysadmin password in it by a known one.
For that, you will need:
- A Windows computer running Sage 50
- A Debian computer
- Some patience and a little familiarity with the command line
It is best if the computer running Debian Gnu/Linux is a "disposable" one, or at least a computer that is not running MySQL for anything important. We'll be playing with MySQL quite a little and my instructions will overwrite any configurations or databases MySQL uses.
Extracting the database
The file you are trying to open is likely an automatic backup copy Sage 50 saved somewhere. Those files are .CAB files, a type of lossless compression archive Windows uses.
Copy the .CAB file to your Debian computer. If the computer has a graphic
interface, the easiest way to extract the archive is to open it with
file-roller. If it is not installed by default, you can install it by running:
$ sudo apt install file-roller
On a headless computer, you will need to use cabextract.
Once you extracted the files, you should end up with something like this:
├── file_name.SAI
└── file_name.SAJ
├── errorlog.txt
├── ExceptionError.log
├── ibdata1
├── ib_logfile0
├── ib_logfile1
├── mysql
│ ├── columns_priv.frm
│ ├── columns_priv.MYD
│ ├── columns_priv.MYI
│ ├── db.frm
│ ├── db.MYD
│ ├── db.MYI
│ ├── func.frm
│ ├── func.MYD
│ ├── func.MYI
│ ├── help_category.frm
│ ├── help_category.MYD
│ ├── help_category.MYI
│ ├── help_keyword.frm
│ ├── help_keyword.MYD
│ ├── help_keyword.MYI
│ ├── help_relation.frm
│ ├── help_relation.MYD
│ ├── help_relation.MYI
│ ├── help_topic.frm
│ ├── help_topic.MYD
│ ├── help_topic.MYI
│ ├── host.frm
│ ├── host.MYD
│ ├── host.MYI
│ ├── proc.frm
│ ├── proc.MYD
│ ├── proc.MYI
│ ├── procs_priv.frm
│ ├── procs_priv.MYD
│ ├── procs_priv.MYI
│ ├── tables_priv.frm
│ ├── tables_priv.MYD
│ ├── tables_priv.MYI
│ ├── time_zone.frm
│ ├── time_zone_leap_second.frm
│ ├── time_zone_leap_second.MYD
│ ├── time_zone_leap_second.MYI
│ ├── time_zone.MYD
│ ├── time_zone.MYI
│ ├── time_zone_name.frm
│ ├── time_zone_name.MYD
│ ├── time_zone_name.MYI
│ ├── time_zone_transition.frm
│ ├── time_zone_transition.MYD
│ ├── time_zone_transition.MYI
│ ├── time_zone_transition_type.frm
│ ├── time_zone_transition_type.MYD
│ ├── time_zone_transition_type.MYI
│ ├── user.frm
│ ├── user_info.frm
│ ├── user_info.MYD
│ ├── user_info.MYI
│ ├── user.MYD
│ └── user.MYI
├── simply
│ ├── db.opt
│ ├── palrtres.frm
│ ├── pqueue.frm
│ ├── psptrdt.frm
│ ├── psptrdtp.frm
│ ├── psptrdtt.frm
│ ├── psptr.frm
│ ├── psptrtax.frm
│ ├── pstatus.frm
│ ├── pstrdtts.frm
│ ├── ptrans.frm
│ ├── ptrsnum.frm
│ ├── simplygroupmembership.frm
│ ├── simplygrouppriv.frm
│ ├── simplyusergroup.frm
│ ├── taccount.frm
│ ├── .
│ ├── .
│ ├── .
│ ├── tuser.frm
│ ├── tuserfrm.frm
│ ├── tuserinv.frm
│ ├── tuserlog.frm
│ ├── tuserprf.frm
│ ├── tuserwnd.frm
│ ├── tusrmsg.frm
│ ├── tusrpane.frm
│ ├── tusrpopt.frm
│ ├── tusrprf2.frm
│ ├── tusrrmsg.frm
│ ├── tusrrole.frm
│ ├── tusrrpt.frm
│ ├── tusrrrpt.frm
│ ├── tvendor.frm
│ ├── tvendp.frm
│ ├── tvendplg.frm
│ ├── tveninv.frm
│ ├── tvenitem.frm
│ ├── tvenmemo.frm
│ ├── tventrdt.frm
│ ├── tventr.frm
│ ├── tventxdt.frm
│ └── tvenudf.frm
└── SimplyError.log
So yeah, you end up with a very small file (the .SAI file) and one larger
directory (the .SAJ directory).
The .SAI is basically a unicode string describing the database to Sage 50.
That's the file you click on in Windows to access the database. Sage 50 will
read whatever .SAJ directory that has the same name the .SAI file has, so be
sure to keep the two named the same way.
The real fun resides in the .SAJ directory. If you are a little familiar with
MySQL you can now see that Sage 50 is using MySQL with InnoDB, which is great
news for us. I guess if you had time to spare, you could extract all the data
you need from there.
We are not going to do that though: I'm not familiar with accounting nor Sage 50, and chances are neither are you.
Changing the sysadmin password with the help of MySQL
This part is greatly inspired by this article. Shout out to Santosh
Yadav! If you are not yet logged in as root on the Debian computer, please do
so. All the commands that follow need to run as superuser.
If you have not yet installed MySQL on your Debian computer, it's now time to do it:
$ apt install mysql-server
Note that we are not using MariaDB even though it's a drop-in replacement for Oracle's MySQL because:
- I did not test this with MariaDB
- Sage 50 is likely using Oracle's MySQL and InnoDB can be a tricky thing
Maybe this procedure indeed works with MariaDB, who knows? If you can't run Oracle's MySQL for whatever reason but can run MariaDB, give it a try!
Anyway, what we will be doing is opening the tuser table and replacing the
sysadmin password by a new one. We can't just read the password from that table
since it is hashed with some proprietary algorithm. Since we don't know the
algorithm used, we cannot brute force the hash either.
I thus created a new Sage 50 database using patate55 as the sysadmin password
and got this hash:
jt8UzeaVRSk/lzb1UCaBXw==
That's the hash we will use to replace the old password. After that, you will be
able to open your file using sysadmin as username and patate55 as password.
The first step is to copy the files in the .SAJ directory to /var/lib/mysql:
$ service mysql stop $ cp -R /path/to/the/SAJ/directory/* /var/lib/mysql $ chown -R mysql:mysql /var/lib/mysql
You also need to modify the /etc/mysql/my.cnf to specify the size of the
ib_logfile0 in the .SAJ directory:
$ echo 'innodb_log_file_size=2M' >> /etc/mysql/my.cnf
Once that is done, you can start MySQL again. Since we have overwritten some of
the previous data in the /var/lib/mysql directory, your MySQL root password
won't work anymore. You can fix that by restarting it this way:
$ mysqld_safe --skip-grant-tables &
You can now log in into MySQL:
$ mysql -u root
You should now see the mysql > prompt. Run these commands to display the
tuser table, the table that contains the user login infos:
> use simply; > select * from tuser;
You should see something resembling this:
| lId | sName | nGLAccess | nPAAccess | nREAccess | nPRAccess | nINAccess | nPJAccess | n3rdParty | bAdmin | nTBAccess | nGJAccess | nPUJAccess | nPYJAccess | nSJAccess | nRJAccess | nPRJAccess | nIJAccess | bFullAcc | nLangPref | nUserType | sPassword | bExpired | lRole | nBRAccess | nDSAccess | sEmail | +-----+-------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+--------+-----------+-----------+------------+------------+-----------+-----------+------------+-----------+----------+-----------+-----------+--------------------------+----------+-------+-----------+-----------+---------------------------+ | 1 | sysadmin | 7 | 7 | 7 | 7 | 7 | 7 | 2 | 1 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 6 | 1 | 2 | 0 | fd2DacqSFCk/lzb7UCfBXw== | 0 | 1 | 7 | 7 | sysadmin@foobar.com | | 2 | User2 | 7 | 5 | 7 | 0 | 0 | 0 | 2 | 0 | 0 | 7 | 7 | 0 | 7 | 7 | 0 | 0 | 0 | 2 | 0 | pACbfSY1jthQj5c3CiHk2g== | 0 | 0 | 0 | 0 | user2@foobar.com | +-----+-------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+--------+-----------+-----------+------------+------------+-----------+-----------+------------+-----------+----------+-----------+-----------+--------------------------+----------+-------+-----------+-----------+---------------------------+
Now comes the moment when we replace the old password hash with the new one. For
me the old sysadmin password hash is fd2DacqSFCk/lzb7UCfBXw==. Please find
that hash in the data the last command displayed on your screen and replace
OLD_HASH in the next command by it:
> update simply.tuser set sPassword = replace(sPassword,'OLD_HASH','jt8UzeaVRSk/lzb1UCaBXw==');
If everything went well, you should have replaced the old sysadmin password you
lost by patate55. You can verify this by running:
> select * from tuser;
You should now see a line like this:
| 1 | sysadmin | 7 | 7 | 7 | 7 | 7 | 7 | 2 | 1 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 6 | 1 | 2 | 0 | jt8UzeaVRSk/lzb1UCaBXw== | 0 | 1 | 7 | 7 | sysadmin@foobar.com |
You can now quit the MySQL prompt:
> exit
Packing things back up
The only thing left to do is to pack things back up and to move them back on the Windows computer. First copy back the files from the MySQL directory to your home directory:
$ mkdir ~/patate55.SAJ $ cp -R /var/lib/mysql/* ~/patate55.SAJ/
You'll also need a .SAI file for Sage 50 to work properly:
$ cp /path/to/the/SAI/file ~/patate55.SAI
You can now copy the patate55.SAJ directory and the patate55.SAI file back
to your Windows computer and open it using sysamin and patate55 during the
authentication prompt.
Chances are you saved a few days of work to someone. Entering a whole year (or more!) of financial transactions by hand is no fun and it's even worse if you have to do it a second time. Grab a beer or something, you deserved it.