protecting sql server database file

what is the recommendations should i do to prevent anyone from hacking or getting the sql server data base file (MDF File) ?

Note : i use sql server 2005
more ▼

asked Mar 31, 2011 at 02:29 AM in Default

MZT gravatar image

13 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

Really your only option here is to properly protect the server. You can use Encrypting File System (EFS) and encrypt the directories where the SQL Server data files reside (or you could go through the trouble of encrypting the files manually). If you go this route, make sure you plan it out carefully, including how to get the files decrypted by an account other than the service account running SQL Server.

The issue is if I compromise the server, I will likely gain control of the account which serves as an escrow account for the purposes of decryption. At the very least, I have the ability to stop and restart SQL Server in single user mode and as an administrator of the server, SQL Server will automatically escalate me to sysadmin status as of SQL Server 2005. This is a "back door" that Microsoft built in because of so many folks locking themselves out of their SQL Servers in SQL Server 2000.

Which is why I come back to: properly secure the server.
more ▼

answered Mar 31, 2011 at 03:19 AM

K. Brian Kelley gravatar image

K. Brian Kelley
933 2

(comments are locked)
10|1200 characters needed characters left

Firewalls, strong password policy, normal ACL work. You should secure your MDF-file the same way you would secure any other sensitive information in your server.

You could read up on encryption in SQL Server 2005. There's an OK (subjectively put) article on SQL Server Performance: [http://www.sql-server-performance.com/articles/dev/encryption_2005_1_p1.aspx][1]

But you should keep in mind that encrypting data is really no help if someone gains access to a local administrator account.

[1]: http://www.sql-server-performance.com/articles/dev/encryption_2005_1_p1.aspx
more ▼

answered Mar 31, 2011 at 03:17 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

In this case, though, that article refers to SQL Server's built-in encryption, which would like require a retrofit of the database and the applications. A painful process (been there).
Mar 31, 2011 at 03:21 AM K. Brian Kelley
(comments are locked)
10|1200 characters needed characters left
If you're on 2005 Transparent Database Encryption isn't an option. I think Windows file encryption is possible but I suspect it would really slow SQL Server down. Make sure access to the server is as restricted as possible. Check out the tools suppliers like Red Gate, Quest and Idera. One of them might offer something at a price. Personally I worry more about security of backups as they tend to go offsite. There are definitely 3rd party tools to encrypt backups. Those tools usually compress backups too which makes them much faster and provides another - admittedly thin - layer of protection as you need the tool to use the backup even if it's not encrypted.
more ▼

answered Mar 31, 2011 at 03:10 AM

David Wimbush gravatar image

David Wimbush
5.1k 29 31 34

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 31, 2011 at 02:29 AM

Seen: 1184 times

Last Updated: Mar 31, 2011 at 02:29 AM