Exploring the New File Level Encryption Feature in FileMaker Pro 13

I started out exploring FileMaker Pro 13’s new encryption feature because I wondered about securing a single FileMaker database on my system. We like to think of a FileMaker file as a locked box, but I was never quite sure about how secure a FileMaker database is, because in the old days, it wasn’t very. Fortunately, the new version, FileMaker Pro 13, offers the ability to strongly encrypt files.

This quote from a FileMaker white paper on security from FileMaker 7 era explains what is going on:

File Security
The database file is not encrypted, but the data is obscured via a proprietary Unicode compression algorithm. This will help prevent casual attackers from extracting data from copies of the application files using a text-editor. When one FileMaker product is accessing peer-to-peer or client-server data, the local cache file no longer contains the list of all passwords, because authentication is performed remotely. The compressed data in the cache file is further obscured to protect the data and metadata with a fast proprietary encryption algorithm. Customers who require better file-level security can use file and folder encryption features built into the operating system (e.g.,WinNT and WinXP Pro) or with third party tools. They can also use the file and folder access control mechanisms of the underlying OS to restrict access from local users. High security applications can use the FileMaker Server product running on a computer that is protected from ordinary users.

Wise old FileMaker developers always recommended that you physically secure your database on a server and not let anyone near it. In these days of Heartbleed hacker attacks and NSA snooping, no server actually seems very secure, so moving to a new level of file encryption appears to be an important step. That way, if the wrong people get access to your data file, they won’t be able to read the data without significant effort.

For an individual, hoping to increase the security of an unshared database running on FileMaker Pro, the encrypt/decrypt ability does not seem to add much over what can already be done with external tools, such as an encrypted disk image on OS X. or an encrypted .zip file. The encryption password is not tied into FileMaker Pro’s Security setup. Essentially, it just adds another level of password to the file. If a database administrator or developer wanted to distribute a stand alone database file, all recipients would have to be given the decryption password, in addition to their regular FileMaker Pro password.

One significant improvement over using an OS X encrypted disk image, is that if an intruder gets access to the host system when the disk image is decrypted, they can access any files stored on that disk, including a FileMaker database file. In the FileMaker Pro 13 Encrypted scenario, even if the file is taken, it is still locked down with 256-bit encryption. Apparently this is known as ‘EAR’, or Encryption At Rest. Stephen Blackwell, a well-known FileMaker security expert has written a good article on FileMaker Pro 13’s new Encryption at Rest feature that is worth reading.

Turning on FileMaker Pro 13’s new database encryption feature requires a copy of FileMaker Pro Advanced, it cannot be done with FileMaker Pro. FileMaker Pro can read and access encrypted files, but it can’t encrypt them.

With the database you wish to encrypt closed, in FileMaker Pro Advanced 13, select Developer Utilities… from the Tools menu.

Four Steps to Configure
A) Select the database you want to encrypt
B) Specify the Project output Folder
C) Specify the Solution Options — in this case it will be “Enable Database Encryption”
D) Click ‘Create’

A) Select the database you want to encrypt
Similarly select a Folder location to write the Encrypted database to.

B) Select the database you want to encrypt

1) Click Enable Database Encryption option
2) Enter a Full Access FileMaker Pro account for the database solution
3) Enter an Encryption Password.
4) Keep Open Storage is an option to keep container fields unencrypted.
5) Click OK

C) Step 3: Enter an Encryption Password for the Database
Enter a [Full Access] FileMaker Pro Password for the Database and then setup the Encryption Password.

Enter a Password hint if you are so inclined. Then click ‘OK’.

D) Click Create
Once the parameters for the encryption have been set, click the ‘Create’ button to generate an encrypted version of the file.

Encrypted Database in a Folder
If everything has gone well, you now have a new version of your database that has encryption enabled.

This routine of encrypting your database has left the original file intact and created a new copy of the file in an encrypted format. The encryption on the new database is very strong, so take care to store the encryption password for future reference. Keeping the original unencrypted database as a fall back may be worthwhile, but remember everything entered into the new encrypted database is locked inside a very secure container.

It is also possible to reverse this process and get rid of encryption on a database using the Developer Utilities in FileMaker Pro Advanced.

There is not much to the log file, just that the encryption was successful and the time and date when it occurred.

Accessing Database with Encryption and FileMaker Pro Passwords
Now logging into a standalone copy of an encrypted database, requires two passwords to be entered,
1) one for the encryption and the
2) second for FileMaker user level access.

These dialogs are shown in sequence, I have combined them for the purposes of illustration.

3) Non-networked copies of the database running on FileMaker Go, will also see the File Encrypted dialog box which requires the encryption password to get to the next level, a FileMaker Account Name and Password.

Encrypted or Unencrypted — Same File Size
Interestingly, encryption does not seem to effect the file size as seen in the screenshot above

The real benefit of FileMaker Pro 13’s new file encryption comes when hosting a shared FileMaker database. The administrator has the decryption password, decrypts the file when opening it on FileMaker Server, or sharing it using FileMaker Pro’s built-in sharing ability (up to 5 clients). The clients logging in do not need the encryption password, just their regular FileMaker or External Authentication password.

FileMaker Pro has had the ability to encrypt the transmission of data between FileMaker Server and FileMaker clients since FileMaker 9, using SSL, the standard for Internet security. When hosting a database file using FileMaker Pro (not FileMaker Server), users will not get the benefit of SSL encryption when passing data back and forth. With FileMaker Server however, there is an option in the FileMaker Server Admin Console called “Secure Connections” to turn on SSL. This feature is also available in previous versions of FileMaker Server versions going back to FileMaker Server 9.

From a FileMaker Pro 13 client, under the File Menu select Sharing > Upload to FileMaker Server…

Networked Databases – Select The Server
In this test case, I am using a local FileMaker Server hosted on my development machine. (Did you know that you could get a low cost version of FileMaker Server for testing purposes?)

Networked Databases – Note the Caution Icon in the Upload to FileMaker Server Dialog
Before uploading administrators will notice the yellow flag warning: “File is Encrypted: a server administrator must open the file after it is uploaded”

Networked Databases – Activity Tab: Databases Tab: Select Uploaded Database
1) Select the Encrypted Database
2) Click the small Database Actions menu icon

Networked Databases – Select ‘Open’ from the Database Actions Menu
Click on the small Database Actions icon to Open the encrypted database.

Networked Databases – Enter the Encryption Password
Note the option to Save Password. Presumably the encryption password is well protected in FileMaker Server. In a very secure environment, this checkbox would not be checked, requiring an administrator to enter the password each time the server is restarted or the database reopened. Saving/not saving the password will be a workflow decision as to how this is handled in your particular environment.

Networked Databases – Clear the Encryption Passwords
Another menu choice under the Database Actions icon, are some options to clear the Encryption Password for a single database, or Clear All the Encryption Passwords for all databases hosted on the Server.

Networked Databases – Encryption State Indicator
When logging into an encrypted database on FileMaker Server, in either FileMaker Pro or FileMaker Go, the system displays the encryption state for files hosted with FileMaker Server. A lock icon appears to indicate when connections to the server are encrypted and turns green if the connection is using a trusted security certificate.

Get(EncryptionState) Function
One more tool that may be useful in programming is the new Get function Get(EncryptionState), which returns a value representing the file’s current encryption state. This function works in FileMaker Pro and FileMaker Go.

FileMaker Pro 3 and 5 File (.fp3, .fp5) in a Text Editor
Back in the bad old days, you could read data right out of a FileMaker database with a text editor. Seen here is a FileMaker Pro 6 database open in TextMate. Not much improvement in FileMaker Pro 3 or 5 file format, the text in fields can be copied out in plain text. I have a few files on my drive labeled .fp6 and they open as well. According to FileMaker, FM 6 shared the format with FM 5 and FM 5.5.

FileMaker Pro 7 File (.fp7) in a Text Editor
Opening a FileMaker Pro 7-13 file in a text editor, (TextMate) reveals some XML which seems to be describing the structure of the database. Most of the text is unreadable — it is encoded, but not encrypted. Apparently, FileMaker Inc., experimented with encryption for fp7 but backed off because of speed issues. The text is not human readable in a text editor, but there is a technique to decode it so it readable, as seen in the screenshot below.

Unencoded and Unencrypted FileMaker File 7-13
Above is a Hex Editor displaying a FileMaker Pro 12 format file, showing field content from an unencrypted file in plain text.

According to a very smart FileMaker developer and friend of mine, the text in a FileMaker 7-12 format file can be restored by reversing the “proprietary Unicode compression algorithm” mentioned above in the FileMaker 7 White Paper. The screenshot above is a demonstration of this capability.

There are also programs like Passware that are able to go through FMP12, FP7, FP6, FP5, FP4, FP3 formatted files and overwrite a password, replacing the existing password with a new one that allows users to open the file. I have used Passware in the past to recover a lost password for a customer — the company’s tech support person had left town, and there was nothing written down for management to refer to.

FileMaker Pro 13 File (.fmp12 Encrypted) in a Text Editor
It appears that FileMaker Pro 13’s AES 256-bit database encryption would be challenging unless you have some help from the NSA.

1) Unencrypted version of the file
2) Encrypted version of the same file open in a Text Editor
3) Encrypted version of the file, open in FileMaker Pro 13 simultaneously opened in a text editor.

I experimented with opening the Encrypted version of the database when it was open in FileMaker Pro, I then opened the same database file in my text editor and it still looks encrypted, so it seems that FileMaker Pro 13 is decrypting on the fly, rather than fully decrypting the file when it opens.

Conclusions

Security is never done, because cyber crime is a moving target. The move towards file encryption in FileMaker Pro 13 is definitely a good thing, but FileMaker developers should not rest easy as new attack vectors are always emerging. Sophisticated hackers have access to advanced techniques like scraping the RAM of a server to look at data in an unencrypted state. We don’t know how FileMaker Server would fare under such scrutiny.

As developers, we have benefited by the scale of our projects — most of us are not running Target customer databases that contain millions of credit cards. We benefit by security through obscurity. FileMaker databases have not been as much of a target as larger scale SQL databases.

It may be time for an assessment of the data you are collecting. Database developers think nothing of adding fields of detailed data on customers, on the theory that more is always being better. Perhaps it is time to think about ways to reduce the amount and quality of data you store on your customers.

Credit Cards

A few years back it might have been acceptable to collect credit card numbers. Now it is much better to set up your system with just a reference number to a third-party credit card processor. For things like monthly billings, instead of sending the credit card information, a reference code is stored and sent to incur a new charge to the credit card. No data is stored in FileMaker except that reference code.

Addresses

Do you really need a customers full mailing address? When was the last time you mailed anything? Have all your customer communications switched to email? How about asking for cross streets or a postal code for customer mapping purposes? Obviously if you are delivering them packages, you need this, but if you are a retail store and just want a general idea of where your customers are coming from, perhaps cross streets are sufficient.

Phone Numbers

“At first we were worried that our customers would be apprehensive to give us their phone numbers. It turns out that very few people had issues with giving out their cell number because the value of doing so outweighs the negatives. We also delete the phone numbers at the end of the night and tell our customers that we do so.” –Designing FileMaker

Birthdates

Are actual birthdates crucial? If you need to know they are over a certain age, for an activity, just record an approximate date, say the first day of the month their birthday occurs in. Round the data where possible.

In the end, perhaps the less data you can store on your customers, the better. Just store what you really need to serve them effectively.