SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Security with Distribuatable Application


Database Security with Distribuatable Application

Author
Message
Anirudh
Anirudh
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 59
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 !!!
Steve Jones
Steve Jones
SSC Guru
SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: Administrators
Points: 144022 Visits: 19424
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
My Blog: www.voiceofthedba.com
Anirudh
Anirudh
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 59
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 Smile

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 ?
Steve Jones
Steve Jones
SSC Guru
SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: Administrators
Points: 144022 Visits: 19424
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
My Blog: www.voiceofthedba.com
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34366 Visits: 9518
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."
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34366 Visits: 9518
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."
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (24K reputation)

Group: Moderators
Points: 24406 Visits: 1917
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search