I'm new to this forum and just want to ask some questions/suggestions on how to manage my database..
I'm having an application with sql server 2005 database as a backend.
I want make the application standalone, the application is installed along with an sql server 2005 installer, so I don't have any control on my app when it is deployed/installed in other computers.
I have my app working fine, but the database is not secure if ever the app is installed on other computer.
Now here's the scenario. (if the user is a techie guy and wants to dig things up)
The user installs sql server 2005.
The user installs the app.
The user can see the database via windows explorer (database is .mdf file)
Opens up SQL Server, uses windows authentication.
Attaches the database, and tada! Instant access to the database.
Now he can freely modify the contents of the database without the use of the app.
So how can I solve this? Is there any possible ways to avoid this? Any suggestions?
Take note that I can't control the application because it is installed on a different place
P.S., I'm quite new to this, actually this is my first project on a company 🙂
Best Answer
So there a couple thoughts here from my end. And I think they probably follow an order:
So there are things you can do, but the biggest question that I would ask if I were you is:
Why?
What I mean is - what is your goal? Customers see databases all the time. In fact as a DBA and Consultant with 15 years of experience, I can honestly say that 90% or more of the databases I work with are wide open to the folks who purchased the application. If they want to, they can certainly play with the data in their database directly. Most of them don't because they like support contracts to remain valid. Some of them do because their vendors are pains to work with and don't give common sense support and updates.
So you should check your motives. If you've made software that someones wants to buy, and they've paid for it and deploy it locally following your instructions and have entered into an agreement with you - why would they want to build their own app? And if they do? What is the harm? Charge enough money and make your agreement strong enough that if they do it is a non-issue.
Also - Is this a new application? SQL Server 2005 is 3 versions back from the currently released version (SQL Server 2012) and a new release (SQL Server 2014) is right around the corner.
You also said "the application is installed along with an sql server 2005 installer, so I don't have any control on my app when it is deployed/installed in other computers"
With the right installers and install procedures you should have control over how the application is installed. There is actually a lot in your question sort of behind the question that is hard to answer in this forum without a lot of back and forth. Some of the questions that come to mind are around areas like "How does your app install? Is there an installer app?", "What are the instructions for the installation and use of SQL Server?" "Are there configuration files in place to help determine where things get placed and how to connect to the database?", "Do you provide guidance to the customer on best practices for deployment?"
Don't answer those questions here - they are out of scope, but those are some of the questions you should be asking yourself as you go on this quest. I would recommend reading up on SQL Server application development and spending more time learning about deploying applications in general. I can't think of a good reference right now - but perhaps someone can add an answer with some. There must be something on msdn.com that can help here.