SQL Server 2005 Connectivity Issue Troubleshoot - Part I

SQL Server 2005 Connectivity Issue Troubleshoot - Part I

Rate This

This post provides some tips to troubleshoot Sql Server connection problems based on various displayed error messages. And, I will describe connection problems according to different client stack: SNAC/MDAC/SQLClient. Thus, there are 3 parts for this topic.

 

First Part – Troubleshoot SNAC connect to SQL Server 2005

Second Part - Troubleshoot MDAC connect to SQL Server 2005

Third Part – Troubleshoot SqlClient connect to SQL Server 2005

 

Before start, it is very important to identify which client connected to Server and failed. Here, I list out follow key terminology in Sql Server Connection.

 

SNAC - A new data access technology that is new in SQL Server 2005, and is a stand alone data access application programming interface that is used for both ODBC and OLEDB.

 

MDAC - Microsoft Data Access Component contains core data access components, such as OLEDB provider and ODBC provider.

 

SqlClient  - Access data from within a CLR database object by using the .NET Framework Data Provider for SQL Server.

 

Protocol Prefix: explicitly specify which protocol you want to use to make connection. Supported protocol prefix in Sql Server 2005 includes: “lpc:”, “np:”, “tcp:”, “via:”

 

Last connect cache – contains the fully resolved/specified connection strings for the instances that were successfully connected to. 

 

Part I – Connection Fail when SNAC connects to Sql Server 2005

 

Use osql.exe to simulate the connection string in your application and quick troubleshoot if your application uses ODBC provider and use sqlcmd.exe for OLEDB provider. They are located in %SYSTEMDRIVE%\Program Files\Microsoft Sql Server\90\tools\binn.

 

Basic connection string:          osql(sqlcmd) /S[prefix]<servername> /E

                                                Osql(sqlcmd) /S[prefix]<servername>\<Instance> /E

In each follow Message, there are two, one is from ODBC, and the other one is from OLEDB.

 

Message 1:

 

[SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [53].
[SQL Native Client]Login timeout expired
[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 under the default settings SQL Server does not allow remote connections.


-OR-

 

HResult 0x35, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [53].
Error: 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 under the default settings SQL Server does not allow remote connections..

Error: Microsoft SQL Native Client : Login timeout expired.

 

This indicates that your target server can not be accessed or does not exist. Try to use "ping <servername>" , " ping <ipofserver>" , "ping -a <ip>", If either of the pings time out, fail, or do not return the correct values, then either the DNS lookup is not working properly or there is some other networking or routing issue that you will need to resolve.

 

Message 2:

 

[SQL Native Client]Named Pipes Provider: Could not open a connection to SQL

Server [2].

[SQL Native Client]Login timeout expired

[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 under the default settings SQL Server does not allow remote connections.

 

-OR-

 

HResult 0x2, Level 16, State 1

Named Pipes Provider: Could not open a connection to SQL Server [2].

Error: 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 under the default settings SQL Server does not allow remote connections..

Error: Microsoft SQL Native Client : Login timeout expired.

 

This error message means that the server was not found or not running or can not make Named Pipe connection through the pipe name that client specified. To verify this:

 

1) From the command line, do "sc query mssqlserver" or "sc query mssql$<instancename>" to check whether sql instance present.  Then open sql server configuration manager -> check the state of the sql service, if it is not running, start it. If server started fail, check event log or server error log, see what happened there.

2)  if you are sure the service is running and shared memory/Named Pipe enabled, please try connection if it is local default instance "osql /Snp:\\.\pipe\sql\query"; or try connection "osql /Snp:\\.\pipe\mssql$<InstanceName>\sql\query" if it is local named instance. if you still get error 2, then go to step 3).

3) Double check the server is started and listening on named pipe if you enabled Named Pipe. One way is that see the ERRORLOG of the server, search follow keywords: 

Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ] or [\\.\pipe\mssql$<InstanceName>\sql\query] 

Notice that "sql\query" is the default pipe name, so you need to know server is listening on which pipe name. eg: if you specify server pipe name is "sql\query1", then you would see in the errorlog that server listening on [ \\.\pipe\sql\query1 ]. 

4) See your connection string, whether you explicitly specify the pipe name and does it match the pipe that server is listening on? Or whether you just specify server name( like ".","(local)", etc), but you specify the wrong pipe name on client side Named Pipe configuration.eg, go to SQL Server Configuration Manager, click client Named Pipe properties, see whether the pipe name is same with the one server listening on. 

Note: For remote connection, you need to verify step 2) and 3).  

Message 3:

 

[SQL Native Client]Named Pipes Provider: Could not open a connection to SQL

Server [233].

[SQL Native Client]Login timeout expired

[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 under the default settings SQL Server does not allow remote connections.

 

 -OR-

 

HResult 0xE9, Level 16, State 1

Named Pipes Provider: Could not open a connection to SQL Server [233].

Error: 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 under the default settings SQL Server does not allow remote connections..

Error: Microsoft SQL Native Client : Login timeout expired.

 

Note: the difference between Message 2 and Message 1 is “Could not open a connection to SQL Server [233].” – error state.

 

You might specify the server name as FQDN/127.0.0.1/ IP Address and NP was disabled on the server. To resolve this, first way is to replace server name as the machine name or “.” or”(local)” or “<machinename>” and you should be able to connect as long as server listening on Shared Memory; second way is to enable named pipe from sql configuration manager and restart server.

 

Message 4:

 

[SQL Native Client]Named Pipes Provider: No process is on the other end of the pipe.

[SQL Native Client]Communication link failure

[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 under the default settings SQL Server does not allow remote connections.

 

-OR-

 

HResult 0xE9, Level 16, State 1

Named Pipes Provider: No process is on the other end of the pipe.

Error: Microsoft SQL Native Client : Communication link failure.

Error: 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 under the default settings SQL Server does not allow remote connections..

 

1) Local connection: You might specify the server name as FQDN/127.0.0.1/ IP Address in the connection string and speculate connection through Named Pipe provider. To resolve this, either change server name to <machinename> as long as the server is listening on Shared Memory or enabled NP.

2) Remote connection: the server is not listening on Name Pipe. To resolve this, enable name pipe on the remote server and restart the server.

 

Message 5:

 

[SQL Native Client]SQL Network Interfaces: Server doesn't support requested protocol [xFFFFFFFF].

[SQL Native Client]Login timeout expired

[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 under the default settings SQL Server does not allow remote connections.

 

-OR-

 

HResult 0xFFFFFFFF, Level 16, State 1

SQL Network Interfaces: Server doesn't support requested protocol [xFFFFFFFF].

Error: 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 under the default settings SQL Server does not allow remote connections..

Error: Microsoft SQL Native Client : Login timeout expired.

 

1)     Local connection: You might explicitly specify protocol prefix “np:” and connect to a named instance and NP was disabled.

2)     Remote connection: You might connect to remote named instance and remote server is not listening on name pipe.

To resolve this, enable NP if you only want to use name pipe protocol or you can remove “np:” prefix to let connection over shared memory locally.

 

Message 6:

 

[SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [0xFFFFFFFF].

[SQL Native Client]Login timeout expired

[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 under the default settings SQL Server does not allow remot the connections.

 

-OR-

 

HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

Error: 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 under the default settings SQL Server does not

allow remote connections..

Error: Microsoft SQL Native Client : Login timeout expired.

 

You might connect to local named instance and explicitly specify protocol prefix “tcp:” or “np:” in the connection string, however, SqlBrowser service was not running.

To resolve this, you should enable Sqlbrowser service on the server

1)     Use net start or go to sql configuration manager(SSCM), check whether sqlbrowser service is running, if not, start it.

2)     You still need to make sure SqlBrowser is active. Go to SSCM, click properties of sqlbrowser service -> Advanced-> Active “Yes” or “No”, if sqlbrowser is running but is not active, the service would not serve you correct pipe name and Tcp port info on which your connection depends.

 

Message 7: Shared Memory provider error

 

HResult 0x2, Level 16, State 1

Shared Memory Provider: Could not open a connection to SQL Server [2].

Error: 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 under the default settings SQL Server does not allow remote connections..

Error: Microsoft SQL Native Client : Login timeout expired.

 

-OR- 

 

[SQL Native Client]Shared Memory Provider: Could not open a connection to SQLServer [2].

[SQL Native Client]Login timeout expired

[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 under the default settings SQL Server does not allow remote connections.

The error indicate you speculated shared memory as connection protocol and server is not listening on shared memory, plus you probably specified “.”/”(local)”/<machinename> /localhost as the server name in the connection string. To resolve this, enable shared memory protocol and restart the server.

 

Message 8:

 

[SQL Native Client]SQL Network Interfaces: Cannot open a Shared Memory connection to a remote SQL server [87].

[SQL Native Client]Login timeout expired

[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 under the default settings SQL Server does not allow remote connections.

 

-OR-

 

HResult 0x57, Level 16, State 1

SQL Network Interfaces: Cannot open a Shared Memory connection to a remote SQL server [87].

Error: 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 under the default settings SQL Server does not  allow remote connections..

Error: Microsoft SQL Native Client : Login timeout expired.

The reason is same as the one of Message 6, just you might specify FQDN/127.0.0.1/IP Address as server name in the connection string.

 

Message 9: TCP specific

 

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

[SQL Native Client]Login timeout expired

[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 under the default settings SQL Server does not allow remote connections.

 

-OR-

 

HResult 0x274D, Level 16, State 1

TCP Provider: No connection could be made because the target machine actively refused it.

Error: 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 under the default settings SQL Server does not  allow remote connections..

Error: Microsoft SQL Native Client : Login timeout expired.

 

The reason is server is not listening on TCP, probably TCP protocol was not enabled.

 

-Follow messages are some special case - 

 

Message 10:

 

[SQL Native Client]Unable to complete login process due to delay in opening server connection.

 

Reason:

1)     There are spaces after Instance name in the connection string eg. osql /S”<machinename>\Instance  “ /E, to resolve this, you need to remove the trailing space.

2)     Connect through 127.0.01.

3)     Remote connection and WINS was disabled on the client machine and you connect using FQDN as server name. To resolve this, One way, turn on “File and Printer Sharing” and explicitly use name pipe protocol. Another is enlarge the connect timeout to around 30 secondes.

 

Message 11: - Firewall specific

 

HResult 0x274C, Level 16, State 1
An error has occurred while establishing a connection to the server. When connectiong to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.(provider:TCP Provider, errror:0-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.)

 

-OR-

 

Error: 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 under the default settings SQL Server does not allow remote connections..
Error: Microsoft SQL Native Client : Login timeout expired.

 

This is because connection blocked by firewall. To resolve this, take follow steps:

1)     Enable SqlBrowser, see the info in Message 4. Plus, add sqlbrowser.exe into Firewall exception list: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\DomainProfile\AuthorizedApplications\List
2)     Add Tcp port to Firewall exception list. (eg, Name-1433:TCP, Value-1433:TCP:*:Enabled:Tcp 1433).

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\DomainProfile\GloballyOpenPorts\List

For more detailed  operation, see http://support.microsoft.com/default.aspx?scid=kb;en-us;287932 

 

Message 12:

 

“[SQL Native Client] Can not Generate SSPI Message”

 

http://blogs.msdn.com/sql_protocols/archive/2005/10/19/482782.aspx 

 

Part II - Troubleshoot Connectivity Issue in SQL Server 2005

 

http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx

 

Part III - Troubleshoot Connectivity Issue in SQL Server 2005

 

http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx

 

 

Ming Lu SQL Server Protocols

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Leave a Comment
  • Please add 5 and 8 and type the answer here:
  • Post
  • SQL has two hurdles to deal with when connecting.  

    #1. Dynamic port resolution, or "What port is SQL running on?".

    #2. Once I know the port, can I connect to the port.

    #1 dynamic port resultion is blocked by most firewalls by default today, this is UDP port 1434.  If you want dynamic port resolution, you need to open up UDP port 1434 on your firewall and also ensure that the SQL Browser Service is running for SQL 2005.  Also note that dynamic port resolution only works on named instances, it does not work for default instances.  So if you changed the tcp port for the default instance, #1 will never work for you, the client MUST know the port to connect to.

    #2. Now that I know the port, why can't I connect to the port?  This one is just basic tcp-ip 101.  Can I ping the IP?  Can I telnet to the port (telnet 123.123.123.123 1433)?  You could be blocked by the firewall or router or IPSec policy, you need to open these up for the specified target port and then everything will work.

    So to successfully resolve #1 and #2, you need to know what port your SQL is running on.  Start SQL and examine the ERRORLOG to see what tcp-ip port it is listening on.  Perhaps you have not configured SQL to listen on TCP-IP at all, check the configuration tools and enable TCP-IP for the instance, etc...

  • When I try to connect to a default named Express instance (computername\SQLEXPRESS) in Management Studio at the machine (Windows XP SP2) where the instance is installed, I'm getting the following error:

    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 under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server)

    What does provider: SQL Network Interfaces mean? Does it mean the Shared Memory, Named Pipes and TCP/IP?

    Additional information:

    1.  The following Network Protocols are enabled: Shared Memory, Named Pipes, TCP/IP thru Configuration Manager

    2.  SQL Server Express default instance is running using NT AUTHORITY\NetworkService.

    3.  SQL Server Browser service is not running. I believe this is not needed unless I want to connect to the instance from another machine.

    4.  Has tried to disable Windows Firewall but I believe it should not be matter in this particular case since this is within the same machine.

    5.  Can connect to the instance in ODBC Administrator using System DSN thru Named Pipes or TCP/IP.

    I also want to confirm my understanding that a remote connection means you're connecting to a SQL Server instance installed on another computer and it will not mean to connect to a SQL Server instance installed on the same machine regardless of which network protocol is used.  

    I don't understand why sql browser service is required for a local named instance that is mentioned in the paragraph (see below) in Message 6.

    --- copy from message 6

    You might connect to local named instance and explicitly specify protocol prefix “tcp:” or “np:” in the connection string, however, SqlBrowser service was not running.

    To resolve this, you should enable Sqlbrowser service on the server

    1)     Use net start or go to sql configuration manager(SSCM), check whether sqlbrowser service is running, if not, start it.

    2)     You still need to make sure SqlBrowser is active. Go to SSCM, click properties of sqlbrowser service -> Advanced-> Active “Yes” or “No”, if sqlbrowser is running but is not active, the service would not serve you correct pipe name and Tcp port info on which your connection depends.

    ----- end of message 6

  • In my previous message, I forget to ask whether Management Studio using a connection string which specifies protocol prefix "tcp:" or "np:" when it tries to connect to a local named instance?  If yes, what is the connection string look like and how to view it?

  • There is no need to start SQL Browser service if you are connecting locally.  This is the default configuration for SQLExpress and SQL Server in general.

    Q. SQL Network Interfaces mean?  

    A. This is just the name of the underlying network interface that the driver is using.  MDAC drivers use dbnetlib interface and the newer SNAC drivers use SNI (SQL Network Inteface).  To most customers this is not really that important, like knowing if you have copper or steel pipes, you don't care as long as the water comes out.

    Q. Does it mean the Shared Memory, Named Pipes and TCP/IP?  

    A. Yes.  SNI implements these protocols (and so does dbnetlib).

    Q. I also want to confirm my understanding that a remote connection means you're connecting to a SQL Server instance installed on another computer and it will not mean to connect to a SQL Server instance installed on the same machine regardless of which network protocol is used.  

    A. Yes, this is correct.

    The driver will first try hitting SQL Browser and secondly check the local registry to determine how to connect to the named instance SQLEXPRESS, this is how it works locally.  So if your client application cannot access the registry for some reason, then it could possibly fail to get information about the SQLEXPRESS instance.

    Go to SQL Server Configuration Manager and go under the SQL Native Client Configuration tool and verify Shared Memory is enabled and at the top of the list, this will probably fix it.

  • Q. In my previous message, I forget to ask whether Management Studio using a connection string which specifies protocol prefix "tcp:" or "np:" when it tries to connect to a local named instance?  If yes, what is the connection string look like and how to view it?

    A. Management studio does not make any changes to the connection string, it just uses the given server name and the server to connect to. You can actually prefix the servername with tcp: or np: if you want and it will pass this thru, for example ->

       tcp:MyServer,5555

    This will connect to server on tcp port 5555.

  • Hi Matt,

    Thanks for your posts.  

    I have tried few different settings in SQL Server Configuration Manager (SSCM).

    1. Enabled only the Shared Memory network protocol under SQL Server 2005 Network Configuration (SSNC) and disabled all the client protocols under SQL Native Client Configuration (SNCC).  I can connect in SSMS.  However, restarting SQL Server service in SSCM will get the message "The request failed or the service did not respond in a timely fashion..."  Sometimes, the message will just stay there and the CPU will run 100% and I need to reboot the machine.  If I stop and start the SQL Server service in SSCM, it does not have this problem.  It seems to me that the Client Protocols under SNCC can be all disabled and SSMS will still work.

    2.  Disabled all network protocols under SSNC.  Enabled only Shared Memory under SNCC.  Starting SQL Server service in SSCM will get the message "The request failed or the service did not respond..."  Does it mean that SQL Server service can only be started if at least one of the network protocols is enabled?

    3.  Enabled only Named Pipes or TCP/IP under SSNC.  Disabled all client protocols under SNCC.  Trying to connect in SSMS will get error: 26 Error Locating Server/Instance Specified if SQL Server Browser service is not running.

  • Hi Matt,

    Forget to ask this in my last message.

    You mention that connection issue may be caused by registry entries not being accessible.  What are the registry entries that I need to check?  Who should able to access those registries when the SQL Server service is running under NT Authority/networkservice?

    Thanks,

    Peter

  • SQL Server 2005 Express is installed in a Windows XP SP2 machine in a peer-to-peer network.  When the windows firewall is on, only 1 of the workstations within the network can connect to the SQL Server.  When windows firewall is off, all the workstations within the network can connect to the SQL Server. I cannot figure out why only 1 workstation can connect when the windows firewall is on.

    Thanks,

    Peter

  • BIG Ah Ha!  For a number of you, this may have been obvious, but I didn't see this one.  When trying to connect to my sql instance, such as using the SQL Server Manager; where Microsoft asks for a server, they REALLY MEAN Server AND Instance!  i.e. server\instance (MYPC\SQLEXPRESS)

    Hope this helps some of you!

  • I want to understand the IP Addresses tab of the TCP/IP Properties in SQL Server Configuration Manager.  The following settings are from the default named instance of SQL Server 2005 Express (computername\SQLEXPRESS) on a Windows XP SP2 machine with IP Address 192.168.1.151.

    IP1 has the following settings:

    Active: Yes

    Enabled: No

    IP Address: 192.168.1.151

    TCP Dynamic Ports: 0

    TCP Port: 1433

    IP2 has the following settings:

    Active: Yes

    Enabled: No

    IP Address: 127.0.0.1

    TCP Dynamic Ports: 0

    TCP Port:

    IPAll

    TCP Dynamic Ports: 1121

    TCP Port:

    Is the instance using Dynamic Port or Static Port?  Normally, I will see blank for TCP Port when TCP Dynamic Ports is 0 in IP1 but in this case, TCP Port has 1433.

    My guess is that the instance is using Dynamic Port and the port is 1121.

  • Hi, Peter

       You are right, sql server named instance use Dynamic port. And from the configuration you posted, your sql express is listening on 1121 if tcp enabled. Since, IP1 and IP2 were not enabled, which means you server is listening on all IP Address, hence it use the port 1121.

    You can check out following blog for sqlexpress connectivity guidline and troubleshooting tips.

    http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

    Good Luck!

    Ming.

  • It is not enough to set a firewall exception for sqlservr.exe. The 1433 port also has to be added as an exception.

  • I'm trying to create an ODBC System DSN to connect to a remote SQL Server 2005 default instance (MSSQLSERVER).  Information for the remote SQL Server 2005:

    1. SQL Browser service running under LocalSystem

    2. Windows Firewall is disabled

    3. Shared Memory, Named Pipes, and TCP/IP are enabled.

    4. TCP/IP's Listen All is set to Yes and IPAll is using dynamic port which is 1905.

    The problem I have is that the ODBC System DSN won't able to connect to the remote SQL Server 2005 if I check Dynamically determine port but able to connect if I specify the port number 1905.  

    If I issue telnet 192.168.1.151 1905, I will the blank command window titled Telnet 192.168.1.151 so I guess this mean the port is accessible.

  • I have just submitted a feedback in connect.microsoft.com.  I think I should include it in here since it is a connection issue:

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=259022

  • The issue I posted on Monday, February 19, 2007 4:08 PM is also caused by the issue I posted on Monday, February 19, 2007 7:41 PM.

Page 5 of 10 (142 items) «34567»