Solved

Problem connecting to SQL Server

Posted on 2007-11-21
13
269 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

743 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

13 Experts available now in Live!

Get 1:1 Help Now