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 1 and 2 and type the answer here:
  • Post
  • The solution to my issue was to place a record for the subscriber in the hosts file in sys32.  Apparently what I have done for over 8 years, establishing and alias for the subscriber is not the correct thing to do and Microsoft does not support DNS resolution for replication but WINS .... Who figures.

  • The solution to my issue was to place a record for the subscriber in the hosts file in sys32.  Apparently what I have done for over 8 years, establishing and alias for the subscriber is not the correct thing to do and Microsoft does not support DNS resolution for replication but WINS .... Who figures.

  • Useless and junk product, also bundled with a scrap product reporting services. A product should be simple, like even creating a user is kept like taking time to give birth to a baby from starting.

  • I am using Vista Ultimate.  I still get the 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) "

    I've tried most of the things in this forum.  My named pipes are enabled and I've tried changing the connection string to both use SQL Authentication and Windows.  

    I am setting the connection string in my web.config using the Enterprise Library config tool.  This doesn't do anything wierd, it just sets the connection string up and inputs that into your web.config per the database, authenticaion method (windows or sql), logon info (if using sql), etc.   You can even test the connection while in the config tool and it works fine either way so I don't understand why when I try to load my .aspx I am still getting this error.

    The user of course has rights to the database or else the config testing of the connection string in enterprise manager wouldn't work anyway.

    Here's a couple of connection strings that were created that I tried but I get the same error for all t hese:

    <add name="Connection String" connectionString="Data Source=(local);Initial Catalog=Elbalazo;Persist Security Info=True;User ID=ElbalazoAdmin;Password=******"

         providerName="System.Data.SqlClient" />

    <add name="Connection String" connectionString="Data Source=(local);Initial Catalog=Elbalazo;Integrated Security=True"

         providerName="System.Data.SqlClient" />

    I checked in the SQL Server Configuration Manager and named pipes is enabled across the board.

    The connection string test to be good in Enterprise Library Config tool (what was created above) when it tests that string.

    I don't now what else to do.  I of course am part of the Administrators group on my PC.  This is a home PC using Vista Ultimate.

  • You gotta love it.  Microsoft increases security in both Vista and SQL Server 2005 but cannot give us decent error messages nor give us common scenario list that I'm sure they came across when they tested and their clients beta tested connection strings within their applications to SQL 2005.

    I mean in my case we're talking about a simple .aspx that has a correct connection string clearly in the web.config, the user has the rights, typical sh** and still there is an entire dedicated page to this BS.

    Common Microsoft,  you should be shipping a box of Fail with instructions on how to undo your mysterious mazes.

  • Still having trouble figuring out the problem with our linked server issue.  Looking at the servers having the problems, we are running MSSQLServer under an end user's domain account...  I checked his account profile under the delegation tab, the settings are as follows:

    -Do not trust this user for delegation

    -Use Kerberos only.

    Our problem is when we try to do cross-server queries, the link is supposed to authenticate on the other server as the user running the query since we have the connection set to "Be made using the login's current security context" on the link object itself.  However, when the query fails, we get this error:

    OLE DB provider "SQLNCLI" for linked server "Administration" returned message "Login timeout expired".

    OLE DB provider "SQLNCLI" for linked server "Administration" returned message "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.".

    Msg 53, Level 16, State 1, Line 0

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

    Msg 18456, Level 14, State 1, Line 1

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    Msg 18456, Level 14, State 1, Line 1

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    Msg 18456, Level 14, State 1, Line 1

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    Msg 18456, Level 14, State 1, Line 1

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    Msg 18456, Level 14, State 1, Line 1

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    Why would it say it was an anonymous login if it is windows authentication?

    Testing the link object itself (by right clicking and do a "test connection" gives us a similar error:

    "The test connection to the linked server failed."

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

    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

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

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

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    OLE DB provider "SQLNCLI" for linked server "Administration" returned message "Login timeout expired".

    OLE DB provider "SQLNCLI" for linked server "Administration" returned message "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.". (Microsoft SQL Server, Error: 53)

    FYI, We use only windows authentication for SQL.  Any ideas?

  • On the server which we're trying to pull the data from (via the linked object) a quick glance at the SQL Server Logs reveals this error:

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    Error: 18456, Severity: 14, State: 11.

  • Hi Patrick,

     It looks like this is your problem:

    -Do not trust this user for delegation

     The SQL Server's user account needs to be trusted for delegation, otherwise it won't be able to pass on its credentials to the next SQL Server account.  Try changing this to trust the SQL Server account for delegation and see if that helps.  

     If it isn't already, you will also need to set the account of the user that is connecting to the SQL Server and issuing the query (i.e., the user your application runs under) so that it is trusted for delegation.

    Hope this helps.

    Dan

  • Dan,

    Thanks for the quick response!!!  One more question.  I haven't seen a delegation tab under any other users except for this account (happens to be the company president) that's running the SQL services....  Why would that tab not be there for anyone but him?  Does the tab automatically appear if your user account is used to run a service on the domian like SQL?  Or is it a setting somewhere else?

  • Hi Patrick,

     I don't have an Active Directory box handy to look at this, but I think the Delegation tab appears for any user that doesn't have "This account is sensitive and cannot be delegated" selected.  Can you make sure that this is not selected for the user(s) you are trying to enable delegation for?

    This MSDN article might be helpful:

    http://msdn2.microsoft.com/en-us/library/ms189580.aspx

  • Dan,

    Well, I solved our problem and answered your question all in one shot.

    I believe the delegation tab appears if you have a service running under your user account on the domain.  The reason I think this is because I just changed both our dev SQL boxes to run the SQL service under "NT Authority\NetworkService" and that solved our linked server problem.  I went back and checked the user's account that it was running under before, and viola!  No more delegation tab.  For the record, I have no idea why these boxes were setup to run under his domain account (instead of a built-in one) in the 1st place, but I'm sure he had a good reason at the time.

    Thanks for all your help on this..  I'll keep you guys posted if anything changes.

    Patrick-

  • Hi There,

    I am trying to configure SQL Server 2005 express in a local system. It does not have any network connection. I have the services stared but facing the following issues when trying to connect.

    When I type osql/snp:\\local\pipe\sql\query at the command prompt

    I am getting the fowllowing error messagre

    [Shared Memory]SQL Server does not exist or access denied.

    [Shared Memory]ConnectionOpen (Connect()).

    When I look into the configuration manager I see both

    SQL Server(SQLEXPRESS) and SQL Server Browser running.

    I have the following Protocol for SQLExpress enabled:

    Shared Memory

    Named Pipes

    TCP/IP

    Further,when I try to run sqlcmd at the command prompt I am getting the following error message:

    HResult 0x2, level 16, State 1

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

    Sqlcmd: Error: Microsoft SQL Native Client: An error has occured 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..

    Sqlcmd:Error: Microsoft SQL Native Client : Login timeout expired.

    Any hlep to resolve the above issue will be highly appreciated.

    Thanks,

    Kalyan.

  • I recently install game called Perfect World. Installation was succesful also patching. But when i started the game i got this error:

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

    i have no idea what to do, because I dont have any experiance regarding SQL. I have SQL Server Compact edition insatlled v3.1.

  • sory for another posthere is more detiled error

    Microsoft SQL Native Client error '80004005'

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

    /ann-list.asp, line 67

  • Guys, in Delphi I've resolved "SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]" error by simple changing of Provider property of ADO Connection component from SQLNCLI.1 to SQLNCLI, the same I did in ConnectionString.

    I believe the same works for VB.

Page 8 of 10 (143 items) «678910