| Groups | Blog | Home |
sql server msde : 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,
[quoted text, click to view] Q. John Chen wrote: SQL Server/MSDE is secure as long as you provide an accurate login logic...> 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 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/default.asp?url=/library/en-us/adminsql/ad_security_05bt.asp , http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_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-performance.com/vk_sql_security.asp http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec03.mspx http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec00.mspx [quoted text, click to view] > 2. Handle large amount of data and be very fast. about large amount of data, MSDE is limited to 2gb data file per database.. [quoted text, click to view] > 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/howtobuy/redistregister.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/default.asp?url=/library/en-us/distsql/distsql_84xl.asp -- Andrea Montanari (Microsoft MVP - SQL Server) http://www.asql.biz/DbaMgr.shtm http://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
hi Norman,
I do not understand if you are just claiming for security... [quoted text, click to view] 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 [quoted text, click to view] > 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).. [quoted text, click to view] >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... [quoted text, click to view] >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.shtm http://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. [quoted text, click to view] "Andrea Montanari" <andrea.sqlDMO@virgilio.it> wrote in message news:3drhk9F6n6b9nU1@individual.net... > 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/default.asp?url=/library/en-us/adminsql/ad_security_05bt.asp , > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_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-performance.com/vk_sql_security.asp > http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec03.mspx > http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/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/howtobuy/redistregister.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/default.asp?url=/library/en-us/distsql/distsql_84xl.asp > -- > Andrea Montanari (Microsoft MVP - SQL Server) > http://www.asql.biz/DbaMgr.shtm http://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
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. [quoted text, click to view] "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: > > 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.shtm http://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
[quoted text, click to view]
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
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. [quoted text, click to view] "Q. John Chen" <qjchen@email.com> wrote in message news:1115221772.287953.148830@o13g2000cwo.googlegroups.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 >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |