Encrypting a Database
The Database Engine for SQL Server Compact 4.0 lets you encrypt databases. Databases that are encrypted are also protected with a database password.
Important
Because encrypted databases can only be accessed with a password, if the password for an encrypted database is lost, the data is unrecoverable.
Encryption Modes
SQL Server Compact 4.0 supports the following modes of encryption to encrypt the database. The new crypto algorithms (combination of Encryption and Hashing) are mapped to these encryption modes.
Advanced Encryption Standard (AES) is available on all SQL Server Compact 4.0 supported platforms for encryption with key length of 128-bit and 256-bit. The Secure Hashing Algorithm (SHA) is available for hashing with key length of 256-bit and 512-bit.
Platform Default: The algorithms used in this mode are AES128_SHA256, where AES128 is the encryption algorithm with 128-bit key and SHA256 is the hash algorithm with 256-bit key. This is the default encryption mode option on all SQL Server Compact 4.0 supported platforms.
Engine Default: In this mode, the database is encrypted using AES256_SHA512, where AES256 is the encryption algorithm and SHA512 is the secure hash algorithm. The default key length is used to maintain backward compatibility with SQL Server Compact 3.5.
Note
You can specify the type of encryption used to encrypt the database by setting the connection string attribute, Encryption Mode, to one of the available encryption modes: Platform Default, or Engine Default.
Creating Encrypted Databases
You can create encrypted databases by supplying both encryption and password properties when the database is created. Encrypted databases can be created by the following methods:
Using SQL syntax
To create an encrypted database by using SQL syntax, specify the database_password. This will encrypt the database by default. The encryption mode will be Platform Default. For example:
Create Database "secure.sdf" databasepassword '<password>'Using the .NET Compact Framework Data Provider
To create a password-protected database by using the SqlCeEngine.CreateDatabase method, you must specify the password property in the connection string. For example:
"data source=\secure.sdf;encryption mode=platform default;password=<enterStrongPasswordHere>;For more information, see the System.Data.SqlServerCe.SqlCeEngine class in the Microsoft Visual Studio documentation.
Using OLE DB
To create an encrypted database by using the OLE DB provider for SQL Server Compact, you must specify the encryption mode with the provider-specific property DBPROP_SSCE_ENCRYPTIONMODE and specify a password by using the provider-specific property DBPROP_SSCE_DBPASSWORD.
Accessing Encrypted Databases
A password must be supplied to open an encrypted database. Encrypted databases can be accessed by the following methods:
Using the data provider for SQL Server Compact
To access a password-protected database by using the SqlCeConnection.Open method, you must specify the password property in the connection string. For example:
"data source=ssce.sdf; password=<enterStrongPasswordHere>"For more information, see the System.Data.SqlServerCe.SqlCeConnection class in the Visual Studio documentation.
Using OLE DB
To open a password-protected database in OLE DB, you must specify the provider-specific property DBPROP_SSCE_DBPASSWORD.
Note
If you attempt to open a database file created using an earlier version, of SQL Server Compact, you will get a message that the file cannot be opened. This is because the Database File Format for SQL Server Compact 4.0 is different than the earlier versions. To open the database file by using SQL Server Compact 4.0, the database file must be first upgraded.
You can upgrade SQL Server Compact 3.1/3.5 databases to SQL Server Compact 4.0 database but not SQL Server Compact 2.0. For more details, see Upgrading from Earlier Versions (SQL Server Compact)