Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 :)

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

So there a couple thoughts here from my end. And I think they probably follow an order:

  1. The first principle is - you will never secure your MDF file from being looked into by someone with SQL savvy. There are ways around most of what you can do. If you are deploying this application to a customer, and that customer has enough SQL knowledge and you've given them a data file - they can do what they like with it ultimately.. This principle rules all of the others.
  2. Support contracts and license agreements protect you legally here - that is outside the scope of this format. But you have to trust the folks buying your app to honor their support agreements and EULAs. Every vendor out there has to do that same thing.
  3. You can, however, do things like encrypt data in tables and encrypt/decrypt it in and out from your application. This can help protect data, but it isn't foolproof. Someone can try and attach a debugger, dig into the traffic and try and get a sense for what is going on.
  4. You can encrypt your stored procedures to protect the stored procedure code - again this isn't full proof (remember Principle #1 rules them all here)

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.

share|improve this answer
2  
+1 for Mike. Also, you COULD be like SAP and implement your database with 30,000 cryptically named tables so that it is so impossible to understand that no would ever touch it. But, don't be that guy, Codemunkeee ... don't be that guy. –  Jon Boulineau Nov 15 '13 at 2:15
    
wow! thanks Mike! That clarified some of my thoughts about my project. Actually I'm not yet on the deployment phase but it is always good to look forward. Anyway, I guess I missed out one important factor about the project. The project is used by a company, well the users of this app are employed on this company but they are situated on different places (like one is from Australia, Indonesia, etc) just like freelancers nowadays. Because of that, I have to secure my database. But now that this isn't really possible, I guess I have to think of other ways. Thank you so much ! :) –  Codemunkeee Nov 15 '13 at 2:34
    
@JonBoulineau hahahaha that's a good suggestion! But that would be my last option if ever nothing's left :))) thanks –  Codemunkeee Nov 15 '13 at 2:35
    
Well it is possible - but it isn't ever complete. And I am not sure I understand why the security concerns. Sounds like you might consider making a web application instead of shipping a database everywhere a single user exists also :-) –  Mike Walsh Nov 15 '13 at 2:45
1  
+1 - Why is the best question ever. Just because you can do something doesn't mean you should. Especially if it'll bite you later, which almost all forms of obfuscation eventually do. Support is enough of a nightmare as it is, without being mired in unnecessary convolution. –  Max Vernon Nov 15 '13 at 4:55
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.