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 1 and type the answer here:
  • Post
  • I tried to connect to the SQL server Express 2005 database from my Pockect PC Emulator(Visual Studio 2005). I use C#.net and Remote Data Access method. so I used pull method of the RDA object. But there is a error on my Pockect PC Application like {"Failure to open SQL Server with given connect string. [ connect string = Provider=172.16.10.48,1433; Data Source=172.16.10.48\\SQLEXPRESS;Initial Catalog=LocalDBInsurance;User ID=Niluka;Password=windowsloginpassword ]"}
    and
    I also used the connection string as Provider=SQLOLEDB.1; Data Source=172.16.10.48\\SQLEXPRESS;Initial Catalog=LocalDBInsurance;User ID=Niluka;Password=windowsloginpassword. But these 2 were not worked.
  • Please overcome this
  • Hi, Thara

       1) The first connection string you used has wrong value for the "Provider" part, please search connection string properties in SQL Server Book Online for OLEDB provider.

       2) The second connection string you use seems good, but I do not know why you fail to connect. Please visit our Express blog to troubleshoot:
    http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

       3) We also noticed that it seemed that you try to use windows authentication from "windowsloginpassword", however, you should use connection property "Trusted_Connections=yes" instead of explicitly inputting password of windows account;unless you use SQL Login, you should use "User ID and Password".

    Thanks!
    Ming.

  • Hello,
    I have the following error while running this command:

    C:\>sqlcmd -S <servername>\SQLEXPRESS
    HResult 0xFFFFFFFF, Level 16, State 1
    SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
    Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
    shing a connection to the server. When connecting to SQL Server 2005, this failu
    re 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.

    When I run the above sqlcmd command on the client workstation (with SQL 2005 Express installed),
    I still get above error even though
    1. SQL Server Browser service is already running & active.
    2. All Share Memory, Named Pipes, TCP/IP Protocols for SQLEXPRESS & Client Protocols are Enabled.
    3, 'Local and remote connections-Using both TCP/IP and named pipes' are enabled.

    Are there anything I miss?
    Thanks
  • Hi,Lim

      Are you sure your sql server is running? Or whether you restarted the service after enabling the TCP or NP?
    1) If it is local connection, please see the SERVER ErrorLog to double check.
    2) If it is remote connection, please follow the instruction below http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx.

    For best practice and troubleshoot tips to make connection to SqlExpress, please visit our blog:
    http://blogs.msdn.com/sql%5Fprotocols/

    Thanks!
    Ming.
  • Hi there,

    I am wondering if I can get some help with connectivity to SQL Server Standard 2005.

    The obvious problem that I have is there is no protocols found in sql server 2005 network configuration (in SQL server configuration manager).

    The next thing I did was to run sqlcmd from command prompt and I received message as follow:

    Microsoft Windows XP [Version 5.1.2600]
    (C) Copyright 1985-2001 Microsoft Corp.

    C:\>sqlcmd
    HResult 0x2, Level 16, State 1
    Named Pipes Provider: Could not open a connection to SQL Server [2].
    Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
    shing a connection to the server. When connecting to SQL Server 2005, this failu
    re 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.

    I think this is server-side related problem since none of the server's network protocol can be found. I have tried reinstall SQL server 2005 and .NET 2.0 but to no avail.

    Thanks.

  • I have seen that this problem can occur if you install SQL when the machine has no network hardware installed or enabled (sql can't find any networking support).  Does your machine have a network card?
  • Hi, John

       The error indicates that your sql server might not be started successfully. Go to server ERRORLOG, see what error you saw, did you see info like " TDSSNIClient initialization failed with error <xxx>, status code <xxx> " ? The status code can give us clue.

    Also, Matt posted his comments, hope that yours is the same one.

    Thanks!
    Ming.
  • Hello, I have a serious problem and I need a resolve as fast as you can. I have installed sql server 2005 express edition. By default the shared memory is enable and all others are disable. I enabled all of them and I tried to connect through the SQL MANAGER EXPRESS EDITION. Anyway I can login only with Network protocol SHARED MEMORY. But when I run the sqlcmd I have the following error:
    C:\Program Files\Microsoft SQL Server\90\Tools\Binn>sqlcmd -U sgvaultuser
    Password: HResult 0x7E, Level 16, State 1
    Shared Memory Provider: The Shared Memory dll used to connect to SQL Server 2000 was not found [126].
    Sqlcmd: 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..
    Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

    Can any one tell me why I cannot connect through the sqlcmd utility? I have WinXP SP2

    Thanks Elias
  • Hi, Elias

       The problem here is that you were trying to connect to a default instance instead of SQL Express instance. By default, sql server 2005 express edition was installed as a named instance, which means, when you make connection you should give the instance name in your connection string:

    "sqlcmd -S.\SQLEXPRESS -U sgvaultuser"

    The reason you can not connect through " sqlcmd -U sgvaultuser" might be that you do not have SQL 2000 server installed on you box, and when SNAC try to connect to 2000 Server, it can not load "dbmslpcn.dll", check whether you have this module under "%windir%system32".

    To fix your problem:

    Please follow the best practice of connection to sql server 2005 express according to below blog:

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

    Good Luck!
    Ming.



  • Persone los pioneros non rabata. Great...
  • I get the following when trying to log in using

    osql -S instancename -U sa

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

    if I run it as
    osql -S 127.0.0.1 -U sa
    it logs in.

    Shared Memory, TCP/IP, Named Pipes all enabled, and in that order.

    Named Pipes not working with \\.\pipe\mssql$instancename\sql\query    or
    \\.\pipe\sql\query

    stopped and started services after each change.

    any help?
  • Figured it out.  The instancename was not the same as the server name.  When I do

    osql -S servername -U sa

    it works.   It's the only instance on this machine.  I'm an idiot.
  • Hi there, i hope i can get some help here :(
    I've been for about 3 days trying to connect VS2005 to my MS-SQLServer2005, what is working :
    ->I can connect trough SQL Server Management Studio , and Add, remove, edit any database
    ->I have allowed remote connections in SQL Server Surface Area Configuration
    ->My winXP SP2 firewall is off, and i don't have other
    ->In VS2005 i configure the webparts,membership providers to MSSQL2005 and work just fine

    Now the things that don't work:

    ->sqlcmd -S XPTO_NAME\MSSQLSERVER
    HResult 0x57, Level 16, State 1
    SQL Network Interfaces: Connection string is not valid [87].
    Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
    shing a connection to the server. When connecting to SQL Server 2005, this failu
    re 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.

    ->When i try  in VS2005 create a Data Connection (under the server explorer) the only Server name is the instanse name of SQLEXPRESS, i already try to put the .\MSSQLSERVER2005, but when i try to select any database show none, and the test connection faild

    Some configuration that i have:
    -> in the web.config of my application, the connection string that VS2005 use to save webparts information : <add name="LocalSQLServer" connectionString="&#xA;      server=(local);&#xA;      Trusted_Connection=False;&#xA;      uid=sa;&#xA;      password=12345;&#xA;      database=aspnetdb&#xA;" />, this work just fine.
    ->I have two sql server, the 2005 express and the 2005 developer edition

    Now anyone can help me to figure out whats wrong ? why the sqlcmd doesn't work ?
  • Hi, rstons

    1)

    sqlcmd -S XPTO_NAME\MSSQLSERVER
    HResult 0x57, Level 16, State 1
    SQL Network Interfaces: Connection string is not valid [87].

    The problem: "MSSQLSERVER" is not a valid instance name for SQL Server. If you installed a default instance, <machinename> can be the representitive of the default instance, hence, you need use "sqlcmd /S<machinename> /E"

    2)SQLExpress has fixed instancename, sqlexpress, so, when you make connection to the named instance sqlexpress, you should use ".\sqlexpress" instead of ".\MSSQLSERVER2005".

    More info about sqlexpress, please check another blog:

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

    Thanks!
    Ming.

Page 2 of 10 (142 items) 12345»