Solved

Problem connecting to SQL Server

Posted on 2007-11-21
13
275 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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
 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Slow Connectivity over ODBC 8 32
query execution hang 5 28
SQL Server 2012 r2 - Varible Table 3 23
optimize stored procedure 6 23
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

786 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