How safe are SQL Server 2005 MDF files? (re: security)

Question

We've started using SQL Server 2005 Express for a project and I noticed that every database has it's own MDF/LDF files. Whenever we need to duplicate our database (to another SQL Server) we simply copy these files and then attach them to the database.

However, this has me wondering about the security of these files. If somebody from the outside gets a copy of them are they encrypted or can they simply attach to them like I'm doing? I'm not prompted for any passwords when I copy/attach them myself so I'm a bit nervous.

Thanks, John

Answer 1

They are not encrypted and can be loaded/attached by any person with read access to the MDF/LDF files.

There are a couple of approaches you can take if the actual MDF files need to be protected. (This assumes that merely using file permissions to lock down access isn't feasible, for example if you need to protect against users with Administrative rights to the machine or direct HDD access.)

1. Use EFS or BitLocker or similar to encrypt the files themselves. This won't protect against other applications in the same session/context accessing the files, but it will protect against other accounts accessing the files (EFS) or someone stealing a laptop/hard drive (EFS/BitLocker).
2. Encrypt the sensitive data within the database itself. You will obviously want to store the encryption key separately (possibly using DPAPI to protect it, for example). SQL Server 2008 Enterprise Edition introduces a feature called Transparent Database Encryption (TDE) to make this process easier, which obviously doesn't help much for 2005 or Express Edition, unfortunately. Your best option in this case might be to do custom encryption of sensitive data in your app. (Unfortunately in 2005 this will require either using varbinary fields or Base64-encoding encrypted values etc.)
3. Don't store the data locally in the first place (i.e. use a remote SQL Server rather than a local Express instance). That doesn't truly solve the problem, but it reduces it (you only need to protect the remote instance rather than N local/express copies).