February 6, 2009 at 2:22 am
Hi All,
Use Case:
Need to prevent a Windows/SQL Server Users from attaching a database and allow only the DBO of that Database to Login, view and modify the database.
Any other user if found must not be able to view the database contents.
This scenario is not a traditional one, the case is that if MSDE / SQL Express is installed on Client Machines, how can we prevent them from accessing the MDFs, and attaching them to a different server.
I have managed to achieve granular permissions on SQL Server and Windows.
For example:
There is Instance 1 and it has a database Sales and the login is called Anil.
Now I have given Anil the DBO permission for the database and removed the BUILTIN\Administrators Login from the Instance 1.
Now here is the problem.
When I try to stop INSTANCE 1 and copy the mdf and ldf to Server 2 and try to attach it to INSTANCE 2, where Builtin\Administrators and sa is enabled. Now how do I ensure that sa does not have access to the database?
I am really confused... Am I missing something?
Any help appreciated.
Cheers!
Kind Regards
Anil Mahadev
SQL Server DBA
MISPL
Bengaluru
INDIA
February 6, 2009 at 3:38 am
I would look at encryption first. Is this on 2005?
February 6, 2009 at 4:15 am
I think this is what you want to do.
http://www.sqlservercentral.com/articles/Administering/implementing_efs/870/
February 6, 2009 at 7:31 am
If they are an administrator on the system, you really can't stop them. You could deploy EFS, but then you've got to decide on the key escrow situation. You'll also take a performance hit by doing so.
K. Brian Kelley
@kbriankelley
February 7, 2009 at 2:35 am
Hi Mike and Brian,
Thanks a ton for your answers. Really appreciate it
This is on SQL 2000.
I will obviously be implemented in 2k5 / 2k8.
But will be the EFS permissions still remain on the drive despite a server crash and if the hard drive is attached to another machine?
The problem is that , in the event of a server crash someone may take the hard drive and try to attach it to another system and try to reattach.
So I am pretty confused on how to proceed. Is there any way or tool that can apply a password to the mdf similar to backup and restore?
Kind Regards
Anil Mahadev
SQL Server DBA
MISPL
Bengaluru
INDIA
February 7, 2009 at 8:01 am
If you use EFS, you won't be able to copy unless you possess the key. You'd have to log as the SQL Server service account or an account that could recover the key. That's why I say EFS causes more problems than it solves. If encrypting the data is really that important, encrypt it within the application and store the encrypted form in SQL Server.
K. Brian Kelley
@kbriankelley
February 7, 2009 at 8:43 am
Hi Brian,
Yes we are ensuring that only 'X' user present within the database can access the application.
So I guess writing a batch file to execute and lock the folder might be a solution during the application's setup might solve the issue.
Cheers!
Anil Mahadev
Senior SQL Server DBA
MISPL
Bengaluru
INDIA
February 7, 2009 at 9:02 am
It won't. Here's why: If I'm an administrator on the box, I can reset the permissions at any time and there's nothing you can do to stop me. SQL Server 2005 and above try to get around by checking and resetting security every time it touches a database file. But if you make the change when SQL Server is stopped, for instance, it will stay that way until SQL Server is restarted.
K. Brian Kelley
@kbriankelley
February 7, 2009 at 10:55 am
Hi Brian,
Thanks for the heads up and advice :). Will need to ask the Developers to try to encrypt the info from the App itself :).
Cheers!
Anil
December 22, 2010 at 4:29 am
Dear all,
From last 2 days i have a question in my mind the question is how can we secure MDf file by been copyed and stop the access by any unknown or any user except me.
sir plz help me out
Anil Mahadev-992042 (2/7/2009)
Hi Mike and Brian,Thanks a ton for your answers. Really appreciate it
This is on SQL 2000.
I will obviously be implemented in 2k5 / 2k8.
But will be the EFS permissions still remain on the drive despite a server crash and if the hard drive is attached to another machine?
The problem is that , in the event of a server crash someone may take the hard drive and try to attach it to another system and try to reattach.
So I am pretty confused on how to proceed. Is there any way or tool that can apply a password to the mdf similar to backup and restore?
Kind Regards
Anil Mahadev
SQL Server DBA
MISPL
Bengaluru
INDIA
December 22, 2010 at 7:39 am
Please post new questions in a new thread and give details of the SQL version involved.
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
March 8, 2011 at 2:50 pm
Dear all,
From last 2 days i have a question in my mind the question is how can we secure MDf file by been copyed and stop the access by any unknown or any user except me.
sql server 2008 enterprise edition.
sir plz help me out
March 8, 2011 at 2:55 pm
Please post new questions in a new thread. Thank you.
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
March 8, 2011 at 10:03 pm
dear sir,
i don'nt know how to post a new question in new article will u help me
March 8, 2011 at 10:26 pm
Go to the forum that's relevant to the question (click on Forums on the left to get the full list) Click on the 'New Thread' button at the top.
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply