SQL Server Error: 10060 SQL Server Error: 10061

  • Article

Ok, new entry with connectivity problems and examples.

The scenario:

You have a SQL Server 2005 that is up and running you also know that the SQL Browser service is up and running.

So on the server side, all is well.

On a side note, if you do not know what the SQL Browser is or how it works, the short story is as follows.

When SQL 2005 Server starts, a TCP/IP port is assigned to it. This is so that you can have several instances of SQL Server on the same machine.

So in order to be able to serve the clients that tries to connect to these different instances, the SQL Browser steps in as a 'lookup' service.

So you specify <servername>\<instancename> and connect, when this request hits the server, SQL Browser looks up the instance name and

sends the connection information for the instance back to the client. The client then can perform the proper connection using this information.

If checking this using Network Monitor, we can see the following.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

xxx.xxx.162.45 55515, 55515(0xd8db) xxx.xxx.162.187 1434, 1434(0x59a) SQLBrowser SQLBrowser

xxx.xxx.162.187 1434, 1434(0x59a) xxx.xxx.162.45 55515, 55515(0xd8db) SQLBrowser SQLBrowser

                             ServerName;<servername>;InstanceName;MASPENGLAB2000;IsClustered;No;Version;8.00.194;np;\\<servername>\pipe\MSSQL$MASPENGLAB2000\sql\query;tcp;1273

xxx.xxx.162.45 61635 xxx.xxx.162.187 1273 TCP TCP: Flags=.S......, SrcPort=61635, DstPort=1273, Len=0, Seq=950729843, Ack=0, Win=8192

xxx.xxx.162.187 1273 xxx.xxx.162.45 61635 TCP TCP: Flags=.S..A..., SrcPort=1273, DstPort=61635, Len=0, Seq=1948104676, Ack=950729844, Win=16384

xxx.xxx.162.45 61635 xxx.xxx.162.187 1273 TCP TCP: Flags=....A..., SrcPort=61635, DstPort=1273, Len=0, Seq=950729844, Ack=1948104677, Win=16502

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

In this case xxx.xxx.162.45 is the client, xxx.xxx.162.187, the client is connecting using the data source name: <servername>\maspenglab

When connecting we can see that the client calls the server machine, SQLBrowser interferes, does it's lookup and when checking the payload we can see that

the SQLBrowser sends back the connection details to the client, in this case it says that TCP/IP connections are to be made on port 1273.

When this is done, we can see that there is the 3way handshake on that particular port (1273).

Anyway, this is background. For more information on SQLBrowser, have a look at:

"SQL Server 2005 Books Online (September 2007) - SQL Server Browser Service"

https://msdn.microsoft.com/en-us/library/ms181087(SQL.90).aspx

Now, back to the scenario, you have verified that both SQL Server and SQL Brower services are running, but when you try to connect using, for example,

the ODBC Data Source Administrator (Start -> Run -> odbcad32) you will may get connection errors that are related to ports:

Error scenario 1:

The SQL Browser service is allowed through the firewall on the server, but the SQL Server 2000/2005 is not.

This means that in, as shown above, that the client can connect to SQL Brower and the SQL Browser sends back the port number, 1273 in this case, however, when the client then

tries to connect using this port, the firewall on the SQL Server machine blocks it, and the client will show the following error:

When using "SQL Server" (sqlsrv32.dll) provider:

----------------------------------------------------------------------------------------------

Title: Microsoft SQL Server Login

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.

----------------------------------------------------------------------------------------------

When using "SQL Native Client" (sqlncli.dll) provider:

----------------------------------------------------------------------------------------------

Title: Microsoft SQL Server Login

Connection failed:

SQLState: '08001'

SQL Server Error: 10060

[Microsoft][SQL Native Client]TCP Provider: A connection attempt failed because the connected party did not properly respond after a

period of time, or established connection failed because connected host has failed to respond.

Connection failed:

SQLState: 'HYT00'

SQL Server Error: 0

[Microsoft][SQL Native Client]Login timeout expired

Connection failed:

SQLState: '08001'

SQL Server Error: 10060

[Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server.

When connecting to SQL Server 2005, this failure may be caused by the fact that that under the default settings SQL Server does not allow remote connections.

----------------------------------------------------------------------------------------------

SOLUTION: Allow traffic in the firewall on SQL Server 2000 / 2005.

Error scenario 2:

Here there may be an alias setup for the server using the server name or in the case "Dynamically determine port" may be unchecked.

If this is unchecked, then there in effect is an alias created for the connection.

So, when there is an alias setup, and this alias is pointing to a fixed port, the connections will fail. Again, the SQLBrowser may return a certain port number, however,

this number is ignored since the alias provides a fixed port, and there is no server instance listening on that particular port and the client will show the following error:

When using "SQL Server" (sqlsrv32.dll) provider:

----------------------------------------------------------------------------------------------

Title: Microsoft SQL Server Login

Connection failed:

SQLState: '01000'

SQL Server Error: 10061

[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.

----------------------------------------------------------------------------------------------

When using "SQL Native Client" (sqlncli.dll) provider:

----------------------------------------------------------------------------------------------

Title: Microsoft SQL Server Login

Connection failed:

SQLState: '08001'

SQL Server Error: 10061

[Microsoft][SQL Native Client]TCP Provider: No connection could be made because the target machine actively refused it.

Connection failed:

SQLState: 'HYT00'

SQL Server Error: 0

[Microsoft][SQL Native Client]Login timeout expired

Connection failed:

SQLState: '08001'

SQL Server Error: 10061

[Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server.

When connecting to SQL Server 2005, this failure may be caused by the fact that that under the default settings SQL Server does not allow remote connections.

----------------------------------------------------------------------------------------------

SOLUTION:

For sqlsrv32.dll :

Make sure that "Dynamically determine port" is unchecked in the ODBC Data Source Administrator setup, this is done in the "Client Configuration" dialog.

For sqlncli.dll :

Make sure that there is not a fixed port number for an alias (using the server\instance name) in the SQL Server Configuration Manager/SQL Native Client Configuration/Aliases

or if you feel brave, locate the alias in the registry and delete it there.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo\

The error seems similar, but there is small difference in the "SQL Server Error", 10060 vs 10061.