prevent replace sql database files with old one

Question

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?

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 '14 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 '14 at 19:53
OK, then don't give them Administrator access to the server or SA access to SQL. — joeqwerty, Nov 25 '14 at 19:56

score 3 · Answer 1 · answered Nov 25 '14 at 20:53

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.

answered Nov 25 '14 at 20:53

Katherine Villyard

18,550
4
37
59

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 '14 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 '14 at 20:59
1

Right, but you can't be too cautious, so a good suggestion. – joeqwerty Nov 25 '14 at 21:00
2

And backups... encrypt those backups you should be taking. – HopelessN00b Nov 25 '14 at 21:05

prevent replace sql database files with old one

1 Answers1