MSDE Login Problem

Hi All;
MSDE has so many complication to install and to use. So, I am facing one
problem right now. It is very frustrating right now. I created one VB.NET
application and used MSDE SP3. I am trying to install on Windows 2000
professional. There is a problem setting the SA password. To install MSDE I
am passing following parameter into
[Options]
INSTANCENAME= <instance name>
DISABLENETWORKPROTOCOLS=1
SAPWD= <password>
First thing, MSDE ignore SECURITYMODE = SQL at the time of installation and
installs in Windows Authentication. I can connect to MSDE in windows
authentication if I am using that connection string to connect to MSDE
Connection String = "(local)\<instance name> ;persist security info=True;
initial catalog= <database name> ;Trusted_Connection=True"
It is working fine if the Windows user is Administrator, if the user is a
domain user and not have Admin rights then it fails to connect.
I changed to MSDE login mode to SQL mode using RegEdit and change the Key to
Login mode = 2 which changes the mode to SQL Authentication. I reset the
service as well. Now, I am using that connection string to connect:
Connection String = "data source=(local)\<instance name>;initial
catalog=<database name> ; user id=sa ; password= <sa password which set at
the time of installation> " />
Now, it is failed to connect using sa login. It always says "Login failed
for user 'sa' "
I tried to connect to the MSDE using
osql -U sa -S <instance name>
then provide Password:
it is not connected that way as well.
I am stuck here, I want to install application at client side which is a
domain network and all the users can't be administrator to use the
application. The summary is I need to connect using SQL Authentication but it
is not setting the SA password properly which causes failing to connect. Is
there any suggestion and solution.
Thanks for your help in advance.
Essa, M. Mughal
Software Developer
Toronto, Canada
hi Essa,
Essa wrote:
> First thing, MSDE ignore SECURITYMODE = SQL at the time of
> installation and installs in Windows Authentication. I can connect to
> MSDE in windows authentication if I am using that connection string
> to connect to MSDE
I was unable to reproduce the problem... providing the SECURITYMODE=SQL
always set the MSDE instance to accept mixed authenticated connections..

> Connection String = "(local)\<instance name> ;persist security
> info=True; initial catalog= <database name> ;Trusted_Connection=True"
> It is working fine if the Windows user is Administrator, if the user
> is a domain user and not have Admin rights then it fails to connect.
and this is normal, as MSDE, at installation time, register local
administrators in the BUILTIN\Administrators WinNT group logins, but other
accounts (power users, limited users, .. ) have to be manually granted
authentication rights via a WinNT login or WinNT group login...

> I changed to MSDE login mode to SQL mode using RegEdit and change the
> Key to Login mode = 2 which changes the mode to SQL Authentication. I
> reset the service as well. Now, I am using that connection string to
> connect:
> Connection String = "data source=(local)\<instance name>;initial
> catalog=<database name> ; user id=sa ; password= <sa password which
> set at the time of installation> " />
> Now, it is failed to connect using sa login. It always says "Login
> failed for user 'sa' "
>
if the remaining part of the error is " ... not associated with a trusted
connection" then the server did not (still) accept the registry change, and
you have to stop and re-start it..
http://support.microsoft.com/default...;EN-US;q285097
else the provided password does not match the registered one... please
verify it is correct...
you can modify it accessing MSDE via a local windows admin in integrated
security mode and executing the
EXEC sp_password @.new = 'your_new_pwd_for_SA' , @.loginame = 'sa'
http://support.microsoft.com/default...;EN-US;q325003

> I am stuck here, I want to install application at client side which
> is a domain network and all the users can't be administrator to use
> the application. The summary is I need to connect using SQL
> Authentication but it is not setting the SA password properly which
> causes failing to connect. Is there any suggestion and solution.
in all my experiences,
C:\...\setup.exe SAPWD=your_pwd DISABLENETWORKPROTOCOLS=0 SECURITYMODE=SQL
INSTANCENAME=YourInstanceName /L*v "c:\msdeLog.txt"
command line alwyas installed a named instance, with mixed mode
authentication, enabled network protocols, logging the installation process
to file
your setting of DISABLENETWORKPROTOCOLS=1 disables network protocols and no
remote connection will be granted (but this is another story)...
apart from this, but this is another entire story, you should not connect to
SQL Server with administrative privileges (sa login) if you really do not
need to... you should use normal privileged logins and database users, as
administrative permissions are not tailored for standard DML access, and
each user should log in with his/her own login, trusted or standard SQL
server authenticated...
please have an additional look at
http://www.sql-server-performance.co...l_security.asp
http://www.microsoft.com/technet/pro.../sp3sec03.mspx
or, at least, an application role,
http://www.databasejournal.com/featu...le.php/3363521 ...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply