Problem connecting to SQL Server

We have two machines (that I know of so far) that since we restarted our server a couple of days ago are giving the following error on trying to establish an ODBC connection to the SQL Server:

"Connection failed:
SQL State: ‘01000’
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen(Connect())
Connection failed:
SQL State: ‘08001’
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied."

Both machines had no problems connecting to the server previously and there are a number of other machines that still happily connect.  No changes have been made to software or hardware on the two machines either.  Any ideas how to fix the problem?
mkalmekAsked:
Who is Participating?
 
nmcdermaidConnect With a Mentor Commented:
can you ping the server from the workstation?

does

TELNET <servername> 1433

show a black screen (assuming the server is running on 1433)
0
 
AustinSevenCommented:
Assuming you can ping the server from the workstation... If you're using Windows Authentication,  try connecting to the server using 'Run As' to start either Query Analyzer (for 2000) or Management Studio (2005) using a valid login/password that definitely works from other workstations.    If you can make the connection using Run As, it should help to narrow the problem down a lot.  

AustinSeven
0
 
mkalmekAuthor Commented:
I can ping the server no problem and it works fine if i click "Start, Run...".  I have even tried logging into the server as the administrator and it still doesn't work.  I use SQL Server verification on all machines with the same login and password so I don't think it is that either.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
AustinSevenCommented:
Ah, you ddn't mention that you can't even log into the server as administrator.    What do you mean 'administrator'?   SA?    Have you tried going to the server's console, starting a Query Analyzer (2000) session and authenticating as 'SA' with the correct password?   I just want to know if you can log in anywhere using SA?  

Forget what I was saying about 'Run As' (not Start, Run anyway).   To test this using SQL Logins, you can start Query Analyzer from any desktop or on the server itself and just specify SA and enter the password.

AustinSeven
0
 
mkalmekAuthor Commented:
No, sorry for being a little vague...I can log into the server perfectly as an administrator (ie when I click Start/Run and \\Server, whether I log in as administrator or any user, I get access to the correct files on the server, but cannot access the SQL server whether I use the sa or any other logon).

I can login anywhere else using sa or any other user name.  Query Analyzer works just fine from the server itself.
0
 
AustinSevenCommented:
Getting closer but still needs clarification...  Suggest we don't refer to authenticating at a Windows level again as, from what I understand, that works in any situation, right?   So, if that's the case (and please tell me if it isn't), we are only concerned with authenticating SQL connections to your SQL Server, ok?

If I am correct to assume that is only a question of authenticating SQL connections using SA (or other SQL Logins), I'm confused because you have said : -

1. ...but cannot access the SQL server whether I use the sa or any other logon (on the server)
and
2. ..Query Analyzer works just fine from the server itself.

The above two statements are contradictory to me at least.   Please let's clear this up.   If you use Query Analyzer to connect to SQL Server on the server (by 'console',  I take it you mean the console or Terminal Services to a virtual console) then you have authenticated a SQL Server connection on the server on the server (2 above).   However, in (1), you say you can authenticate using SA or any other Logon on the server... hence my confusion.   I just need a clear picture of where SQL connections to the server are successful and where they are not.

You can use UDLs to test connection effectively.    Here's how to do it.   On the desktops and on the server: -
1. Open NotePad
2. Save As test.udl (All Types, not .txt) to Desktop (must be Desktop you save it to)
3. Double-click on test.udl from the desktop
4. Specify 'Microsoft OLE DB Provider for SQL Server' on Providers tab
5. Enter server name, SA & password and database on Connection Tab
6. Click the 'TEST' button on the connection tab and see if it is successful.

By elimination, work around the desktops in question as well as the server and test.  

AustinSeven

AustinSeven

0
 
mkalmekAuthor Commented:
I do not have access to the machines giving the problems right now as the users have left for the day.  What I can tell you is that I am unable to set up a new ODBC connection from the machines to the SQL server regardless of whether I use the sa or any other login, which may be a similar process to what you are suggesting above.  
0
 
AustinSevenConnect With a Mentor Commented:
Ok, you should test using the UDL method when the users come back.

On those machines, you can also check to make sure latest service pack is installed in line with other machines plus other system components that might be out-of-line.  If you're using SQL Server 2000,  it might also be worth looking in SQL Server Client Network Utility (Start, Programs, SQL Server, Client Network Utility) to see if there are any aliases in relation to the target server- delete them if there are.

What about connecting from those desktops that are giving the problem to different SQL Servers?  Does that succeed or is the behavior exactly the same?

Investigate SQLPing: http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx
SQLPing is like Ping but will tell you if a SQL Server instance is responding from the offending desktops.  

These are all things to do to in order to narrow down the problem.

AustinSeven
0
 
mkalmekAuthor Commented:
Thanks, will try tomorrow and let you know
0
 
nmcdermaidCommented:
Did you also try TELNET as suggested? The TELNET test removes any login confusion... it just test that the service is accepting connections on that port.
0
 
mkalmekAuthor Commented:
i have no idea how to telnet
0
 
nmcdermaidCommented:
Go to a command prompt. Type

TELNET <servername> 1433

You should get a black screen.

0
 
mkalmekAuthor Commented:
Turns out that all that was missing was a service pack.  Upgraded to SP3a and voila things worked fine.  Not sure what went wrong that stopped these machines working, but they are much happier now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.