0

i have a database that i use in my application.

the operation i want to prevent is if i stop sql server and take the database files to another location then start the sql server and make some operations then stop the sql server services and replace the database files with the old one and start the sql server: i want now sql server say error and don't open the database

how can i make this situation using sql server 2012?

Improve this question
4
  • I'm not completely understanding your question, but why would you do it that way? Why not just backup the database, restore it to the second server, and leave the first server alone?
    – joeqwerty
    Nov 25, 2014 at 19:46
  • you don't understand my question i want to prevent the user of my application to stop sql service , save database files, then start the sql service and make any operation to my database then stop sql then return the database to the old files as his operation not done
    – امير طارق
    Nov 25, 2014 at 19:53
  • OK, then don't give them Administrator access to the server or SA access to SQL.
    – joeqwerty
    Nov 25, 2014 at 19:56
  • So... You want to encrypt your database, then?
    – HopelessN00b
    Nov 25, 2014 at 21:01

1 Answer 1

Reset to default
3

If I understand your question, it sounds like you're looking for something like Transparent Data Encryption, in hopes of preventing a user detaching the database, attaching it to their own server, making changes, and re-attaching it to the original server. Transparent Data Encryption would require the user to have a certificate and a private key in order to attach or restore the database to a different server.

This requires Enterprise (or Developer or Evaluation, neither of which is licensed for production use), and I'm not convinced it's 100% foolproof if the user has administrative access to the server. You'd need to prevent the user from having access to export the certificate, for example.

Unfortunately, joeqwerty is correct that you'll need to not give the user Administrative access to the server or SA access to SQL, and will have to block the user's NTFS permissions to the SQL directories, etc.

Improve this answer
4
  • Good point on the NTFS permissions, although the database files shouldn't be able to be copied while SQL Server is running and the database is "mounted", right?
    – joeqwerty
    Nov 25, 2014 at 20:56
  • No, you're absolutely correct, they shouldn't. They're in use. So the user would have to be able to stop or crash the SQL Service in order to copy the files anyway. Still.
    – Katherine Villyard
    Nov 25, 2014 at 20:59
  • 1
    Right, but you can't be too cautious, so a good suggestion.
    – joeqwerty
    Nov 25, 2014 at 21:00
  • 2
    And backups... encrypt those backups you should be taking.
    – HopelessN00b
    Nov 25, 2014 at 21:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .