How can i secure my MDF file?

How can i secure my MDF file?

• RON 7659

  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

  
  

• Chris Lee

  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

  
  
  

• mickers

  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

  
  
  

• anjumahesh

  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.
  
  
  

• Ben Fidge

  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.
  

• Ger van der Kamp

  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
  
  

• p.partovi

  HI,

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

  
  
  

• bexter

  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.
  

• MobileDev

  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!

  
  

• JamesWalshe

  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
  
  

• SAMI_12

  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

  
  
  

• scsaba

  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

  
  
  

• Aaron Stern

  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
  
  

• Hemant.Shelar

  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.

  
  
  
• How can i secure my MDF file?