MSDE does not work for XP power user

Discussion:

(too old to reply)

Peter Hase

2008-01-19 13:49:59 UTC

Hi,

I have built a MSDE 2000 based application which works fine in in a
LAN environnment with XP clients under power user rights.

Since the customer took the decision to deploy this
we have got the problem, that everything is fine
as the user is running with administation rights. If this
is changed to 'power user' the client does not show any data.
No error will be thrown. Why this and how can I fix it?

Conditions: MSDE 2000 under Window XP, local installation
(notebook), loginmode = sqlserver. Client is a .NET Windows
application.

Thanks in advance.

Peter

Norman Yuan

2008-01-19 16:12:57 UTC

Permalink

It is not whether the user is "power user" or "admin". It is because of SQL
Server security. You must understand how to configure SQL Server/MSDE to
allow different users to access different data in a database of SQL
Server/MSDE. Yes, an local "Admin" user has all access to to the SQL
Server/MSDE, by default. But that does not mean user has to be "admin", and
more importantly, users who only need to access certain data in the SQL
Server/MSDE should only be able to access the data they supposed to be, not
anything else, especially not the permission to altter SQL Server/MSDE's
configuration, database design...

Sure, if your application is simple and the database is simple, you can let
your user always logs in as local admin, so your app will work risking the
user breaks the entire Windows.

So, study a bit more on SQL Server/MSDE security is due.

Basically, you need:

1. Determine which user who logs on this computer would use your
application/access the database in MSDE. Does he need to access data only,
or does he need to configure/change/desgn the MSDE/database

2. Do you want to use MSDE in Windows security mode or SQL Server security
(mixed mode)? By default installation, only Windows security is enabled. If
you want to use SQL Server security (including SQL Server login's
username/password in ConnectionString), you need to explicitly enable mixed
security mode

3. Create one or more SQL Server logins, map the login(s) to corresponding
Windows user account (for Windows security)

4. Make SQL Server login(s) as certain database' user(s), make user certain
role in the database;

5. Asign necessary access permission to the database user/role. For example,
if you do not want some user to edit data (read only), then you can only
give the user "SELECT..." permission, but no "UPDATE..." and "DELETE..."
permission to certain tables/views/SPs in the database.

SQL Server/MSDE is a complicated server software, not something you can do
double-clicking installing and using. You need a lot of knowledge/skill to
set it up properly before using it.

P.S.

In your post, you said "loginmode = sqlserver". If so, whether the user is
admin or power user should not have difference if the ConnectionString
supplied correct SQL Server login's username/password. So, if admin or power
user make difference, then logging in mode is not "sqlserver".

Post by Peter Hase
Hi,
I have built a MSDE 2000 based application which works fine in in a
LAN environnment with XP clients under power user rights.
Since the customer took the decision to deploy this
we have got the problem, that everything is fine
as the user is running with administation rights. If this
is changed to 'power user' the client does not show any data.
No error will be thrown. Why this and how can I fix it?
Conditions: MSDE 2000 under Window XP, local installation
(notebook), loginmode = sqlserver. Client is a .NET Windows
application.
Thanks in advance.
Peter

Kenneth Porter

2008-01-19 23:29:29 UTC

Permalink

Post by Norman Yuan
SQL Server/MSDE is a complicated server software, not something you
can do double-clicking installing and using. You need a lot of
knowledge/skill to set it up properly before using it.

For simple applications, is there a simpler alternative? Should I go with
SQLite?

http://www.sqlite.org/

Norman Yuan

2008-01-20 15:46:28 UTC

Permalink

If you want to avoid the complexity of database server (MSDE/SQL Server
Express), you can consider SQL Server 2005 CE.

Post by Kenneth Porter

For simple applications, is there a simpler alternative? Should I go with
SQLite?
http://www.sqlite.org/

Peter Hase

2008-01-21 10:27:38 UTC

Permalink

Hi,

thanks for your response.

Post by Norman Yuan
SQL Server/MSDE is a complicated server software, not something you can do
double-clicking installing and using. You need a lot of knowledge/skill to
set it up properly before using it.

yes, but maybe I've been a bit misunderstood. In the LAN environment
the security is set to 'SQL Server security' and the default user's
'sa' login is used (as MS has recommended in former times -> btw, data
security is not a main task for this application).
That's what I mean as I wrote "loginmode = sqlserver".
This will be provided by connectionstring data and works as I've
mentioned.
The same(?) configuration does not work on a local machine what looks
weird for me, but I'm not sure wheather it's really identically
because the costumer did this 'deployment' on his own.

Nevertheless you are right, it is a good approach to configure certain
user/roles with corresponding permission sets.

Peter

Norman Yuan

2008-01-21 14:15:59 UTC

Permalink

Post by Peter Hase
Hi,
thanks for your response.

Post by Norman Yuan
SQL Server/MSDE is a complicated server software, not something you can do
double-clicking installing and using. You need a lot of knowledge/skill to
set it up properly before using it.

If your MSDE is SQL Server2000 SP3 or later, "sa" account cannot be used by
default installation (i.e. SQL Server's mixed mode is not enabled). You need
to explicitly enable it during installation or after installation, until
then, the "sa" user name and password cannot be used to connect to SQL
Server MSDE.

You could use Enterprise Manager, SSMS/Express... to check/enable mixed
security mode. Or look into registry to determine if mixed mode is enabled.
However, do not use "sa" for any reason in application level. If you enabled
mixed mode, you can simply and easily create a SQL Server login and give it
whatever user name/password. Using "sa" with application is well known bad
practice, and there is absolutely no reason to use it.

Post by Peter Hase
Nevertheless you are right, it is a good approach to configure certain
user/roles with corresponding permission sets.
Peter

Riccardo Toia

2008-01-31 09:03:06 UTC

Permalink

Hi, i think you shoud try Sql2005 Compact edition.

Post by Norman Yuan

Post by Peter Hase
Hi,
thanks for your response.

Post by Norman Yuan
SQL Server/MSDE is a complicated server software, not something you can do
double-clicking installing and using. You need a lot of knowledge/skill to
set it up properly before using it.

If your MSDE is SQL Server2000 SP3 or later, "sa" account cannot be used
by default installation (i.e. SQL Server's mixed mode is not enabled). You
need to explicitly enable it during installation or after installation,
until then, the "sa" user name and password cannot be used to connect to
SQL Server MSDE.
You could use Enterprise Manager, SSMS/Express... to check/enable mixed
security mode. Or look into registry to determine if mixed mode is
enabled. However, do not use "sa" for any reason in application level. If
you enabled mixed mode, you can simply and easily create a SQL Server
login and give it whatever user name/password. Using "sa" with application
is well known bad practice, and there is absolutely no reason to use it.

Post by Peter Hase
Nevertheless you are right, it is a good approach to configure certain
user/roles with corresponding permission sets.
Peter