Does MSDE fit my need?

I am creating an application that need store data locally (the new data
are downloaded from my webserver daily). Here is what I want:
1. Secure - Only my application can read the database. I don't want the
user be able to look at the data using other tools, or be able to
export the data for other purpose.
2. Handle large amount of data and be very fast.
3. How MSDE is distributed?
Thanks
John
hi,
Q. John Chen wrote:
> I am creating an application that need store data locally (the new
> data are downloaded from my webserver daily). Here is what I want:
> 1. Secure - Only my application can read the database. I don't want
> the user be able to look at the data using other tools, or be able to
> export the data for other purpose.
>
SQL Server/MSDE is secure as long as you provide an accurate login logic...
SQL Server uses a so called "2 phase" authentication policy:
first an SQL Server Login or a Windows login must be created of granted
access to the SQL Server instance... at the server level a login can be made
member of none, 1 or all of the fixed server roles, which include "sysadmin"
role and so on...
the second authentication phase is at database level, where each login will
be granted database access mapping to a database user... here access
permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so on)
privileges at an object level (or column level for tables and views)..
the mapping is performed in the JOIN database..sysusers.sid =
master..syslogins.sid , so the only link is the provided Login's sid, it's
Security IDentification number
so, the second phase regards a database security implementation... in order
to access a specified database the simple login existance does not provide
database access, but a (database) user must be mapped to the corresponding
login.. and is about verifying that at each object level (including
database, tables, views, columns, procedures and so on) the Login/User
association is permitted access to... please go on reading at
http://msdn.microsoft.com/library/de...urity_05bt.asp ,
http://msdn.microsoft.com/library/de...ar_da_0n77.asp
and following chapters..
but, back to the first phase, you can choose between 2 authentication modes:
WinNT (trusted) connections or SQL Server authenticated connections... the
latter always requires full user's credential such as "User
Id=sa;Password=pwd", the password can be NULL so it must not be specified,
but I strongly advise you always to ensure strong passwords are present...
WindowsNT authentication, on the contrary, does not requires user's
credential becouse it's directly provided by Windows via the logins'ID
(sid), which authenticate user's login at the windows login step... SQL
Server only needs to verify that the corresponding login and/or group is
granted to log on the instance...
Microsoft recommends to use the Windows NT (trusted) model as it grants more
and reliable security patterns
you can start reading about authentication modes at
other articles worth reading can be found at
http://www.sql-server-performaXnce.c...l_security.asp
http://www.microsoft.com/technet/pro.../sp3sec03.mspx
http://www.microsoft.com/technet/pro.../sp3sec00.mspx

> 2. Handle large amount of data and be very fast.
about large amount of data, MSDE is limited to 2gb data file per database..

> 3. How MSDE is distributed?
I do not understand this question... if it's about legal permissions, MSDE
is free to download and use, where you have to register (for free) at
http://www.microsoft.com/sql/msde/ho...stregister.asp for
redistribution rights...
frmo a technical point of view, it isa provided as a package including a
boostrap installer based on Windows Installer technology, to be run from a
command line prompt in order to provide all the required parameters
http://msdn.microsoft.com/library/de...stsql_84xl.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||About security the OP asked. His intention is not allowing user to see his
database design and data. On this regard, login logic only guard very
innocent users. For any reasonably knowledgable network/computer
administrator, who allows MSDE being installed or who does his own MSDE
installation (thanks to MSDE, many non-database administrators know how to
do it now), can install/uninstall, attach/detach your *.mdf , then be able
to see the database, unless some sort of encryption is applied. After all,
you cannot prevent an Administrator to install/uninstall MSDE. Say, your app
installs MSDE with SQL Security only with a long SA password. The user can
easily enable Win Security by going to Registry, or simply uninstall the
MSDE (note, user database *.mdf does not get erased during uninstallation)
and re-install it with Windows security. And he can create whatever login
and give the login whatever role he wants and then attach your database and
open it.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3drhk9F6n6b9nU1@.individual.net...
> hi,
> Q. John Chen wrote:
> SQL Server/MSDE is secure as long as you provide an accurate login
logic...
> SQL Server uses a so called "2 phase" authentication policy:
> first an SQL Server Login or a Windows login must be created of granted
> access to the SQL Server instance... at the server level a login can be
made
> member of none, 1 or all of the fixed server roles, which include
"sysadmin"
> role and so on...
>
> the second authentication phase is at database level, where each login
will
> be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
on)
> privileges at an object level (or column level for tables and views)..
> the mapping is performed in the JOIN database..sysusers.sid =
> master..syslogins.sid , so the only link is the provided Login's sid, it's
> Security IDentification number
> so, the second phase regards a database security implementation... in
order
> to access a specified database the simple login existance does not provide
> database access, but a (database) user must be mapped to the corresponding
> login.. and is about verifying that at each object level (including
> database, tables, views, columns, procedures and so on) the Login/User
> association is permitted access to... please go on reading at
>
http://msdn.microsoft.com/library/de...urity_05bt.asp ,
>
http://msdn.microsoft.com/library/de...ar_da_0n77.asp
> and following chapters..
>
> but, back to the first phase, you can choose between 2 authentication
modes:
> WinNT (trusted) connections or SQL Server authenticated connections... the
> latter always requires full user's credential such as "User
> Id=sa;Password=pwd", the password can be NULL so it must not be specified,
> but I strongly advise you always to ensure strong passwords are
present...
> WindowsNT authentication, on the contrary, does not requires user's
> credential becouse it's directly provided by Windows via the logins'ID
> (sid), which authenticate user's login at the windows login step... SQL
> Server only needs to verify that the corresponding login and/or group is
> granted to log on the instance...
>
> Microsoft recommends to use the Windows NT (trusted) model as it grants
more
> and reliable security patterns
> you can start reading about authentication modes at
>
> other articles worth reading can be found at
> http://www.sql-server-performaXnce.c...l_security.asp
>
http://www.microsoft.com/technet/pro.../sp3sec03.mspx
>
http://www.microsoft.com/technet/pro.../sp3sec00.mspx
>
> about large amount of data, MSDE is limited to 2gb data file per
database..
>
> I do not understand this question... if it's about legal permissions, MSDE
> is free to download and use, where you have to register (for free) at
> http://www.microsoft.com/sql/msde/ho...stregister.asp for
> redistribution rights...
> frmo a technical point of view, it isa provided as a package including a
> boostrap installer based on Windows Installer technology, to be run from a
> command line prompt in order to provide all the required parameters
>
http://msdn.microsoft.com/library/de...stsql_84xl.asp
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||It seems that I can not use MSDE for my application thanks to you
answer. (unless I can find a way to remove your post :-))
Any recommendation on a local database that provide the security I
wanted. (not a good place to ask for an alternative here though).
Thanks
John
|||hi Norman,
I do not understand if you are just claiming for security...
Norman Yuan wrote:
> About security the OP asked. His intention is not allowing user to
> see his
> database design and data.
and of course you have to manage your WinNT users/groups accordingly to your
security needs and policy.. never give permissions your user is not
interested/accorded with..
and again, of course, we are dealing with SQL Server security and not OS
security, you should already know and manage accordingly to your needs

> On this regard, login logic only guard very
> innocent users. For any reasonably knowledgable network/computer
> administrator, who allows MSDE being installed or who does his own
> MSDE
> installation (thanks to MSDE, many non-database administrators know
> how to
> do it now), can install/uninstall, attach/detach your *.mdf , then be
> able
> to see the database, unless some sort of encryption is applied. After
> all,
> you cannot prevent an Administrator to install/uninstall MSDE.
do not understand this point... you can actually prevent your administrators
from logging in SQL Server... but you can not prevent them to uninstall SQL
Server... that's ok... but, what kind of employee do you have in your
organization? usually, if you can not trust your (fews) administrator, I
really think you have to fire them... the very same applys to SQL Server
(not os) administrator(s)..

>Say,
> your app
> installs MSDE with SQL Security only with a long SA password. The
> user can
> easily enable Win Security by going to Registry,
not the user, the local administrator...

>or simply uninstall the
> MSDE (note, user database *.mdf does not get erased during
> uninstallation)
> and re-install it with Windows security. And he can create whatever
> login
> and give the login whatever role he wants and then attach your
> database and
> open it.
again... I think you should fire your employees :D
as a local admin can do whatever operation he likes to do, you can not
prevent him to stop the server and trash your data.. he can perhaps even
eventually log on SQL Server, if you did not remove the
BUILTIN\Administrator login group (as I usually do) from the MSDE istance,
and of course, as part of the sysamin server role, even access the company
database and increase his salary by 20%...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||The original OP are concerned about protecting his software, including the
database design and data the software used. He does not want the software
user, be it individual or organization, to peek into his software logic by
openning database on MSDE. So my point is the software user has the power to
open a SQL Server database installed on his computer unless some encryption
is implemented on the database. It has nothing to do with how the employee
is behaves and is regulated.
Actually, from the point of view of pretecting software, lot of your unique
business logic are reflected on the database design. And when using MSDE in
your application, we are educated to use as much stroed procedures as
possible, meaning more business logic in the DB. Obviously, there is need to
protect them (I know and you know there are tools for encypting SPs). In
most cases of SQL Server being used in a organization, it is most likely
that app used there are custom-developed, so protecting software wouldn't be
a problem. But when you are developing a stand-alone app for sale, with MSDE
integrided, concerns on user peeking into the DB is understandable.
I developed a windows app package a couple of years ago and used MSDE with
tons of SPs in it. It was aimed to small business in certain business. There
is nothing to prevent them to find a knowledgable guy to get into the
database and uses those Tables/SPs and develop there new UI app, although
they did not do that. Since MSDE is a powerful data engine and very easy to
be integrited into your app, protecting software resulted in by this should
be a concern. How to safyly regulating SQL Server/MSDE in a organization is
not my topic here.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3dsa5rF6tlqnqU1@.individual.net...
> hi Norman,
> I do not understand if you are just claiming for security...
> Norman Yuan wrote:
> and of course you have to manage your WinNT users/groups accordingly to
your
> security needs and policy.. never give permissions your user is not
> interested/accorded with..
> and again, of course, we are dealing with SQL Server security and not OS
> security, you should already know and manage accordingly to your needs
>
> do not understand this point... you can actually prevent your
administrators
> from logging in SQL Server... but you can not prevent them to uninstall
SQL
> Server... that's ok... but, what kind of employee do you have in your
> organization? usually, if you can not trust your (fews) administrator, I
> really think you have to fire them... the very same applys to SQL Server
> (not os) administrator(s)..
>
> not the user, the local administrator...
>
> again... I think you should fire your employees :D
> as a local admin can do whatever operation he likes to do, you can not
> prevent him to stop the server and trash your data.. he can perhaps even
> eventually log on SQL Server, if you did not remove the
> BUILTIN\Administrator login group (as I usually do) from the MSDE istance,
> and of course, as part of the sysamin server role, even access the company
> database and increase his salary by 20%...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Norman,
You read my mind. The software I developed is an application analyzing
commodity trading data. The user is not of a single organization but in
different organization or individuals all over the world. What we are
selling is the data not the software ifself. So protect the data is the
first priority.
Still, I want thank Andrea for giving me all the information about
MSDE.
Again, thanks both.
John
|||Q. John Chen wrote:
> Norman,
> You read my mind. The software I developed is an application analyzing
> commodity trading data. The user is not of a single organization but in
> different organization or individuals all over the world. What we are
> selling is the data not the software ifself. So protect the data is the
> first priority.
> Still, I want thank Andrea for giving me all the information about
> MSDE.
> Again, thanks both.
> John
>
Hi,
My company sells data and ships an MSDE application. What we ended up
doing was coding in application encryption logic. Numbers are not
encrypted but databae column with anything in text (like say
COMPANYNAME) was encrypted.
We can't stop the end-users looking at the database through Access, but
what they do see is a garbled mess !
|||Encrypt the data before storing it in the database, and decrypt it on the
application side when you read it. Of course this will affect overall
performance, but if security is your primary concern, performance is going
to take a hit no matter what.
"Q. John Chen" <qjchen@.email.com> wrote in message
news:1115221772.287953.148830@.o13g2000cwo.googlegr oups.com...
> It seems that I can not use MSDE for my application thanks to you
> answer. (unless I can find a way to remove your post :-))
> Any recommendation on a local database that provide the security I
> wanted. (not a good place to ask for an alternative here though).
> Thanks
> John
>