Users are reporting that they cannot connect to SQL Server using ODBC and Windows Authentication after I applied a SQL Server 2000 SP4 hotfix. The client-side error is:
Connection failed:
SQLState: '01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied
I installed hotfix for SQL Server 2000 SP4 (sets version to SQL Server 2000 build 8.00.2148 (SP4)); see KB894905. This hotfix was intended to resolve the following issues occurring on the server:
902955 (http://support.microsoft.com/kb/902955/) FIX: You receive a "Getting registry information" message when you run the Sqldiag.exe utility after you install SQL Server 2000 Service Pack 4
895123 (http://support.microsoft.com/kb/895123/) FIX: You may receive error message 701, error message 802, and error message 17803 when many hashed buffers are available in SQL Server 2000
Has anyone else experienced this, found a solution, etc.?
Thanks,
David
The server is SQL Server 2000 SP4 on Windows 2003 SP1. The client PC has MDAC 2.8 SP1 on Windows XP SP 2.
|||double check that windows firewall isn't blocking on both client and server.
on clients run "C:\windows\System32\CliConfg.exe" and make sure that tcp/ip is a configured protocol
on Server, run "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\svrnetcn.exe" and make sure that tcp/ip is configured
|||Thanks for your reply.
Neither client or server is running Windows Firewall. Both are enabled for TCP/IP. I also ran the MDAC Component Checker on both and neither reported mismatches.
|||sanity check. . . is the service running?
what happens when you do this from a client -
telnet servername 1433
does the screen go blank, as if waiting for or input? or does it say - "Connecting to servername" and then time out? or something different?
what happens when you do this on the server -
telnet localhost 1433
|||Yes, the MSSQLSERVER and SQLSERVERAGENT services are both running on the server and I can connect fine using EM from my PC. In fact, I can connect using ODBC from my PC, but a user cannot connect from his PC using the same ODBC setup. An app. server running Windows 2000 SP1 also cannot connect. I've been validating the Windows and MDAC versions and making sure no firewalls are running, etc.
When I run telnet servername 1433 from my PC the window opens and reports "Connecting to servername" and then it disappears. When I run telnet localhost 1433 on the server it does the same thing.
|||wait a second. . .
sitting at the server, from the start menu, click run . . .
you do:
telnet localhost 1433
a command window opens up and you get a message "connecting to localhost"
and then the command window closes?
If so the connection is being refused from the machine/service.
the telnet should go to a blank/black command window as the sql telnet session awaits more commands.
Is it a named instance of sql server? or default instance?
|||This is a default instance and yes, the telnet window disappears.|||when you are sitting at the server and trying the telnet, you are logged in as a machine administator and the telent fails?
using enterprise manager, check the server from your machine and confirm that 'servername\Administrators' have server admin permissions.
double check all your logins and db users
are you running NT Authentication or Mixed mode authentication?
|||Yes, I am logged in as an administrator on the server when I run telnet. 'BUILTIN\Administrators' has been removed from the SQL Server logins, but that should not affect users trying to connect with valid registrations. The instance uses mixed-mode authentication (Windows and SQL Server).|||well, double check your logins and users. . . . no one is denied.
|||This issue has been resolved. It was related to permissions for the SQL Server Service Account. That account needed permissions adjustments, as described in KB283811.
The permissions changes resolved the ODBC TCP/IP connection failures, but the root cause is still a little perplexing. Connections worked fine for many months, then the failure. The MS Support Engineer that I worked with was convinced that the 2 SP4 hotfixes did not cause the issue. He thinks it may have been the result of a change in Group Policy, which I will have to do more research on.
No comments:
Post a Comment