QuickBooks Desktop: Passwords, Encryption, Decryption... Part 1

by

How much do you think you really know about what is going on beneath the QuickBooks User Interface?

Contrary to many popular software programs like Excel, Word or even Access the internal workings, operations and procedures of QuickBooks are closely guarded secrets completely unknown to all but a few present and former Intuit technicians.  In fact, I would venture to guess that less than 10% of the total number of Intuit personnel who actually support QuickBooks have a fundamental knowledge of what goes on deep within QuickBooks.

If you have been to any of my ‘Super Geek’ classes at Scaling New Heights, or if you remember way back when I first started writing for Insightful Accountant, you have undoubtedly seen the illustration below showing the relationship between the 3 fundamental components of QuickBooks.

Mid-2000's versions of QuickBooks, went through migration from the C-index database to the Sybase SQL Anywhere database. The QuickBooks application is a ‘graphical user interface’ made from a rag-tag of programming language codes (developed over the years) sitting on top of one of two Sybase database server engines. While this article discusses aspects of all three of these components, it really focuses on internal structures, hidden deep from public view, within your QuickBooks Company file.

But before we get down to the nitty-gritty, we have to discuss some fundamental aspects of the QuickBooks Company file.

Each QuickBooks file is a ‘clustered table’ design, all the various data is stored in tables and all of those tables are clustered together within a single file.  In some database designs each table is associated with a unique file, but that’s not how QuickBooks does it.

One reason for a clustered approach is ‘security’.  All the data that makes up your ‘financial information’ is securely encompassed into one file that can be restricted in terms of accessibility. 

Those restrictions take many forms including the set-up of user accounts, passwords, and other restrictions, likes rolls and permissions.  Another form of protection is the encryption of both the file as a whole and sensitive data within the file.

When we open a QuickBooks Company file, it is full of our information. From a database context, the data is stored in a variety of tables, stored in a ‘clustered table’ design. Some tables store your list data, like the Customers and Vendors. Other tables store your transactions, like the Check header table, and the check detail-account table. Still other tables store data objects that work with the QuickBooks application to make it run more smoothly and provide faster access to data. And yes, some tables are responsible for many of the security measures (the hoops we must jump through) like passwords, to get to our data.

While many of you maybe familiar with ODBC, QODBC and the custom reporting feature’s that provide access to QuickBooks information, the tables accessed by these methods are ‘limited’ reproductions of table data within the QuickBooks company file. None of these applications, or methodologies, either access the tables directly or touch the underlying ‘real data’ supported by the QuickBooks User Interface.

Essentially, without breaking a QuickBooks Company file apart after decrypting it, there are no tools on the market that typical users ‘can afford’ that will give them direct access to the actual table structure. 

Third party products that allow you to read, write and otherwise modify data within a QuickBooks file are actually communicating with one of the two Sybase database engines simply passing commands in the exact same manner as the actual QuickBooks User interface itself.  Only the Database Server provides query, read, write and append instructions impacting the actual data.

One reason for the clustered approach QuickBooks takes with your data is ‘security’, all the data that makes up your ‘financial information’ is securely encompassed into one file that can be restricted in terms of accessibility.  As a whole the file is restricted by encryption that prevents unauthorized penetration into the inner components of the file.  But that is not where this ‘encryption form of security’ stops within QuickBooks, as we will see.

Over the years, I have been openly critical of many of the security weaknesses within QuickBooks. Last year I was one of only a few people who was opening supportive of Intuit's efforts to reform and enhance security by increasing password requirements.

Public outcries from users who seemingly don't care about the security of sensitive data, blasting Intuit for requiring passwords, and more specifically frequently changed complex passwords, gave rise to the recently revised implementation of measures that seemingly get around last year's password enhancement.  I still concern myself with the flaws that permit the potential loss of data, but that is not what this article is all about.

Rather, this article is about QuickBooks number one mechanism for file security, which is based upon the Admin user’s password and the related encryption ‘master keys’ associated with that password. 

Until last year’s security updates, you didn’t need to ever use passwords, even if you had sensitive data like credit card numbers stored in your file, unless you were using Intuit Merchant Services.  You were encouraged to turn on credit-card protections, but nothing made you do so. Without those protections you didn’t even need a Admin user password, although there was already an Admin account set-up for your file.

But before we get much further along, we need to discuss the fundamental principles of encryption and decryption.  Encryption is the process of coding information in such a way that only authorized parties can read it. Encryption does not prevent access to, or review of, any information, it simply denies the information’s content to unauthorized persons.

If I gave you a coded message, you could probably pass it around to everyone you know and I wouldn’t worry that anybody knew what I actually wrote. In order to code the message, I make use of an ‘encryption key’. To decode the message, someone must use the same key, or they likely will never figure it out.

What if my girl friend writes a message reading 092  712  152  205  272  512  21  - would you have any idea what that means? Well, that’s what encryption is all about.

If encryption is the coding of information, then decryption is the decoding of information.  As with the encryption process, the key thing is ‘the encryption key’. Without both sides having the encryption key, the message doesn’t accomplish it’s goal. 

I must have the key to encrypt the data, and whoever is going to read the data must have the key to unlock the meaning. Without the key, nobody knows that my girlfriend wrote, “I Love You.”

In order to understand how encryption and decryption are applied in a QuickBooks file, we must look at how user accounts and their passwords are created and stored.

Initially, when QuickBooks is opened and a new file is created, a single user account, permanently called ‘Admin’, is automatically populated into a table called “ISYSUSER”.

While you may choose to ‘rename’ the account to ‘John’ within the User Interface, underneath it is still ‘Admin’, because, there can’t be a QuickBooks file without an Admin User account, even if you don’t know that it is there.

Prior to last years changes in QuickBooks security associated with PCI compliance, there was no password requirement until the user selected the option of creating additional users and setting passwords.

But even before there is a single new user password, the ISYSUSER table is still encoded because each QB username becomes a Hex string. So Admin is actually encoded in a hex string as (0x41 0x64 0x6D 0x69 0x6E) when it is stored in the ISYSUSER table. In fact, every QuickBooks User name is stored as a hex string, a process know as ‘hashing’. User names are not the only security layer we create in QuickBooks, not if we have any ‘sensitive data’ in our files, we also enter passwords.But before we look at passwords let’s examine what we mean by ‘sensitive data’.

Sensitive data includes things such as customer credit card numbers, employee social security numbers, vendor tax id numbers, employer identification numbers, bank account numbers, and several others Intuit lists now as Personally Identifiable Information (PII) data:

  • Employee and Company Social Security Number
  • Company EIN
  • Company Bank Details (Routing Number, Account Number)
  • Company Credit Card Acct. No.
  • Other Assets Account No.
  • Other Current Assets Account No.
  • Loan/Other Current Liability Account No.
  • Long Term Liability Account No.
  • Vendor Tax ID
  • Vendor Account No.

You will still see ‘sensitive data’ used in places like the QBWin.log, and that will be the terminology I use within this article. This ‘sensitive data’, just like user names and passwords, is encrypted within QuickBooks to provide an extra layer of security.

We don’t just enter User Names in QuickBooks, we assign passwords, and the very first password we assign is to our User Admin Account. Within the “ISYSUSER” table, password values are stored in the "password" field.

All recent versions of QuickBooks have password verifications that are SHA-256 based (early QuickBooks versions use a proprietary hashing algorithm). SHA-256 is considered the industry-standard signature hash algorithm. SHA-256 provides stronger security and has replaced SHA-1 and SHA-2 as the recommended algorithm.

SHA-256 has become the standard for all SSL Certificates. You commonly see SSL certificates displayed when you connect QuickBooks desktop to a 3rd party-application via the Integrated Application permissions protocol.

I wrote earlier that this User Admin Password is ‘key’ to the encryption/decryption process QuickBooks uses. Because this information is so important, QuickBooks stores all of the data captured in the window shown above in a 2nd, even more important table called the ABMC_TSAFE table.

Unlike most QuickBooks tables, this table has only one row of data encompassing the ADMIN Name stored as a Hex string, the Admin user challenge question, and the ADMIN Key which is the encrypted Admin password and challenge answer.

Next time, in part 2 of this series, we will look at log-in control, the relationship of user roles and permissions, and issues of security compromise, the mechanisms for safeguarding sensitive data, and forms of corruption associated with these security features.