MSDE on Windows 2003

I am trying to connect to a MSDE from XP pro SP2, the MSDE is running on Windows 2003 server and the only way I can connect to the MSDE is if I make the network user an Administrator of the Windows 2003 server.
How can I connect to MSDE without making the user an Admin?
Thanks
Peter
hi Peter,
Peter wrote:
> I am trying to connect to a MSDE from XP pro SP2, the MSDE is running
> on Windows 2003 server and the only way I can connect to the MSDE is
> if I make the network user an Administrator of the Windows 2003
> server.
> How can I connect to MSDE without making the user an Admin?
assuming you've no network problem ( :-D ), what kind of authentication are
you using for that?
I guess you are using Windows authentication (trusted connection)... MSDE
installs by default granting local Windows admins membership to the SQL
Server sysadmins role (BUILTIN\Administrator) so that they are "naturally"
granted login to the MSDE instance... if you want your power/limited users
to be granted to, you have to manually create a SQL Server "Windows"
Login(s) or Windows Group login for all your required users...
please have a look at
http://msdn.microsoft.com/library/de...ga-gz_8dri.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||I have added a domain user the the database
EXEC sp_grantlogin 'MyDomain\Peter'
Go
but still user Peter can not connect to the database unless Peter is an
Admin
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3ngpceF1f9poU1@.individual.net...
> hi Peter,
> Peter wrote:
> assuming you've no network problem ( :-D ), what kind of authentication
> are you using for that?
> I guess you are using Windows authentication (trusted connection)... MSDE
> installs by default granting local Windows admins membership to the SQL
> Server sysadmins role (BUILTIN\Administrator) so that they are "naturally"
> granted login to the MSDE instance... if you want your power/limited users
> to be granted to, you have to manually create a SQL Server "Windows"
> Login(s) or Windows Group login for all your required users...
> please have a look at
> http://msdn.microsoft.com/library/de...ga-gz_8dri.asp
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Peter,
Peter wrote:
> I have added a domain user the the database
> EXEC sp_grantlogin 'MyDomain\Peter'
> Go
> but still user Peter can not connect to the database unless Peter is
> an Admin
using and accessing MSDE/SQL Server and it's databases belongs to an
authentication area of the product...
SQL Server uses a sort of 2 phase authentication..
the first one regards login, where someone try to connect to a particular
instance using WinNT (trusted) connections or standard SQL Server
connections (yes, handling both is known as "mixed authentication")
in the second case, standard SQL Server connections, a user id and a
password must be provided, and this information has to be matched against
available SQL Server logins... if a match is found the connecting person has
provided correct information and can log in...
the WinNT (trusted) method does not require this kind of information as the
underlaying OS provides the sid of the against the DC authenticated OS
login, that only need to be matched against a WinNT SQL Server already
granted login or WinNT granted group..
that's to say both kind of authentications need a match in the granted
logins...
Standard SQL Server logins must all be manually added via sp_addlogin system
stored procedure but "sa" "super" login, which is provided by default and
that can not be removed..
as regard WinNT connections, the setup automatically adds a WinNT group
including all WinNT administrators, and this is the reason you can log in
MSDE when logged in Windows as admin...
but all other Windows users/groups have to be manually added via sytem
stored procedure sp_grantlogin
so you have to add you specific WinNT group (say "powerusers") in order to
grant all them permission to log in, or only your specific WinNT account
Domain\Account if you want only him to be granted login...
as regard the second authentication phase, it regards specific access to
each database, and this is done via the sp_grantdbaccess system stored
procedures, that adds an entry in each specific database.dbo.sysusers system
table matching the master.dbo.syslogins system view in the join of the sid
column... this operation generate a database user mapped to a specific
login, allowing him/her to access the desired database with the provided
database role privileges... additional permission can be granted/denied to
each db user, defining the specific security design as required on every db
object and so on, but this is a successive step...
please start reading at
http://msdn.microsoft.com/library/de...ar_da_0n77.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea,
Thanks for all of your help. I ran both cleanup tools. Still did not solve
problem. I would not give up! After many hours of trial and error I
determine the problem was with a corrupted MDAC component. Reinstall MDAC on
XP sp2? Yes there is a way to do that (very simple). Once I reinstalled
MDAC the client application connected and installed just fine. --RS
"Andrea Montanari" wrote:

> hi Peter,
> Peter wrote:
> using and accessing MSDE/SQL Server and it's databases belongs to an
> authentication area of the product...
> SQL Server uses a sort of 2 phase authentication..
> the first one regards login, where someone try to connect to a particular
> instance using WinNT (trusted) connections or standard SQL Server
> connections (yes, handling both is known as "mixed authentication")
> in the second case, standard SQL Server connections, a user id and a
> password must be provided, and this information has to be matched against
> available SQL Server logins... if a match is found the connecting person has
> provided correct information and can log in...
> the WinNT (trusted) method does not require this kind of information as the
> underlaying OS provides the sid of the against the DC authenticated OS
> login, that only need to be matched against a WinNT SQL Server already
> granted login or WinNT granted group..
> that's to say both kind of authentications need a match in the granted
> logins...
> Standard SQL Server logins must all be manually added via sp_addlogin system
> stored procedure but "sa" "super" login, which is provided by default and
> that can not be removed..
> as regard WinNT connections, the setup automatically adds a WinNT group
> including all WinNT administrators, and this is the reason you can log in
> MSDE when logged in Windows as admin...
> but all other Windows users/groups have to be manually added via sytem
> stored procedure sp_grantlogin
> so you have to add you specific WinNT group (say "powerusers") in order to
> grant all them permission to log in, or only your specific WinNT account
> Domain\Account if you want only him to be granted login...
> as regard the second authentication phase, it regards specific access to
> each database, and this is done via the sp_grantdbaccess system stored
> procedures, that adds an entry in each specific database.dbo.sysusers system
> table matching the master.dbo.syslogins system view in the join of the sid
> column... this operation generate a database user mapped to a specific
> login, allowing him/her to access the desired database with the provided
> database role privileges... additional permission can be granted/denied to
> each db user, defining the specific security design as required on every db
> object and so on, but this is a successive step...
> please start reading at
> http://msdn.microsoft.com/library/de...ar_da_0n77.asp
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||Wrong thread, see above... RS
"Andrea Montanari" wrote:

> hi Peter,
> Peter wrote:
> using and accessing MSDE/SQL Server and it's databases belongs to an
> authentication area of the product...
> SQL Server uses a sort of 2 phase authentication..
> the first one regards login, where someone try to connect to a particular
> instance using WinNT (trusted) connections or standard SQL Server
> connections (yes, handling both is known as "mixed authentication")
> in the second case, standard SQL Server connections, a user id and a
> password must be provided, and this information has to be matched against
> available SQL Server logins... if a match is found the connecting person has
> provided correct information and can log in...
> the WinNT (trusted) method does not require this kind of information as the
> underlaying OS provides the sid of the against the DC authenticated OS
> login, that only need to be matched against a WinNT SQL Server already
> granted login or WinNT granted group..
> that's to say both kind of authentications need a match in the granted
> logins...
> Standard SQL Server logins must all be manually added via sp_addlogin system
> stored procedure but "sa" "super" login, which is provided by default and
> that can not be removed..
> as regard WinNT connections, the setup automatically adds a WinNT group
> including all WinNT administrators, and this is the reason you can log in
> MSDE when logged in Windows as admin...
> but all other Windows users/groups have to be manually added via sytem
> stored procedure sp_grantlogin
> so you have to add you specific WinNT group (say "powerusers") in order to
> grant all them permission to log in, or only your specific WinNT account
> Domain\Account if you want only him to be granted login...
> as regard the second authentication phase, it regards specific access to
> each database, and this is done via the sp_grantdbaccess system stored
> procedures, that adds an entry in each specific database.dbo.sysusers system
> table matching the master.dbo.syslogins system view in the join of the sid
> column... this operation generate a database user mapped to a specific
> login, allowing him/her to access the desired database with the provided
> database role privileges... additional permission can be granted/denied to
> each db user, defining the specific security design as required on every db
> object and so on, but this is a successive step...
> please start reading at
> http://msdn.microsoft.com/library/de...ar_da_0n77.asp
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>