How can i secure my MDF file?

Question

If a user is a local admin of the box they can gain full access to the database via integrated security. They can create their own database and attach .MDF
How can i secure the .MDF so that no one can gain access to it?

Answer 1

Hi,

Open Enterpirse Manager and navigate to Security and then expand Logins.
In the Logins delete the entry Builtin\Administrators and specifically add the user whom u want to give access to.

Regards,
Vikram

Answer 2

But if i distribute MDF in installion cd any client with local admin access or sa access and sql server on the same box can attach this mdf file and access the db.

Answer 3

There is no bulletproof solution that will protect the database from a box admin. You cannot prevent the box admin from being able to read the MDF file, and if he can do that, he can attach it to his own SQL Server installation. The box admin could also attach a debugger to the SQL Server process and try to read passwords.

The main difficulty in protecting the database from the box admin is that the server it is attached to is supposed to read it, so that means there is a way to access it locally, and you cannot hide that method from a determined box admin, you can maybe only make it a little harder for him to find it, that's all.

Thanks
Laurentiu

Answer 4

hi,
you can distribute the backup file(Password protected) of that database, and then restore using query.
you can programticaly restore the database using OSQL EXE available in tools folder of microsoft sql server

Answer 5

And if the user stops sql, copy mdf just created with this method and attach that to a new sql server?

No way about this question. As maximum is possible to protect only stored precedures with encryption.

Answer 6

The encryption option for stored procedures is really obfuscation. See Books Online for more information. The name "encryption" had to be kept in the syntax of CREATE PROCEDURE for backward compatibility; it does not imply strong encryption.

Thanks
Laurentiu

Answer 7

Think about what you are doing and what you are worried about - you're distributing a file on CD, but you don't want anyone to access it. What's the point?

You want people to attach your db but not be able to access it?  Again, that's nonsense.


However, you can secure the data with encryption.  Identify the actual risks you are worried about and address them individually - the MDF is but a piece of the pie.

Answer 8

Of course he is worried about db structure, not data...

Ok, exist a way doing properly what you want, but is compliacted, and not automated.

Follow this steps:

1. Create a Windows User with Administrative rights.
2. Log on with that user
2. Create a folder for the mdf file.
3. Remove rights from that folder for all other Windows Users excpet the user you just created.
4. Encrypt that folder with windows encryption (owner will be only the user you are logged on)
5. Go to Services and change MSSQL service user to the user just created
6. Copy MDF to the encrypted folder and attach database
7. Log off

Of course dont forget to remove Administrator Login from SQL Server and add the windows user you created. And don't leave sa pass blank...

In this mode even administrator can't access that folder. Changing password from User Management for that user is not possible, because in that mode all encrypted folders are lost.

Answer 9

Hi, Renis!

Interesting idea !  Now we have no way  to protect MS SQL databases except tricks.

But will it be a reliable solution? - might be MS company will fix this "problem"? 

What will happen if SQL Server service try to start with windows account which does not exist - how OS can check the account that used to log on for SQL Server service?

Answer 10

The problem with this solution is that it's manual, if you try to automate it you will then have the problem of protecting the password you set for this account. See my reply to your original post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=371562&SiteID=1.

You cannot use this solution to protect the database that you ship with your application.

I'll comment more on this on the newer thread.

Thanks
Laurentiu

Answer 11

Wow

 

This is a real let down.

From an engineers perspective I can see that the only true way to expect encryption of data and structure is runtime implmentation of some integrated on the fly encryption.

Seeing that MS doesn't implement this into the framework of SQL Server, I certainly am not expecting to succeed where they have failed, especially without the source for SQL Server.

So, as to contents ?   On the fly encryption

as to DB table structres ? Zilch ?

I SUPPOSE technically SQL Server would have to allow encrypted schema meta data and at runtime force all decrypted info to memory, which is a space hit,

AUGH! - 

In a perfect world,  if the user had enough memory, the entire MDF could be decrypted ONLY to memory I suppose.  I can see no other way that prevents exposing access to a 'file' that is decrypted for an instant.  Maybe some kind of 'partially' encrypted workspace on disk as a cache perhaps,  where one would always be looking at a 'mix' of encrypted data mixed in with unencrypted, and this no doubt would cause SOME overhead to pull off.

Seeing as it's unfair to request a user to hold 2 GB + of RAM (not that MSDE 2000 is limited to this as to allowed memory, heh, that's only DB size that's capped at 2GB), I can see no way to protect the structure of the MDF from being seen if sp_attachdb is used.

I CAN see 3rd party COM encryption for MSDE 2000 in my case, at the expense to the user for the runtime decryption, which I've not figure out if SQL engine truly does internally or not.

Oh well.

 

Damn human's  - can't trust em ? Isn't THIS the real problem ?

Paindeer

 

Answer 12

The problem is not encryption, the problem is protecting the encryption key.

Also, decrypting to memory doesn't protect from an administrator who can dump the memory and read the decrypted data.

Thanks
Laurentiu

Answer 13

problem with creating a new admin user is.... another admin can delete the user. Then all the files that have been encrypted can no longer be accessed..... that is very bad!

 

Answer 14

HI,

as stated earlier no way protecting MDF/LDF files from Box Admin he can get access to those files.

Answer 15

Use TrueCrypt to create a volume as a container to hold the .mdf and .ldf files that hold you data.

Answer 16

I am very sure; To the companies it does not import the author's copyright for them of third and not only only it happens with Microsoft.

I see a trend of the big companies to sell your software, besides the fact that the source code is shared; Of fact not openly.

This does that your software is more spread, have a wide community at the cost of us ...   and sales.

Sorry:
notices: I use an Internet translator

Answer 17

There is one solution not Use MS SQL user any other open source database give this all facility. 

Answer 18

so in SQL 2008 maybe there's something like transparent encryption ?

Answer 19

   If you are looking for protection of data at rest (i.e. stored the data in an encrypted form in disk), then TDE can be helpful as it encrypts every page being written to disk. The root of this protection mechanism resides in a certificate, which is protected by the master DB master key (DBMK). That way an unauthorized SQL Server user who doesn’t have access to the certificate won’t be able to read the MDF file. Please notice that the machine administrator still has control over SQL Server process and hence still has control over all the data stored and used by SQL Server itself.

 

   If what you are looking is protection against an administrator, you are really looking for Digital Rights Management (DRM), and SQL Server does not provide such infrastructure. Laurentiu wrote and excellent article that talks about this particular topic: http://blogs.msdn.com/lcris/archive/2007/09/24/security-and-copy-protection.aspx

 

  I hope this information helps. If you have any additional questions or feedback on TDE, I would recommend starting a new thread to distinguish between the two different topics.

 

-Raul Garcia

  SDE/T

  SQL Server Engine

 

Answer 20

Hi,
Recently we have released tool which does exactly what you need. It hides database design and completely encrypts database and log files.
Free version (unlimited by time) is available: you can work with databases up to 77 MBs and use/distribute it freely.
It works with SQL Server 2008 and 2005 (X64 and X86)
The tool is called DbDefence and can be found easily.