Authentication Connection problem XP

[stull]

Hi our sql server 2000 recently had sp3 installed then it rebooted over the night then none of
our Win2000/xp boxes using query analyser could connect to the server unless we used the sa account
then we changed all the pro boxes to connect thru named
pipes and disabled and re-enabled the windows group on the sql
server and that worked.... but any xp box with query anayliser
still can not connect gets the same error win 200 pro
boxes use to get 'login fails for user null not
associated with a trusted sql server connection'

I believe the reason we have to use named pipes now is because of the NTLMssp authentication
which relies on tcp
but I dont know what I have to change on the Xp boxes to get them connected any ideas please
as I am stumped

[MAK]

In the server

click on-Start-run-regedit-click ok

scroll to
Hkey_Local_Machine
Software
Microsoft
MSSQLServer
MSSQLServer
double Click on Login Mode
change the value to 2

then restart the sql server service or recycle the machine. you should be all set.

[MAK]

if you still cannot connect then see this link, another guy had a same problem like you.

http://forums.databasejournal.com/sh...threadid=32289

[stull]

Hi Mak

I was told you were the man to contact so its good to hear from you.
I was wondering the reg hack MSSQLServer
double Click on Login Mode
change the value to 2

what that will that do as I will try it tomorrow at work but I will have to explain to colleagues what that fix is susspose to do.

Thanks Mak

Shaun

[MAK]

Thanks for flattering me. Now I am a walking festival.

1. The registry hacking changes the authentication to mixed mode so you can connect to sql server box with NT login and SQL login.

2. The second link I gave is a detailed trouble shooting of similar issue. Make sure the IP of the sql server machine and the IP you are trying to connect are same.

3. One more reason to your problem might be the port number. see the port opened for sql server on the server.

(server)
click on-Start-run-regedt32-click ok

scroll to
Hkey_Local_Machine
Software
Microsoft
MSSQLServer
MSSQLServer
Supersocketnetlib
TCP
TCPPort

(Client)
click on-Start-run-regedt32-click ok

scroll to
Hkey_Local_Machine
Software
Microsoft
MSSQLServer
Client
Supersocketnetlib
TCP
Defaultport

[stull]

Hi Mak
I tried all those suggestions but I now think it is the windows security authentication that the service pack messed up or upgraded to fort knox standard .
To get the Xp users connected we used tcp created a normal sql account on the server as there windows group account would not work thru named pipes or tcp... so XP I think is authenticating at higher level than Win2000 pro.Win 2000 pro can only connect with a windows group account by using named pipes . We also had to change users Internet explorer in tools\internet options\advanced\ security and make sure enabled intergrated windows authentication was ticked so clients could connect to our online DB
What do you think Mak ?

Shaun

[MAK]

http://www.microsoft.com/sql/evaluat...00/default.asp

http://www.devhood.com/messages/mess...hread_id=92975

http://www.sqlmag.com/Forums/message...&threadid=8824