Purpose

The purpose of this page is to review the fundamentals of encryption and how to configure table encryption for a SQL Anywhere version 10 or later database.

Overview

Encryption is a security tool used to protect information from people who are not authorized to view it. SQL Anywhere introduced strong encryption in version 8.0.0. In version 10.0.0 or later, you can encrypt entire databases or only selected tables. This gives you added flexibility, and can boost performance for encryption users who only need to encrypt a portion of their databases.

Encryption is the process of altering information to make it incomprehensible to those who are not intended to view it. If someone has physical access to the database file, they cannot examine the file and see the data contained in the database.

There are two types of encryption: simple encryption and strong encryption. Simple encryption obfuscates data, but does not use an encryption key. It is a simple way of preventing unauthorized people from viewing the data using a disk utility. Strong encryption uses an algorithm to encrypt data, making it accessible only with a key.

A cipher is an algorithm used to encrypt and decrypt information. The cipher is used to convert legible plaintext into ciphertext, which is text that is unreadable by humans. A block cipher is a cluster of text that is a fixed number of bytes in size.

SQL Anywhere Encryption

SQL Anywhere implements the AES cipher for strong encryption. The algorithm is called Rijndael, chosen to be the Advanced Encryption Standard by the United States Government.

The AES-FIPS cipher is also available for certain platforms in SQL Anywhere. AES-FIPS is essentially the same as AES. The only difference is that AES-FIPS uses an implementation provided by Certicom. This implementation is approved by the United States Government according to the FIPS standard. FIPS stands for Federal Information Processing Standard. In SQL Anywhere, the blocks that are used are the same size as the page size of the database. The SQL Anywhere implementation of AES and AES-FIPS uses 128-bit blocks.

Selecting an Encryption Key

If you use strong encryption, you must specify an encryption key to use. It is essential when creating a key to use for strong encryption, that you you create one that cannot be easily guessed. The longer the key is, th emore difficult it is to guess. The encryption key can be formed using uppercase and/or lowercase characters, numbers and special characters. It is very important that you remember your encryption key. If the key is lost, the database is permanently inaccessible.

Changing Your Encryption Key

It is possible to change your encryption key by using the CREATE ENCRYPTED FILE statement in Interactive SQL. This SQL statement changes your encryption key by creating a new database with a new encryption key, and loading your old database into the new database.

The following SQL syntax creates a new database, newfile.db, from the old database, oldfile.db. The new encryption key is new_key and the old encryption key is old_key. The encryption algorithm is AES.

CREATE ENCRYPTED FILE 'c:\newfile.db'
FROM 'c:\oldfile.db'
KEY 'new_key'
OLD KEY 'old_key'
ALGORITHM 'AES';

You must also execute this statement for the transaction log file, as well as any dbspace or mirror files.

Enabling Encryption

To use encryption on tables in your database, you must enable encryption when you create the database. When enabling encryption, it is important to decide whether you want to use simple or strong encryption.

Enabling Simple Table Encryption

Creating a Database with Simple Table Encryption (Command Prompt)

Execute the following command using command prompt:
dbinit -e -et demo.db

This command creates a new database using the dbinit utility. It enables simple encryption (-e), table encryption (-et) and names the new database demo.db.

Creating a Database with Simple Table Encryption (SQL)

Open Interactive SQL.
Execute the following SQL statement:
CREATE DATABASE 'c:\demo.db'
ENCRYPTED TABLE ALGORITHM 'simple';

This CREATE DATABASE statement creates a new database with simple table encryption.

Enabling Strong Table Encryption

Creating a Database with Strong Table Encryption (Command Prompt)

Execute the following command using command prompt:
dbinit -ea AES -et -ek myencryptionkey c:\demo.db

This command creates a new database using the dbinit utility. It enables strong AES encryption (-ea AES), table encryption (-et), specifies the encryption key (-ek myencryptionkey), and names the new database demo.db.

Creating a Database with Strong Table Encryption (SQL)

Open Interactive SQL.
Execute the following SQL statement:
CREATE DATABASE 'c:\demo.db'
ENCRYPTED TABLE KEY 'myencryptionkey' ALGORITHM 'AES';

This CREATE DATABASE statement creates a new database that has table encryption enabled using strong AES encryption, and specifies the encryption key (myencryptionkey).

Enabling Table Encryption from Sybase Central

Creating a Encrypted Database from Sybase Central

In Sybase Central, from the Tools menu, choose Create Database. Follow the instructions in the wizard.
On the Encryption Settings page of the wizard, configure the database encyrption settings
1. To use simple table encryption, select Enable Encryption. Then select Simple Encryption. Select Encrypt only tables that are explicitly marked for encryption.
2. To use strong table encryption, select Enable Encryption. Then select Strong Encryption. Provide an encryption key, and select Encrypt only tables that are explicitly marked for encryption.

Encrypting a Table

You can encrypt a table when you create it.

Start Interactive SQL and connect to your database.
Execute the following command:
     CREATE TABLE Employees (
     UserName CHAR (50),
     UserID INTEGER )
     ENCRYPTED;

To encrypt a table from Sybase Central:

Start Sybase Central and connect to your database.
Select the Tables folder.
Choose File > New > Table. The Create Table Wizard appears. Follow the instructions in the wizard, ensuring that the Encrypt the table's data option is selected.

Accessing an Encrypted Database

To start a database that is encrypted with simple encryption or has simple table encryption, you do not require a key. Since simple encryption does not use a key, the only way to access the database or table data is to have the connection parameters. The database file itself is obfuscated.

To start a database that is strongly encrypted or a database with strongly encrypted tables, you must specify the encryption key. You do this by including the -ep or -ek option when you start the database server.

The -ep option indicates that you want to be prompted by a window to enter the encryption key. The key is not seen in plain text.

dbeng16 -ep c:\demo.db

The -ek option ca nalso be used to specify the encryption key. The -ek option is included in the database server command after the database file. The key is typed in plain text.

dbeng16 c:\demo.db -ek myencryptionkey

Another way to connect to the database is using the Sybase Central Connect dialog. When entering the connection parameters, on the Database tab, you can specify the database encryption key.

Page tree

Purpose

Overview