I assume that you mean how to change the server authentication mode, and
not the Query Analyzer connection (as the previous posters described).

Before you do any of these, take a note of this MSKB article:
274773: If You Change Windows Security to Windows/SQL Security the SA
Password is Blank
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q274773&ID=KB;EN
-US;q274773
The article says this was "fixed" in SP1, but since it is likely the SA
never had a password, I don't know how SP1 could put one in. I would
recommend that you follow the workaround procedure to put a password in
before you change the authentication mode.

The authentication mode is stored in the registry. You can change the
value, but you will have to restart SQL Server service instance to have
it take effect.

If you have access to regedit on the server (either locally or
remotely), look for HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer key,
then LoginMode value, and change it to 0 (zero). If you want to change
it back to Windows-only authentication, the value is 1.

If you can connect with QA, run this query:
exec master..xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', 'LoginMode',
N'REG_DWORD', 0

If you can connect with Enterprise Manager, you can change it from the
Properties dialog for the server.

If you don't have access to any of these methods, then I am afraid you
cannot change the authentication mode.


======================
Mark Harr, MCSD, CPIM
Microsoft Solution Architect
Foresight Technology Group, A Berbee Company
EMail: mark.[Email address protected]


> -----Original Message-----
> From: Femi George [ MTN - UBA ]
> [mailto:mssqldba-ezmlmshield-x99688481.[Email address protected]
> Sent: Tuesday, November 30, 2004 10:32 AM
> To: LazyDBA Discussion
> Subject: Changing authentication mode via query analyzer
>
>
> Hello,
> How can i change the authentication mode via Query Analyzer?.
> right now it is set to windows authentication and i want it
> to be sql server
> and windows authentication .
> i use sql server 2000 and i could only connect via my windows
> login. i am an
> administrator on my PC.
> When i try and expland my server in enterprise manager, i get
> the following
> error;
>
> Login failed for user 'sa'. Reason: Not associated with a
> trusted SQL Server
> connection.
>
> Thanks
> Femi George
> Enterprise Data Warehousing [MTN-AGIP]
> [Email address protected]
> 08032090305
>
>
>
> NOTE: This e-mail message is subject to the MTN Nigeria disclaimer see
> http://www.mtnonline.com/contact/disclaimer.asp
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
> For additional commands, e-mail: mssqldba-[Email address protected]
>
>

MS Sql Server LazyDBA home page