SOS Database Encryption

Overview

By default, the SOS database is not encrypted, though the physical layout of the database files is such that assembling a coherent record of data would be difficult. Nonetheless, inspection of the database files could reveal identifying information, so some level of encryption is recommended in most all situations, and always if the database is transported from place to place on a portable computer or on removable media (such as removable disk, CD, DVD, USB memory key, or tape) or if the database is transmitted from one location to another electronically.

You may choose to protect the data in the database by selecting either “simple encryption” or “strong encryption” (AES, Advanced Encryption Standard).

Simple Database Encryption

Simple encryption is equivalent to obfuscation and prevents someone using a disk utility, text editor, or even a word processor from being able to read patient names or other information stored in the database files. Simple encryption uses a built-in key and therefore does not require the user to provide a key to encrypt the database. Once encrypted, the information can still be accessed using the SOS and Sybase programs exactly as with an unencrypted database. In addition, utilities such as DBTRAN (which translates the transaction log into readable text) can still be used by anyone who knows how, without the need to provide a password or encryption key. It is therefore essential to secure the computer and drive on which the transaction log (SOSDATA.LOG) is stored, even if using simple encryption. The folder(s) in which the database and log files are stored should NEVER be shared across a network for this reason. Windows shares are entirely unnecessary for use of the SOS system, and for the reasons just given, undercut efforts to keep your data secure.

Strong Database Encryption

Implementing strong encryption is a more complex procedure but renders the database completely inaccessible without an encryption key. There is no back door or recovery possible if the encryption key value is lost or forgotten, so if no reliable key management system is possible in your organization, you should think twice about it.

Changing the Encryption Level of an Existing Database

In order to change the encryption level of your database, you must rebuild it. Starting with Release 2007.02 (December, 2007 build), the SOS Database Rebuild Utility may be used to encrypt your database with Simple Encryption. There is no automated option for applying strong encryption, though SOS tech support can provide you with hands-on assistance to go through the steps, specifically:

1. Backup your current database.
2. Unload your database to a set of ASCII tables.
3. Initialize a new encrypted database.

Reload the new database with the previously unloaded data.

 

When selecting the Strong Encryption option, you must provide an encryption key, which is a string of characters that will be used to encrypt and decrypt the contents of your database and transaction log. The normal rules for password generation apply:

• Use no fewer than 8 characters. SOS’s SQL Anywhere database supports keys of up to 60 characters. A length of 10 to 30 is recommended. Longer keys are more difficult to crack, but theoretically could result in slower performance. The better your hardware, the less likely you would be able to detect a difference.
• Include upper and lower case letters, plus numbers and special characters in your encryption key. The key may contain any characters on your keyboard except <space>, semi-colon, apostrophe, and quotation mark characters.
• Strong keys are really random – no pet names or birth dates! Many free and low cost password generators are available to assist in generating random sequences of characters for use as passwords and encryption keys. Popular password managers such as LastPass and Roboform also include password generation tools.

Whenever you select or change your SOS database encryption password, we STRONGLY recommend that you use a key management system of some sort. There is  no way to start or recover your database without it, so having a backup copy of your key in a secure location, available to all staff who might need it is mandatory to assure that you will not lose your data because of a forgotten key. Consider that you might not always be around to provide the key if it is ever needed, so appropriate recovery safeguards must be in place that do not depend on one person.

Performance Considerations Related to Database Encryption

There is a performance penalty for the use of encryption. The penalty is insignificant for the simple encryption method, but may be detectable when using strong encryption. If implementing strong encryption, the customer should pay even more attention to the selection of server hardware, choosing faster processors and disk systems, and, especially, more RAM than might otherwise be necessary. If the amount of RAM is sufficient to permit the entire database to be cached, the performance penalty should diminish and become relatively small after a period of active use. Note also that the length of the encryption key is related to performance, so if you are using a very long key and have performance issues, you can try rebuilding the database with a shorter key.

Recommendations

Given that the Simple Encryption option has minimal performance impact and provides some protection from unauthorized access, it is the minimum SOS recommendation. If your database is carried out of the office on a laptop or in unencrypted backups (such as a straight copy of the DATA folder onto an unencrypted flash drive or DVD), then serious consideration should be given to using Strong Encryption. In that case, it is essential that you carefully think through the entire process. Precautions must be taken to assure that only the most trusted employees are able to log into the server to encrypt the database. Again, however, keep in mind that losing the only employee who knows the encryption key could be disastrous and your plans must include assuring that you will not be left without a way to start and access your database.

How To Implement Simple Database Encryption

The easiest, and SOS recommended, way to change from no encryption to simple encryption is simply to use the Database Rebuild Utility, which will automatically apply simple encryption. It can be run from the command prompt, or using a menu item in the Administration module. This utility is named DBRBLD.EXE and is located in the SOS folder of the database server, or standalone computer if not running on a network. Within the Administration Module, navigate to “Database Tools” then “Database Rebuild Utility” as described below.

1. Have all users exit all SOS programs.
2. Backup your database as you normally do.
3. On the computer where the database is located start SOSLogin.
4. Enter an account ID and password of a user with SOS security administrator privileges.
5. Click the “Admin” icon (the keys).
6. Select Database Tools.
7. On the list of utilities, select and run “Database Rebuild Utility”

How To Implement Strong Database Encryption

As mentioned previously, applying strong encryption is a manual process that involves several steps and some guidance from SOS tech support. The procedure includes the following steps:

 

1. Backup your current database.
2. Unload your database to a set of ASCII tables.
3. Initialize a new encrypted database.
4. Reload the new database with the previously unloaded data.
5. Create and securely locate an option2.prm file containing an obfuscated version of your encryption key.
6. Reconfigure the command line needed to start the encrypted database (see next section).

Before walking you through the procedure, SOS’s support tech will review with you the fact that loss of your encryption key will mean permanent loss of your data. The support tech also will ask you about your strategy to prevent loss of your database encryption key.

 

Starting a Strongly Encrypted Database

Once the database has been encrypted, whenever the database is started, the encryption key must be provided on the command line when the database is started, or the database engine must be instructed to prompt for the encryption key as it starts the database.

By default, SOS includes database engine startup parameters in a file in the SOS folder called SERVER.PRM. This file contains options for the database engine, which could, potentially run more than one database at a time. Options for each database run by the engine cannot be added to the engine options file. They must be placed after each database file on the startup command line. The following examples assume the database is running in a 64 bit Windows environment. If you are running 32 bit Windows, then replace “bin64” with “bin32” in the command line. All the following examples would be on a single line:

Default command line (simple or no encryption options):

c:\sos\sa\bin64\dbsrv11.exe @c:\sos\server.prm c:\sos\data\sosdata.db

Command line to prompt the user for the encryption key (-ep) every time the database is started:

c:\sos\sa\bin64\dbsrv11.exe @c:\sos\server.prm c:\sos\data\sosdata.db -ep

Command line with encryption key specified explicity:

c:\sos\sa\bin64\dbsrv11.exe @c:\sos\server.prm c:\sos\data\sosdata.db -ek secretkey

The last option is obviously less than ideal because the encryption key appears in plain text in the startup line invoked by your shortcut, batch file, etc. There is, however, an alternative. That part of the command line can be hidden using a provided utility that will hide the text from all but expert snoopers. When you select the Strong Encryption option during the rebuild, your encryption key will automatically be saved in an unreadable file called OPTION2.PRM, which you will find in the SOS folder as well as the DATA folder. You can therefore use this variation of the command above (entire command is typed on a single line):

c:\sos\sa\bin64\dbsrv11.exe @c:\sos\server.prm c:\sos\data\sosdata.db @c:\sos\option2.prm

If you wanted to create the unreadable option2.prm file manually, here is how it would be done:

1.   Create a plain text file using Notepad or another text editor.
2.   Enter -ek and the encryption key with which your database was encrypted.
3.   Save the file as TEMP.TMP in your SOS folder.
4.   Now apply simple encryption to the file, saving the encrypted copy as OPTION2.PRM with the command:

c:\sos\sa\bin64\dbfhide temp.tmp option2.prm

5.   You will now have the original TEMP.TMP and an encrypted version called OPTION2.PRM in your SOS folder. Remember, if you lose your encryption key, there is no way to decrypt or run your database! Once you are sure that the encryption key value is safely recorded in case you should ever need it, delete the TEMP.TMP file. Hold down the shift key while deleting to be sure the file is deleted instead of just being moved to the recycle folder. If you have a utility for secure deletion of files, that would be even better. Now you can start the database using the following command, which does not expose the key:

c:\sos\sa\bin64\dbsrv11.exe @c:\sos\server.prm c:\sos\data\sosdata.db @option2.prm

A strongly encrypted database cannot be opened without its matching encryption key. For that reason it is essential that you either backup the option2.prm file containing your key, or securely record the key in a secure log, or formal key management system, that will document changes in the key over time. If you ever have to restore your data from an old backup, you will need the key that was in use when that database was backed up.

Including Your Encryption Key in the ODBC Configuration

In standalone installations, the database is rarely, if ever, started directly. Instead, it is automatically started whenever needed using a start command specified in the ODBC configuration named SOSDATA (located by default on the System DSN tab). As mentioned above, you should not simply type the encryption key in the indicated field on the Database tab because it will appear in clear text in the Windows Registry. Instead, follow the instructions in the section above to create an OPTION2.PRM file that contains an encrypted version of the database encryption option and your key. (It does no harm to make the same modifications on a network database server, but in almost all cases the database server will be started from a shortcut, batch file, scheduled task command, or as a service. The only time the ODBC configuration would be used is if you were to use the SOS Login shortcut when the database was not already running.)

You must then modify the ODBC configuration, specifically the Database tab entries as follows, assuming default SOS folders and a standalone installation:

Running an Encrypted Database as a Windows Service

If prompting for a password on startup (the -ep parameter), be sure to use the -i parameter when creating your service so the service can interact with the console. As the service starts, a window will appear in which you must type the encryption key.

The following example (typed all on one line) creates a service that prompts for the encryption key whenever you start the database:

c:\sos\sa\bin64\dbsvc -as -t network -s Automatic -i -w mysos c:\sos\sa\bin64\dbsrv11.exe @c:\sos\server.prm c:\sos\data\sosdata.db -ep

You must include the -i (allow service to interact with the desktop) option when you use the -ep (prompt for encryption key) option. When configured in this fashion, someone must be present at the server console to type the encryption key when the service starts.

Although this option would be the most secure, it may well not be practical. The alternative would be to pass the encryption key value on the service startup line automatically. The problem is that if you type the encryption key in your startup command, it is not secure. To avoid having this value exposed, the -ek parameter, along with the encryption key, may itself be encrypted in a form that the database engine will be able to decrypt. When you rebuild the database the utility will create the appropriate, ready-to-use, encrypted key parameter in a file called option2.prm.

You can also create that file manually as follows:

1. Create a plain text file using Notepad or another text editor.
2. Enter -ek and the encryption key with which your database was encrypted.
3. Save the file as TEMP.TMP in your SOS folder.
4. Now encrypt the file, saving the encrypted copy as OPTION2.PRM with the command:
   c:\sos\asa\win32\dbfhide temp.tmp option2.prm
5. You will now have the original TEMP.TMP and the encrypted OPTION2.PRM in your SOS folder. Once you are sure that the encryption key value is safely recorded in case you should ever need it (SOS recommends that you call SOS and have the value stored in your account records at SOS), delete the TEMP.TMP file. Hold down the shift key while deleting to be sure the file is deleted instead of simply moved to the recycle folder. If you have a utility for secure deletion of files, that would be even better.
6. Now create your service using the following format (typed all on one line, with the appropriate changes if your installation is not in C:\SOS):

c:\sos\sa\bin64\dbsvc -as -t network -s Automatic -i -w mysos c:\sos\sa\bin64\dbsrv11.exe @c:\sos\server.prm c:\sos\data\sosdata.db @c:\sos\option2.prm

Note: For additional information and description of all available command line options for service creation, see: Running the SOS SQL Anywhere 11 Database as a Windows Service

Transport Layer Encryption

In addition to encryption of the database itself, a customer might be concerned about protecting the data while it is “in motion” between the server and the client workstations. Various network protection approaches are available, including VPN, wireless encryption, and use of Windows Terminal Services or Citrix.

As with database file encryption discussed previously, there are simple and strong encryption options available for transport-layer security. An easy way to avoid sending easily read data is to add…

-ec simple

…to your server startup options in the SERVER.PRM file in your SOS folder. Doing so forces all connected clients to use simple encryption on database network communications. Doing so provides the same kind of obfuscation for your database communication packets that simple encryption does for your database files. It will not secure the data from an encryption expert or “hacker” who is determined to eavesdrop on your communications, but it will prevent less sophisticated individuals from reading the contents of your transmission packets by simply hooking up a packet sniffer tool. This sort of simple encryption has a minimal impact on performance, and could be used as an extra layer of protection when using other less-than-ideal encryption, such as standard wifi WPA encryption.

Sophisticated, strong, transport-layer encryption options for your database communications are also available, using RSA or ECC certificates. As stated previously, if you want that level of protection, you should probably investigate strategies that will protect all your network communications, not just database packets. In addition, this kind of database-specific encryption requires purchase of an additional component, which is priced by number of user connections.