Can't connect via TCP/IP + NT Auth after hotfix install

Can't connect via TCP/IP + NT Auth after hotfix install

Post by Jasper Smit » Thu, 18 Jul 2002 12:30:49



After applying the Cummulative Hotfix installation
(Q316333) clients can no longer connect remotely via TCP/IP

SQL2000 SP2 on Windows 2000 SP2

SQL Service runs under a domain account that is a member
of the local administrators group on the server.
Connection via TCP/IP results in the following error

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

However if I change the startup account of the SQL Service
to Local System, connections authenticate just fine. Also
if I set up a named pipes alias for the server I can
connect fine. So it seems to be that when the SQl Service
is running under a domain account and a client tries to
connect via TCP/IP and NT Authentication it fails. From a
Terminal Services session on the server itself I am able
to log in via NT authentication because its using the
local pipe/shared memory so this problem seems to be
limited to TCP/IP connections trying to use NT
Authentication , SQL Authentication is fine.

Anyone shed some light on this - I am thinking of
reinstalling MDAC as a first step. I have actually backed
out of the patch but I can find no instructions for
undoing the registry changes made by servpriv.exe which I
suspect is part of the problem. Other servers I have
applied this patch to are all fine - no problems

Cheers
Jasper Smith

 
 
 

Can't connect via TCP/IP + NT Auth after hotfix install

Post by Jasper Smit » Thu, 18 Jul 2002 16:49:35


Some follow up :

It seems that the hotfix has somehow altered the account
that SQL Service was running under - or at least locally
on the server as this account is used to run all our SQL
Servers, all of which are fine except this one. Having
changed the startup account to an alternate account, users
are able to connect via TCP/IP with NT Authentication. I
still believe this has something to do with servpriv.exe -
are there any details on what keys this alters the
permissions on as the readme.txt and KB article were no
use.

Cheers
Jasper Smith

Quote:>-----Original Message-----
>After applying the Cummulative Hotfix installation
>(Q316333) clients can no longer connect remotely via
TCP/IP

>SQL2000 SP2 on Windows 2000 SP2

>SQL Service runs under a domain account that is a member
>of the local administrators group on the server.
>Connection via TCP/IP results in the following error

>Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

>However if I change the startup account of the SQL
Service
>to Local System, connections authenticate just fine. Also
>if I set up a named pipes alias for the server I can
>connect fine. So it seems to be that when the SQl Service
>is running under a domain account and a client tries to
>connect via TCP/IP and NT Authentication it fails. From a
>Terminal Services session on the server itself I am able
>to log in via NT authentication because its using the
>local pipe/shared memory so this problem seems to be
>limited to TCP/IP connections trying to use NT
>Authentication , SQL Authentication is fine.

>Anyone shed some light on this - I am thinking of
>reinstalling MDAC as a first step. I have actually backed
>out of the patch but I can find no instructions for
>undoing the registry changes made by servpriv.exe which I
>suspect is part of the problem. Other servers I have
>applied this patch to are all fine - no problems

>Cheers
>Jasper Smith
>.


 
 
 

Can't connect via TCP/IP + NT Auth after hotfix install

Post by Neil Pik » Thu, 18 Jul 2002 20:21:22


 Jasper - your best bet is to run regmon (www.sysinternals.com) and check what
servpriv.exe does with that.

Quote:> It seems that the hotfix has somehow altered the account
> that SQL Service was running under - or at least locally
> on the server as this account is used to run all our SQL
> Servers, all of which are fine except this one. Having
> changed the startup account to an alternate account, users
> are able to connect via TCP/IP with NT Authentication. I
> still believe this has something to do with servpriv.exe -
> are there any details on what keys this alters the
> permissions on as the readme.txt and KB article were no
> use.

> Cheers
> Jasper Smith

> >-----Original Message-----
> >After applying the Cummulative Hotfix installation
> >(Q316333) clients can no longer connect remotely via
> TCP/IP

> >SQL2000 SP2 on Windows 2000 SP2

> >SQL Service runs under a domain account that is a member
> >of the local administrators group on the server.
> >Connection via TCP/IP results in the following error

> >Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

> >However if I change the startup account of the SQL
> Service
> >to Local System, connections authenticate just fine. Also
> >if I set up a named pipes alias for the server I can
> >connect fine. So it seems to be that when the SQl Service
> >is running under a domain account and a client tries to
> >connect via TCP/IP and NT Authentication it fails. From a
> >Terminal Services session on the server itself I am able
> >to log in via NT authentication because its using the
> >local pipe/shared memory so this problem seems to be
> >limited to TCP/IP connections trying to use NT
> >Authentication , SQL Authentication is fine.

> >Anyone shed some light on this - I am thinking of
> >reinstalling MDAC as a first step. I have actually backed
> >out of the patch but I can find no instructions for
> >undoing the registry changes made by servpriv.exe which I
> >suspect is part of the problem. Other servers I have
> >applied this patch to are all fine - no problems

> >Cheers
> >Jasper Smith
> >.

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 Reply here - no email
 SQL FAQ (484 entries) see
 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq
 
 
 

Can't connect via TCP/IP + NT Auth after hotfix install

Post by Jasper Smit » Fri, 19 Jul 2002 14:04:09


As far as I can tell it simply removes the startup account
from having explicit permissions (although it still has
access by vitue of being a member of the local
administrators group on the server) on

HKLM\System\CurrentControlSet\Services\MSSQLSERVER

and its sub keys. Adding it back in explicitly had no
effect. Running a registry rebuild had no effect. I'm out
of ideas.

Cheers
Jasper Smith

Quote:>-----Original Message-----
> Jasper - your best bet is to run regmon

(www.sysinternals.com) and check what
Quote:>servpriv.exe does with that.

>> It seems that the hotfix has somehow altered the
account
>> that SQL Service was running under - or at least
locally
>> on the server as this account is used to run all our
SQL
>> Servers, all of which are fine except this one. Having
>> changed the startup account to an alternate account,
users
>> are able to connect via TCP/IP with NT Authentication.
I
>> still believe this has something to do with
servpriv.exe -
>> are there any details on what keys this alters the
>> permissions on as the readme.txt and KB article were no
>> use.

>> Cheers
>> Jasper Smith

>> >-----Original Message-----
>> >After applying the Cummulative Hotfix installation
>> >(Q316333) clients can no longer connect remotely via
>> TCP/IP

>> >SQL2000 SP2 on Windows 2000 SP2

>> >SQL Service runs under a domain account that is a
member
>> >of the local administrators group on the server.
>> >Connection via TCP/IP results in the following error

>> >Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

>> >However if I change the startup account of the SQL
>> Service
>> >to Local System, connections authenticate just fine.
Also
>> >if I set up a named pipes alias for the server I can
>> >connect fine. So it seems to be that when the SQl
Service
>> >is running under a domain account and a client tries
to
>> >connect via TCP/IP and NT Authentication it fails.
From a
>> >Terminal Services session on the server itself I am
able
>> >to log in via NT authentication because its using the
>> >local pipe/shared memory so this problem seems to be
>> >limited to TCP/IP connections trying to use NT
>> >Authentication , SQL Authentication is fine.

>> >Anyone shed some light on this - I am thinking of
>> >reinstalling MDAC as a first step. I have actually
backed
>> >out of the patch but I can find no instructions for
>> >undoing the registry changes made by servpriv.exe
which I
>> >suspect is part of the problem. Other servers I have
>> >applied this patch to are all fine - no problems

>> >Cheers
>> >Jasper Smith
>> >.

> Neil Pike MVP/MCSE.  Protech Computing Ltd
> Reply here - no email
> SQL FAQ (484 entries) see
> http://forumsb.compuserve.com/gvforums/UK/default.asp?
SRV=MSDevApps
> (faqxxx.zip in lib 7)
> or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> or www.sqlserverfaq.com
> or www.mssqlserver.com/faq

>.

 
 
 

Can't connect via TCP/IP + NT Auth after hotfix install

Post by Neil Pik » Fri, 19 Jul 2002 19:43:02


Jasper - I'd do a network trace when the users connect and see if that shows
anything.

Also a regmon trace of sqlservr.exe when they try and connect

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 Reply here - no email
 SQL FAQ (484 entries) see
 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq

 
 
 

Can't connect via TCP/IP + NT Auth after hotfix install

Post by Shirley Kelly [M » Tue, 06 Aug 2002 16:17:29


Hello Jasper,

Please verify that the SQL Server is still listening on TCP/IP.  To do this, please check the SQL errorlog and look for a line similar to this:
     SQL server listening on TCP, Shared Memory, Named Pipes.

This line will be near the beginning of the log.  

If there are no errors in the SQL errorlog, please verify that you can ping the server by name and IP address.  This may not be related to the
hotfixes you installed, and could be an Active Directory or DNS registration issue.

If you are unable to determine what's wrong, please open a case with Microsoft support to work through this issue.

Regards,
Shirley Kelly, MCSE, MCDBA
SQL Server Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure?  For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

 
 
 

1. PRB: Connect to SQL on NT 3.5 from WFW 3.11 via TCP/IP sockets

I am writing a program using VB 4.0 in Windows 3.11 for Workgroups.
It uses ODBC call to connect to SQL server on Windows NT 3.5 via
TCP/IP sockets. But I am getting the following error:

[Microsoft][ODBC SQL Server Driver]TDS buffer length too large

This error only occurs when the program is run on a machine with
modem and PPP connection.  It doesn't occur on my machine with
direct network connection.

I have checked Microsoft knowledge base.  There are some articles
about "TDS buffer length too large".  But the platforms are a little
different--they are connected via named pipes.  So the answers do
not help me much.

Could any experts kindly give me some hints?  I would appreciate
them very much!

Lynn Mei

2. Where can I train my SQL knowledge?

3. Can't connect via TCP/IP with PHP

4. (Newark, NJ) FM Bootcamp - Anyone going?

5. NT auth works, SQL Server auth doesn't

6. Question : Can I use jdk1.2 for Oracle 8i

7. Connecting to SQL Server 7 from SQL S 6.5 via TCP/IP

8. Best and Worst Development Practices -- Training in NYC and DC

9. Getting VB6 to connect to SQL Server 6.5 via TCP/IP

10. Connecting via TCP/IP: Configuration and Security

11. Connecting to server via TCP/IP

12. Help Connecting via TCP/IP ?

13. Connect to SQL Server 6.5 via TCP/IP