Diagnosing Connection to SQL Server

问题:

I'm trying to create an ODBC connection to SQL Server, but when i do, i get error:

Connection failed:
SQLState: '01000'
SQL Server Error: 10060
[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.

Here's what I've tried:

1. not a firewall issue: tried with firewall on SQL Server turned off, and client turned off. Also able to telnet 1433 from client and that works fine.
2. not an access issue: I can login from many different computers, including the SQL Server itself using the account i'm using on client (using SQL Server authentication)
3. i can ping the hostname and IP address. (i've tried both)

The only thing i can think of is that the client computer is windows server 2003, and has various roles setup:

• File Server
• Application Server
• Terminal Server
• Domain Controller
• DNS Server

I don't know why these services are setup, but for the time being i can't shut them off. Would it be any of these, and if so, is there a way to disable any of the aspects of them, that might be blocking SQL.

Any advise truly appreciated!

回答1:

Found the issue. For some reason, even though SQL was not in use on the client, it was installed, and by going to All Programs -> Microsoft SQL Server -> Client Network Utility, i found that the protocal TCP/IP was using port 4717. I have no idea why that port was in use, and even further, i'm not sure why it would even matter - I was just setting up an odbc connection, and not using sql server (on the client) at all. Either way, I changed this to the typical port (1433), and like boom, ODBC connection works like charm!

回答2:

Is it possible that the server in question has not been configured to use named pipes?

CAUSE
The most common reason that this connection attempt failed is that this DSN or ODBC data source attempted to make a connection using the TCP/IP sockets Net-Library, which is Dbmssocn.dll. Because the SQL Server is not listening for incoming connections for TCP/IP sockets clients, the connection fails. From ISQL/w, the second error message stated above would be returned. The OS Error is 10061, and the function call that failed was ConnectionOpen(connect()).

WORKAROUND
By default, SQL Server will listen for incoming connections made by Named Pipes clients. Named Pipes is the default IPC mechanism for clients to connect to a SQL Server version 4.2, 6.0, and 6.5 server. The Named Pipes DLL is Dbnmpntw.dll, and it should be located in the Windows\System or Winnt\System32 directory. The TCP/IP sockets netlib DLL is Dbmssocn.dll, and also should be in the Windows\System or Winnt\System32 directory.The most common resolution to this problem is to configure the client computer to connect using Named Pipes. Configuring Named Pipes If the Microsoft Data Access Components (MDAC) ODBC drivers have been installed on the computer, you can make this change in the second step of the Create New Data Source wizard.

NOTE: You can download MDAC drivers from:

http://msdn2.microsoft.com/en-us/data/aa937730.aspx To configure the client, start the Create New Data Source wizard, click the Client Configuration button, and perform the following steps, based on the version of the SQL Server ODBC driver you are using:

For SQL Server ODBC Driver version 3.50

Click the Net Library tab and use the drop down list box to set the 
default network (Net Library) to Named Pipes.
On the Advanced tab, remove any advanced entries that reference the 
server you are connecting to.
Click Done.

For SQL Server ODBC Driver version 3.70

In the Network Libraries section of the Edit Network Library Configuration 
dialog box, select Named Pipes.
Click OK.

If you do not have the MDAC ODBC drivers installed, you can use the SQL Server Client Configuration Utility tool to set the default Net-Library to Named Pipes. You can install the client utilities from the SQL Server CD from the i386 directory.

Source: http://support.microsoft.com/kb/195566

回答3:

By default SQL Native Client seems to look for MSSQLSERVER instance. However, if you use SQLEXPRESS all connections through that will fail.

Very strange, but try installing your SQL Server with MSSQLSERVER instance name - it should work!

回答4:

To create a new Data source to SQL Server, do the following steps:

1. In host computer/server go to Sql server management studio --> open Security Section on left hand --> right click on Login, select New Login and then create a new account for your database which you want to connect to.

2. Check the TCP/IP Protocol is Enable. go to All programs --> Microsoft SQL server 2008 --> Configuration Tools --> open Sql server configuration manager. On the left hand select client protocols (based on your operating system 32/64 bit). On the right hand, check TCP/IP Protocol be Enabled.

3. In Remote computer/server, open Data source administrator. Control panel --> Administrative tools --> Data sources (ODBC).

4. In User DSN or System DSN , click Add button and select Sql Server driver and then press Finish.

5. Enter Name.

6. Enter Server, note that: if you want to enter host computer address, you should enter that`s IP address without "\\". eg. 192.168.1.5 and press Next.

7. Select With SQL Server authentication using a login ID and password entered by the user.

8. At the bellow enter your login ID and password which you created on first step. and then click Next.

9. If shown Database is your database, click Next and then Finish.