Database Security with Distribuatable Application

  • We have developed an Application in c#, .net platform with Crystal Reports - and want to distribute the database for the same. With the Installer - we are distributing MSDE - and using scripts to set it up.

    What we are concerned with, is the security of the database. As a lot of the BI is built into using SP's and other Objects - Is their a way - whereby which we can Protect / Lock or Restrict users to connect to the database - asides the application.

    Even, if the MS Sql Server's Administrator - wants to log in - he may not get the permission to do the same ... until and unless - he does not have the specific password - or something ...

    Look forward to suggestions - Please HELP !!!

  • Not sure you can do anything about the system adminstrator outside encryption, and you'll take a performance hit here.

    If you setup DDL triggers, you can look for the application name, and that would help somewhat, but the administrator can disable them.

    Application roles will help here. You can not setup any rights for users except to connect and then have them execute an application role, which has a password and rights for the tables.

    If the server instance is not under your control, and it won't be with MSDE, you can't stop everyone. You can stop the semi-technical and curious, but they might get around it. More you shouldn't worry about this, write into your sales/support agreement that they can't alter the database and still get support, and don't worry about it.

  • You think creating Application based Roles would help !!! - Cos - at the end of the day - the Application based roles can be overwritten using Administrative Permissions - can't they !!!

    If I use an MSDE - which is what I am doing for my Demo Versions - can you suggest something which I could do their to restrict access ??? ... In which case - atleast the demo can be some what safe 🙂

    What I mean is something - asides possibly setting a Username and Password with SQL Authentication during MSDE Setup ... anything else you suggest ... Or you feel - that is the only safest solution here ?

  • If someone has administrative control of the instance, there's nothing you can do. People will be able to access the data and stored procs. There's no way to stop them.

    So many people sell applications, and they don't worry about this. What are you concerned about?

  • Anirudh (3/15/2009)


    You think creating Application based Roles would help !!! - Cos - at the end of the day - the Application based roles can be overwritten using Administrative Permissions - can't they !!!

    You missed the first thing that steve said, so let me make it clear: You can not distribute a SQL Server database that locks out the Admins. There is no facility in SQL Server that can do anything remotely like this.

    What you can do is to secure your database against everyone else, except the Admins. This is what Steve was describing.

    The Microsoft recommended method of do this (securing the users, but not the admins) is to use Certificates. They are complicated and a little confusing (to me anyway), but they are the best that is available in SQL Server today.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Now, having said that, you can secure your database against all customers including the Admins, but NOT if you distribute it. The key is to host the database instead of distributing it. You might want to look into Microsoft's Cloud/Azure initiative and the new SDS offerings that are in beta right now.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Ultimately you can't stop someone who wants to get at your code from doing so. Even if you put the BI in compiled code, a dissassembler would still reveal what you've done. The point is to approach it legally, as Steve has mentioned. You could encrypt your stored procedures and put in your EULA the standard language about reverse engineering. Can you stop an admin from seeing the code? Absolutely not. But if they tried to act on it and your caught them, you would have legal recourse.

    However, as Steve pointed out, there are a lot of companies boasting that they are transparent about their schema. I can think of two companies I have dealt with in the last six months that used this as part of their sales pitch. Now they weren't BI companies, but they were trying to see us on ease of integration.

    K. Brian Kelley
    @kbriankelley

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply