How to protect SQL Server data file.MDF and lock .LDF from being copied ?

Question

I Upgrade a small Ms Acces Desktop application to VB.net SqlServer Express 2008 R2, this application run on different clients LAN Networks ( about 50 ) with one dedicated Windows Server 2008 R2 and two or trhee PC-Clients.

All installations have SqlServer sa password and nobody knows the password, this sa login is only for administratives tasks.

Each user have your own uid and password, these SqlServer logins are VB.NET code embebed and Crypted.

I need to protect the data, schemas, stored procedures, etc. from being copied mdf file and installed in another SqlServer instance to hack the mdf file.

How I can proctect the .mdf to avoid this hack?.

Thx.

Answer 1

You can encrypt data on a column-by-column basis, but it's quite a hassle and if your users have access to your application (even in compiled form) then they'll be able to find the passphrase (or certificate) you've used to encrypt the columns anyway. See here for more details. It also has some serious performance implications...

SQL Server 2008 Enterprise Edition (so it won't work in the Express edition you're using) has a feature called Transparent Data Encryption which can encrypt the entire database file. It doesn't have as much of the performance concerns as the column-by-column encryption I mentioned above, but it still suffers from the problem of anybody with physical access to your client application can extract the decryption keys without too much hassle. Plus, of course, Enterprise edition is really expensive!

Answer 2

If I understand you correctly, [1] you are giving people local access (on their personal computer's hard drive) to a datafile [2] but don't want them to hack the data or even look inside.

There is nothing you can do which will resist several man-days of effort to defeat it. That said:

1. If your data is worth more than that, find another way of distributing your data. We can offer advice there.
2. If your data is worth less than that, there are various things you can do, all of which will require several man-days of effort on your part. We can offer advice there as well.

Okay, which is it?

Answer 3

If you give away the DB files, your task is hard. Once you have given away the data, it's not yours anymore. If you want to prevent copying of the files by legitimate users of the client computers, then there's a little you can do. If you worry that some third-party can copy them, then there exists one solution -- have a key stored on hardware cryptographic device (USB token or smart card). This way the key never leaves the device (crypto operations that use the key are performed on device). The drawback is that you will need to give a token together with the copy of the database. If it's not a problem, than you'd need to do the following:

1. create a TrueCrypt volume (it can work with USB cryptotokens) and put your files on that volume.
2. create a script that will mount the volume and start MS SQL Server (which will use the DB files on the volume)
3. The legitimate user will have to provide a PIN for accessing the device. So if the third-party user steals the device, he doesn't have a pin. If he captures the PIN and files, - he still needs the device. I.e. his task becomes several times harder, than simply copy the database.

Answer 4

Are you saying you are actually deploying a database instance on each client machine?? SQL Server is not intended as a drop-in replacement for MS Access! It is a highly scalable RDBMS, not a desktop database.

I think you may have missed a huge opportunity to have a single, centralized database as a secure repository for your application data. This is how one generally maintains a degree of control over data and code - keep one working copy of the data on a secure server that your users have only indirect access to via the application and its authentication scheme.

This is known as the Client Server model.

If your users are distributed (not in the same office for example), consider writing a web application, again with a central database.

You cannot expect to have any control over data that has left your hands. Keep it on your server.

Answer 5

The answer to your dilemma is simply to store the databases centrally - and ideally just have one database that everyone shares. You should not be installing a SQL server database on every client machine. Not only do you have security risk, you are also at risk of data loss because users won't be backing up their machines.

No one should ever be on the same server as the mdf file.

Follow these steps and you will be better off.

• Put your SQL database on a dedicated server
• Allow clients to connect to the database as a limited permission user (not as 'sa')
• Don't give any client access additional access to that server