Solved

Problem connecting to SQL Server

Posted on 2007-11-21
13
272 Views
Last Modified: 2010-07-27
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?
0
Comment
Question by:mkalmek
  • 6
  • 4
  • 3
13 Comments
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 250 total points
ID: 20326865
can you ping the server from the workstation?

does

TELNET <servername> 1433

show a black screen (assuming the server is running on 1433)
0
 
LVL 10

Expert Comment

by:AustinSeven
ID: 20327384
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
 

Author Comment

by:mkalmek
ID: 20327584
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
 
LVL 10

Expert Comment

by:AustinSeven
ID: 20327618
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
 

Author Comment

by:mkalmek
ID: 20327759
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
 
LVL 10

Expert Comment

by:AustinSeven
ID: 20328001
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:mkalmek
ID: 20328071
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
 
LVL 10

Assisted Solution

by:AustinSeven
AustinSeven earned 250 total points
ID: 20328215
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
 

Author Comment

by:mkalmek
ID: 20328569
Thanks, will try tomorrow and let you know
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 20331539
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
 

Author Comment

by:mkalmek
ID: 20331589
i have no idea how to telnet
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 20331635
Go to a command prompt. Type

TELNET <servername> 1433

You should get a black screen.

0
 

Author Closing Comment

by:mkalmek
ID: 31410337
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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now