How to implement local database in .NET when the Database files cannot be Protected?

Question

I was asked to write a database and it's GUI frontend for some non-profit organization I am member of.

The decision was to use .NET as I know C# fariy good and writing GUI is quite easy. I though of implementing local SQL Server database (as I worked earlier with MySQL) and connect to it via SqlClient interface. But then I discover some problem:

Machine with database installed is public. Even account from which database would be accessed is freely available. So as far as I know anyone who will bring portable MS Access would gain a free access to any data stored in database. Perhaps I am wrong and it is possible to encrypt database with password independent from user account. But I don't know how to do that (I do not have MS Access, only basic Office and Visual Studio Express).

Or if it is impossible, what is the best way to replace SQL Server database?

Answer 1

If the database files are accessible to anyone, then anyone can just delete them. Encryption won't prevent that.

---

Having said that, if you can't use the file system to make the database files themselves secure, then you'll have to use database encryption. SQL Server does support this.

Answer 2

Have you considered SQLite? There is a .NET library for it, and installation/setup are very easy. It runs as an "embedded" database, so it would be just another DLL and data file for your app.

http://sqlite.phxsoftware.com/

Encryption does exist for SQLite, but I haven't used that feature so I cannot personally recommend it.

Answer 3

Just because the machine is public doesn't mean the data is freely accessible. You need to investigate security on MS-SQL. You can limit access to databases, specific tables, and stored procedures on a user by user level, or on a group level.

Answer 4

If your database requirements are not too heavy, you could use sqlite. It doesn't have a 'server' and doesn't require authentication. It would reside on the same machine as your actual application as a file.

Assuming the actual machine on which you run your app (as opposed to the public machine on which the db runs) doesn't have any public accounts, it should be fairly safe.

Answer 5

MSSQL or MySQL would be your best bet. You can set a user (independent from the OS) on both and use it to login from your C# code.

Answer 6

You may want to check out SQL Express. It is free and lightweight. It does not have all the features of a fully licensed SQL Server, but it probably has what you need.

SQL Express

Answer 7

If the file system must be open to anyone, then you'll need to encrypt the database. People who suggest setting up users with limited access are wrong; this won't prevent someone from simply opening the files and peeking at the data. The logins for SQL server don't protect you from someone simply opening the underlying data files in notepad. They are not easy to understand, but not encrypted.

SQL Server does have the ability to encrpyt the data files -- see databasejournal.com -- but I wouldn't recommend it.

If you can, look to securing the machine using standard Windows file security. Then make sure that you deny read permissions to these files to every user except the one running SQL Server -- usually the local system or network user, IIRC.

Answer 8

As several people have pointed out, if the database files are public then the data is public. Encryption is not going to help you. Switching to SQLite will not help; you have to make the database files non-public.

One way to do this is to store your database on a private server and expose the data through a public API via SOAP, REST, web services or such. The GUI front-end will send requests to the private server and display the results.

Consider making the application a web app; the GUI front-end becomes an ordinary web browser.