How to protect MDF & LDF file from copy ?

Question

How to protect MDF & LDF file from copy ?

Rajat Jaiswal-337252

SSCommitted

Points: 1779
More actions
March 8, 2011 at 4:29 am

#233683

Hi expert,
How can we secure database on client's machine so that then user can not copy MDF & LDF file?
Thanks & Regards
http://www.indiandotnet.wordpress.com

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Short answer: you can't.

If you are putting the database on the client's server then their DBAs have full control. On SQL 2008 you could use TDE, but that would at best slow someone down. They would be able to disable it or to back the certificates up because they have full control over the SQL Server

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dwayne Dibley SSCrazy Points: 2350 More actions · Answer 2

By user, do you mean application users? That would be up to the admins of the machine. They could restrict user access to the directories where the files are. If you are referring to sysadmins, then that has been answered by GilaMonster.

Rajat Jaiswal-337252 SSCommitted Points: 1779 More actions · Answer 3

Hi,

Actually situation is We have to install our database on client machine (end user machine) and obviously the end user have full SQL SERVER control but we want some way by which he/she can not copy the MDF/LDF. or if they copy they can not restore on other machine.

some way to encrypt the data.

Thanks

Rajat Jaiswal

http://www.indiandotnet.wordpress.com

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 4

We have to install our database on client machine (end user machine)

Why? If it's a local DB instance on each machine, and each user is in control of the data that goes into it, surely it can't be that classified?

Is there no way to centralize?

Have you looked into locking down the desktop's and folders, just granting each user access to the DB instance and DB level instead of full blown local admin on the workstation?

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Rajat Jaiswal-337252 (3/8/2011)
Actually situation is We have to install our database on client machine (end user machine) and obviously the end user have full SQL SERVER control but we want some way by which he/she can not copy the MDF/LDF. or if they copy they can not restore on other machine.

Can't be done. If you install SQL and the database on a user's computer and that user is sysadmin in SQL, they can do anything they like to the database. You can slow them down, but you cannot prevent them from doing whatever they want.

If you must secure the database from the client, host the database, don't provide it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rajat Jaiswal-337252 SSCommitted Points: 1779 More actions · Answer 6

GilaMonster (3/9/2011)
Rajat Jaiswal-337252 (3/8/2011)
Actually situation is We have to install our database on client machine (end user machine) and obviously the end user have full SQL SERVER control but we want some way by which he/she can not copy the MDF/LDF. or if they copy they can not restore on other machine.
Can't be done. If you install SQL and the database on a user's computer and that user is sysadmin in SQL, they can do anything they like to the database. You can slow them down, but you cannot prevent them from doing whatever they want.
If you must secure the database from the client, host the database, don't provide it.

Hi thanks for the update

But the problem is this is my client requirement so i have to try up to max level i can.

I recently got idea of "symetric key" if you people can put some light on how can use symetric key to secure database than that would great for me.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Rajat Jaiswal-337252 (3/9/2011)
But the problem is this is my client requirement so i have to try up to max level i can.

Then you need to explain to the client that what they are asking cannot be done. If a database is placed onto a client machine, then any local admin can gain full and complete control of that database and do anything they want and you cannot prevent that.

You can get the front end app to encrypt columns, that's about the best that can be done, but there is no way to secure the database or the database files against an administrator of the machine it is on.

I recently got idea of "symetric key" if you people can put some light on how can use symetric key to secure database than that would great for me.

Symmetric key is part of encryption. Big area, not that useful here.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rajat Jaiswal-337252 SSCommitted Points: 1779 More actions · Answer 8

GilaMonster (3/9/2011)
Rajat Jaiswal-337252 (3/9/2011)
But the problem is this is my client requirement so i have to try up to max level i can.
Then you need to explain to the client that what they are asking cannot be done. If a database is placed onto a client machine, then any local admin can gain full and complete control of that database and do anything they want and you cannot prevent that.
You can get the front end app to encrypt columns, that's about the best that can be done, but there is no way to secure the database or the database files against an administrator of the machine it is on.
I recently got idea of "symetric key" if you people can put some light on how can use symetric key to secure database than that would great for me.
Symmetric key is part of encryption. Big area, not that useful here.

Hi,

Thanks for the update.

recently i visited following site http://www.elitude.net/

The way by which DB defence working i need something like same.

I am sure that symmetric key is main part of this DB defence need to understand symmetric key in more detail.

but could not get any detail explaination.

I hope you got my point.

Please check the link.

Thanks

Rajat Jaiswal

http://www.indiandotnet.wordpress.com

smitty-1088185 SSCrazy Points: 2321 More actions · Answer 9

Remove the Builtin\Administrators group from sysadmin in SQL. Encrypt the db's and give the users only permissions they need. If it's a Windows computer you could also encrypt the folder where the databases reside.