I use SQL Server 2005. I also use mixed mode authentication BUT I removed the the Windows Administrator account from the LOGIN accounts.
I wish that outside me as DBA all other access to the data and schema be restricted to my .NET application. No other person should otherwise have access to the database. Not even the Administrator of the host sever OS.
My .NET application provides facility for making backup files and restoing same when needed.
My challenge is: How to protect/secure the backup file such that it cannot be restored into another instance of SQLServer unless of course that instance has the same LOGIN & PWD as I have on my original instance.
At present I found out that I can pick up the backup file, walk over to another computer with SQL Server (even SQLExpress) with Windows authencation and the database is fully restored.
Third party tools may not meet the need here. This is my reason.
I have built an application for a client. I deploy the application at the client's site and I want to provide means for the client to do backup and restore w/o me. If I use third party tools then it is the client that will supply password for both sessions so he can replace & peek into the database thereby compromising the intergrity of the solution.
If on the other hand my own application is managing the backup & restore I have the chance of protecting the backup file. That way I maintain the security - that is the client has access to the database but under control of the application i built.
SSMS backup & restore do not provide for password protection. Also SSMS is not an option here as we need a solution that is completely under the control of the application built for the client.
T-SQL can be used inside a .NET application. The T-SQL Backup & Restore commands have Password clauses for both the backup FILE and backup MEDIA. Now the password protection for the backup MEDIA can be overidden by the Administrator of the host computer anytime - (I believe). So that is really no protection in this scenario.
We are left with the pasword for the backup FILE. So how do you rate the strength and reliability of the password for the backup file?