Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database Security with Distribuatable Application Expand / Collapse
Author
Message
Posted Saturday, March 14, 2009 9:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 12, 2012 9:44 AM
Points: 6, Visits: 51
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 !!!
Post #675898
Posted Saturday, March 14, 2009 9:34 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:31 PM
Points: 32,780, Visits: 14,941
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #675906
Posted Sunday, March 15, 2009 10:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 12, 2012 9:44 AM
Points: 6, Visits: 51
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 ?
Post #676122
Posted Sunday, March 15, 2009 11:35 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:31 PM
Points: 32,780, Visits: 14,941
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?







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #676140
Posted Sunday, March 15, 2009 11:46 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #676141
Posted Sunday, March 15, 2009 11:50 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #676143
Posted Sunday, March 15, 2009 2:49 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, April 03, 2014 10:06 PM
Points: 6,621, Visits: 1,851
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #676174
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse