Protecting my database files

I have a Windows .net application usi开发者_运维百科ng SQL Server 2005 Express as database, which is used on a server with multiple clients. The users insert, view and update data via the application.

For this, I have to put the .mdf files in a folder on the server. Is there any other way?

Now in this case, anybody can take those .mdf files and open them in their SQL Server & view the database objects such as tables,views and procedures.

Therefore, I want to protect my .mdf files with high-level or maximum security and ensure that :

1. no one can even copy those files from the server.

2. if the files are copied then they themselves should not open,

3. if opened then they should ask for passwords

So, please guide me how to implement this security?

Also, I want to know when someone copies the database files, then whether the user, logins, roles are also copied or not?

We have looked into this as well. In reality with SQL Express, someone can simply stop the service and copy the database files without issue. These can then be ported over to another instance.

Windows security and other products whether you use group policy or some other after market tools that can lock down the file system are your best bets for file system security.

I beleive SQL does offer exactly what you are looking for and that is TDE encryption. Sadly this is only available in Enterprise edition and how many folks does that actually help out.

You could also encrypt specific columns directly in your database. If you have a certain set of tables that you are worried about this is probably the best option offered with Express at the current time.

Check out this article by Dave Pinal: http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/

I have tried it and it does work well to protect your sensitive data.

I believe in SQL Server you can enable encryption then if somebody gets your .mdf but does not have valid credential should not be able to see the data in it.

You might want to think about actually using a database server instead of just a database file. Using a server will allow you to lock down who has access to the database and make it so that only your application (through a username and password) can access it.

This would mean using SQL Server 2005 Express (I think it's limited on the number of connections) or MySql Server.

If that really isn't an option then:

1) You would have to secure the folder, but this might mean your application cannot access the mdf as well.

2 & 3) Both can be tricky. I think that there are some third party tools that can encrypt a file for you, but that will mean you have to change your application and how it accesses the file.

4) You could make a custom service that watches the directory, but you will not be able to tell if the internal aspects of the mdf are copied or not. Generally, if the mdf is copies you are probably safe assuming that the roles, logins and users were copied with it.