MSDE Security (aka users looking at my apps database)

Is there a way to secure a database on MSDE so that my users cannot be
poking around my applications database? I've been reading up on it and see
that MSDE installs with a mixed security mode.
Does that mean if I secure my database with sql security someone who is an
admin on the machine can look at my database regardless of what I set?
I am concerned that my users (and competition) will try to hack into my
instance and steal my data & data structures so I'm trying to figure out
how I can prevent that. I understand I can encrypt my stored procedure at
least I hope that is still the case with MSDE.
Any thoughts?
Thanks.
This is a desktop app and not a web app btw.
hi Jason,
"Jason" <yo@.yo.com> ha scritto nel messaggio
news:%234bvqLoeEHA.3016@.tk2msftngp13.phx.gbl...
> Is there a way to secure a database on MSDE so that my users cannot be
> poking around my applications database? I've been reading up on it and see
> that MSDE installs with a mixed security mode.
> Does that mean if I secure my database with sql security someone who is an
> admin on the machine can look at my database regardless of what I set?
> I am concerned that my users (and competition) will try to hack into my
> instance and steal my data & data structures so I'm trying to figure out
> how I can prevent that. I understand I can encrypt my stored procedure at
> least I hope that is still the case with MSDE.
> Any thoughts?
> Thanks.
> This is a desktop app and not a web app btw.
you can only protect stored procedures, views and user define function with
WITH ENCRYPTION in order to hide their text, but for base table you are out
of luck... there's actually non way for preventing sysadmins role members to
dig into your metaschema...
as regard data, that can only be encripted using third parties tools like
http://www.activecrypt.com/index.htm or tools like that
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hello,
It is also possible to use SQL autentification, then create only one login
that the user doesn't know.
After that, your application connect as the login you 've just created. (for
example Log = marc PWD = marc)
In you database, you make a login table. This table contain the login of the
user and his password, and your application will test this table to see if
the user can connect. With this method you can even forbid a part of the
application or a time to connect ... easily.
For example UID Andre PWD Andre.
After that, your user will enter the login and password to your application
(Andre/ Andre).
your application connect as marc, see if Andre can login and if Andre can
login, your application can work with the user name marc with the database
(and show the user the login Andre)
So your user will never be able to work on the database as his login is not
a database login
Marc Allard
"Jason" <yo@.yo.com> a crit dans le message de
news:%234bvqLoeEHA.3016@.tk2msftngp13.phx.gbl...
> Is there a way to secure a database on MSDE so that my users cannot be
> poking around my applications database? I've been reading up on it and see
> that MSDE installs with a mixed security mode.
> Does that mean if I secure my database with sql security someone who is an
> admin on the machine can look at my database regardless of what I set?
> I am concerned that my users (and competition) will try to hack into my
> instance and steal my data & data structures so I'm trying to figure out
> how I can prevent that. I understand I can encrypt my stored procedure at
> least I hope that is still the case with MSDE.
> Any thoughts?
> Thanks.
>
> This is a desktop app and not a web app btw.
>
|||The only way you can lock the data down is to encrypt it. If a user has
Local Admin rights in the OS (NT/2K/XP), then they can see the data anyway,
irrespective of SQL Auth as NT Auth is always available.
Administrator ALWAYS gets to the data!
On Fri, 6 Aug 2004 11:05:29 +0200, Allcomp wrote:
[vbcol=seagreen]
> Hello,
> It is also possible to use SQL autentification, then create only one login
> that the user doesn't know.
> After that, your application connect as the login you 've just created. (for
> example Log = marc PWD = marc)
> In you database, you make a login table. This table contain the login of the
> user and his password, and your application will test this table to see if
> the user can connect. With this method you can even forbid a part of the
> application or a time to connect ... easily.
> For example UID Andre PWD Andre.
> After that, your user will enter the login and password to your application
> (Andre/ Andre).
> your application connect as marc, see if Andre can login and if Andre can
> login, your application can work with the user name marc with the database
> (and show the user the login Andre)
> So your user will never be able to work on the database as his login is not
> a database login
> Marc Allard
>
> "Jason" <yo@.yo.com> a crit dans le message de
> news:%234bvqLoeEHA.3016@.tk2msftngp13.phx.gbl...
|||Hello,
I didn't know it was possible to connect as Administrator in a sql server
when the installation was mado to allow only SQL autentification. How can
you make it?
Thank you
Marc Allard
"Paul Buxton" <psb@.NOSPAMspireite.demon.co.uk> a crit dans le message de
news:1opgizeso0jib$.ywf1zot73zdg.dlg@.40tude.net...
> The only way you can lock the data down is to encrypt it. If a user has
> Local Admin rights in the OS (NT/2K/XP), then they can see the data
anyway,[vbcol=seagreen]
> irrespective of SQL Auth as NT Auth is always available.
> Administrator ALWAYS gets to the data!
> On Fri, 6 Aug 2004 11:05:29 +0200, Allcomp wrote:
login[vbcol=seagreen]
(for[vbcol=seagreen]
the[vbcol=seagreen]
if[vbcol=seagreen]
application[vbcol=seagreen]
can[vbcol=seagreen]
database[vbcol=seagreen]
not[vbcol=seagreen]
see[vbcol=seagreen]
an[vbcol=seagreen]
out[vbcol=seagreen]
at[vbcol=seagreen]
|||hi,
"Allcomp" <fa097770@.nospam.skynet.be> ha scritto nel messaggio
news:41136323$0$296$ba620e4c@.news.skynet.be...
> Hello,
> I didn't know it was possible to connect as Administrator in a sql server
> when the installation was mado to allow only SQL autentification. How can
> you make it?
just log in in Windows as a member of sysadmins; sysadmis are member of
Builtin\Administrator SQL Server role, which has administrative privileges
by default...
the method you previously exposed only prevents user of YOUR application not
to access that data, but using ad-hoc query tools like QA, oSql or simply
Visual Data Manager (VB3 to VB6 <g>) an administrator can perform whatever
activity...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Only One JASON, FORGET IT.
I have been called in more than once to untangle all sorts of developer
installed security, I do it and will continue to do it.
Developers who think that business data should be locked up from the
Business should be locked up themselves. If your data is proprietary,
then I suggest you create your own encrypted storage system! But give up on
trying to lock the rightful owners of data out from it by denying them
access to the database.
Your schema etc. Might be your work, but locking out legitimate uses of
the data is in my opinion about as quick a way to get your application
dumped as you can get. Companies expect to be able to throw a copy of
Crystal Reports onto a machine and interogate the database for the
information that they want, in the manner that they want it, when they want
it.
As foir competitors using your schema in their own products, you must be
joking! They might however write conversion routines to 'upgrade' from your
product. This is a fairly normal business practice.
You sound like Apple complaining about Real Networks, or an IBM salesman
from the 1970s, telling the customer that MS-Dos on a compatible PC is just
not as good as an IBM PC-XT with PC-Dos for running Lotus 1-2-3
Matt
|||hi Matt,
"Unicorn" <unicorn@.somewhere.com> ha scritto nel messaggio
news:epy96HEfEHA.2604@.TK2MSFTNGP12.phx.gbl...
> Only One JASON, FORGET IT.
> I have been called in more than once to untangle all sorts of developer
> installed security, I do it and will continue to do it.
> Developers who think that business data should be locked up from the
> Business should be locked up themselves. If your data is proprietary,
> then I suggest you create your own encrypted storage system! But give up
on
> trying to lock the rightful owners of data out from it by denying them
> access to the database.
not to start a flame, but just a consideration...
I think it really depends... if you "sell" data, that's to say an aggregated
and/or particular kind of sensible data, perhaps having it not accessible to
external application but your(s), could be a legitimated pray for a
particular protection system, and perhaps external encryption is a way to go
for, as you already pointed out... perhaps not the best as it will be not
integrated.. but acceptable...
the same is true for sensible code you provide in form of stored
procedures/udf/views...
let's think about a genious tool, RAC by SQL Server MVP Thanh Ngo (AKA OJ)..
my understanding is it provides a set of stored procedures to manipulate
data, with unique features like CROSSTAB rotations and so on... this is
valuable code and think is provided as encrypted code... as it should
but you actually have no way to protect your metaschema(s) the same way...
even if SQL Server encryption has been defeated... I do personally think
this could be a valuable feature in some scenario...

> Your schema etc. Might be your work, but locking out legitimate uses of
> the data is in my opinion about as quick a way to get your application
> dumped as you can get. Companies expect to be able to throw a copy of
> Crystal Reports onto a machine and interogate the database for the
> information that they want, in the manner that they want it, when they
want
> it.
this could violate certain EULAs and/or rules, both private and/or
legislative...
recently I've been asked to buy a mailing for german (potential)
customers... the selling company provided a well defined license for 1 use,
n uses of the same data... don't know what kind of database they would
provide as our choice went to mailing labels directly, just for one shot...
but this scenario certainly does not grant you the possibility for multi
server analysis and or use...
another issue... think to privacy protection for sensible data... medical
data... there are both european and USA (as other countries too..) acts that
state and (strictly) discipline the use of this kind of data...
a recent article by Sean Maloney on SQL Server Magazine
(http://www.winnetmag.com/SQLServer/A...731/42731.html for
subscribers only) presents a way to protect data from unlegitimated users
with row level security granularity.. but if you manually bypass that kind
of security (which you can, as a syasdmin) you are elegible for legal
repercussions...

> As foir competitors using your schema in their own products, you must be
> joking! They might however write conversion routines to 'upgrade' from
your
> product. This is a fairly normal business practice.
>
agree =;-D
again... not to flame but just my 2 (euro)cents
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thank you for your answer
Marc Allard
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> a crit dans le message de
news:2nhjgsFud2mU1@.uni-berlin.de...[vbcol=seagreen]
> hi,
> "Allcomp" <fa097770@.nospam.skynet.be> ha scritto nel messaggio
> news:41136323$0$296$ba620e4c@.news.skynet.be...
server[vbcol=seagreen]
can
> just log in in Windows as a member of sysadmins; sysadmis are member of
> Builtin\Administrator SQL Server role, which has administrative privileges
> by default...
> the method you previously exposed only prevents user of YOUR application
not
> to access that data, but using ad-hoc query tools like QA, oSql or simply
> Visual Data Manager (VB3 to VB6 <g>) an administrator can perform whatever
> activity...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||A possible solution may be to use dbLockdown. It will encrypt ALL (or
selected) database object scripts for stored procedures, triggers, user
defined functions and views in your MSDE database using the WITH
ENCRYPTION clause. The tool will also automatically archive the script
source so you can restore it again at any time. Check out
http://www.ecatenate.com/dblockdown_product_info.html
Regards, Simon
"Jason" <yo@.yo.com> wrote in message news:<#4bvqLoeEHA.3016@.tk2msftngp13.phx.gbl>...
> Is there a way to secure a database on MSDE so that my users cannot be
> poking around my applications database? I've been reading up on it and see
> that MSDE installs with a mixed security mode.
> Does that mean if I secure my database with sql security someone who is an
> admin on the machine can look at my database regardless of what I set?
> I am concerned that my users (and competition) will try to hack into my
> instance and steal my data & data structures so I'm trying to figure out
> how I can prevent that. I understand I can encrypt my stored procedure at
> least I hope that is still the case with MSDE.
> Any thoughts?
> Thanks.
>
> This is a desktop app and not a web app btw.