Hi everyone,
I have a desktop application that needs to read data from a database, both installed on client computer.
That data in database must be encrypted, to deny client access, and protect data.
I need to know what kind of database to use that can supports encryption, because the amount of data in some tables will be very huge, I will need efficiency too.
I read some about this on web and perhaps SQL Server Compact Edition will be a good choice.
Can someone help me with this point?
Thanks
SQLite has an Encryption Extension (SEE) that allows an application to read and write encrypted database files: http://www.hwaci.com/sw/sqlite/see.html
This DB may be a good fit for a desktop application, and is widely used. For example, I believe FireFox uses it internally.
SQL Server 2008 includes a feature called Transparent Data Encryption which may do what you need. I don't know if Compact Edition contains this feature. A little googling with those keywords should get you started. I don't normally advocate using SQL Server, but in this case I'm not aware of any similar features from other DB vendors (but they must exist...).
That said, what do you hope to gain by encrypting the data? If this is entirely client-side, then the client is going to have the key to decrypt the data. At best, you're making a slight hurdle for people to get at your database contents. It won't be secure by any meaningful definition.
SQL Server CE has no support for encryption whatsoever. You can at best encrypt the database file using the host OS encryption facilities. The file level encryption does not work for databases because to read page X in the file one has to decrypt all pages 1...X-1 to get the encryption key in the appropriate state (reach the proper CBC block state). BitLocker on the other hand works fine as it can decrypt/encrypt pages in the file individually. But BitLocker is a partition level option, not file level. These general considerations apply to any plan to encrypt the entire database file, irrelevant of the product involved (SQL CE, SQL Express, MySQL, Access, anything).
SQL Server has database level encryption. The easiest to use is TDE, Transparent Data Encryption but this requires Enterprise Edition. The other option is to use the cryptographic functions and manage the encryption yourself. Hardly easy to use, but is available in the free Express edition.
You cannot prevent a determined attacker from accessing the decryption key and accessing the database. This is effectively a copy-protection scheme, and they are all broken.
Update: The question states, "That data in database must be encrypted, to deny client access, and protect data." If the client has access to any application that can access the database, he has access to the key used by the application, and can bypass the application to access the database directly.
If the inescapable logic has no appeal, consider the anecdotal evidence of failed copy-protection schemes attempted to protect music, games, and other digital assets.
Might I recommend SQLCipher? It's a free and open-source implementation of SQLite that supports transparent, page-level encryption. It's similar to SEE, it's under active development, and has experimental support for a number of different ciphers, as it uses OpenSSL for some of its implementation. Full disclosure: I'm one of the developers! We've got a tutorial on using it in iPhone applications that will give you a basic idea of how it works, and there's a comment thread that expands on some related topics. Obviously, using it in Visual Studio will be a tad different than XCode, but you should be able to hook up the linking and get it going in a Windows environment.