Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Part III – Connection Fail when SqlClient connects to Sql Server 2005

When you connect to SQL Server 2005 either using "SQL Server Managment Studio" or any application compiled with .NET Framework 2.0, you are using SqlClient provider(Access data from within a CLR database object by using the .NET Framework Data Provider for SQL Server.)


Error Message 1:

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)

Troubleshoot:
1) Make sure your sql service is running, use either "net start" or "sc query <InstanceName>" or run services.msc, check status of the server; If server start fail, go to ERRORLOG to see what happened there, fix the problem and restart server.

2) You might explicitly use "np:"prefix which ask for connect through named pipe. However, client can not connect to server through the pipe name that specified.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 ], and go to SQL Server Configuration Manager, click client Named Pipe properties, see whether the pipe name is same with the one server listening on.

3) You might specify named pipe protocol in connection string, but did not enable named pipe on the server, check ERRORLOG.

4) You might use FQDN/IPAddress/LoopbackIP to connect to the server when only shared memory was enabled, you can change to <machinename> to resolve this.

5) You might explictly specify "lpc:" prefix in your connection string, but shared memory was not enabled. To resolve this, either remove the prefix as long as named pipe or tcp was enabled or enable shared memory.

Error Message 2:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. 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: 0 - No process is on the other end of the pipe.)

1) You might explicitly use "np:"prefix which ask for connect through named pipe and specify FQDN/LoopbackIP/IPAddress as server name in the connection string.
2) You might use FQDN/IPAddress/LoopbackIP to connect to the server.

To resolve 1) and 2), you can specify <machinename> instead of FQDN/IPADress/LoopbackIP.

Error Message 3:

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: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server)

Troubleshoot:
1) You might explicitly use "lpc:"prefix which ask for connect through shared memory. Either server instance was not started successfully or shared memory was not enabled on the server.To resolve this, you remove "lpc:"prefix in your connection string as long as Server is listening on other protocols or enable shared memory and restart server.

2) You explicitly use "lpc:"prefix and connect to a local named instance through form [./(local)/localhost/<machinename>]\<InstanceName>, but Sqlbrowser service was not started. To resolve this, you need to enable sqlbrowser.

3) You might connect through "lpc:" which not includes any server name, to fix this, add correct server name in your connection string.

Error Message 4:

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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)

Troubleshoot:
1) You might explicitly use "tcp:"prefix which ask for connect through TCP/IP, however either server was not listening on TCP/IP, to resolve this, either remove "tcp:"prefix in your connection string or enable tcp protocol.

2) You might not connect through the exact port that server is listening on, to verify this, go to SQL Configuration Managner
choose "Protocols for <InstanceName>" and click properties for TCP/IP, see which port is configured for server listening and then try connect through the port, like in connection string "tcp:<machinename>,<portnumber>".

3) The instance that you want to connect through TCP was not started, check server ERRORLOG and restart server.

Error Message 5:

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)

1) You might have named pipe or tcp enabled and connect to a named instance, but SQL Browser service was not started or enabled. To enable browser, First, Use net start or go to sql configuration manager(SSCM), check whether sqlbrowser service is running, if not, start it; Secondly,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.

Error Message 6:

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: 41 - Cannot open a Shared Memory connection to a remote SQL server)

1) You might explicitly use "lpc:"prefix and connect to a named instance but specify FQDN/LoopbackIP/IP as <servername>, eg, your connection string looks like "lpc:<FQDN>\<InstnaceName>" or "lpc:127.0.0.1\<InstanceName>"..

2) You might explicitly use "lpc:"prefix and give the wrong server name in your connectionstring, eg: "lpc:xx" <xx> is not the hostname of your machine.

Error Message 7:

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: 28 - Server doesn't support requested protocol)

1) You might explicitly use "np:" prefix in your connection string and try to connec to a named instance, but named pipe was not enabled on the remote server, to resolve this, enable the remote named pipe and restart instance or remove "np:"prefix if remote server is listening on TCP/IP.

2) You might explicitly use "tcp:" prefix in your connection string and try to connec to a named instance, but TCP/IP was not enabled on the remote server, to resolve this, enable the remote TCP/IP and restart instance or remove "tcp:"prefix if remote server is listening on Named Pipe.

Error Message 8:

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

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

a. 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
b. 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

Summary:

1) In any case, SqlClient should be able to connect to SQL Instance through any of the protocols(Shared Memory/Named Pipe/TCP) as long as SQL Instance was started successfully.

2) And if you speculate any protocol in connection string ("lpc:"/"np:"/"tcp:"), the error message would display "<Protocol> Provider, error <Num1> -....<Num2>." <Protocol> stands for "Shared Memory" or "Named Pipes" or "TCP"; If you do not speculate
any protocol, the error message indicates that connection fails when connecting through specific <Protocol>.

3) In the error message format for SqlClient, please notice two different error number. <Num1> stands for internal error thrown out by SQL Protocols, <Num2> is the OS error(eg: 233 - No process is on the other end of pipe). When you see <Num1>=0, that means the connection fails due to OS error not caused by SQL Protocols, under this situation, you can use "net helpmsg" to check specific OS info.

Finally, if you were developing .NET framework application and came across above issues in your client app, the best way is first try SQL Server Management Studio to connect to SQL Server using the exact same connection string in your app, and watch the error message, normally, there is additional error info at the end of error string, eg ( Microsoft SQL Server, Error:87) which gives you clue(net helpmsg 87) that problem inside your connection string.

MING LU

SQL Server Protocols

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

 

Leave a Comment
  • Please add 6 and 3 and type the answer here:
  • Post
  • This is a great list of troubleshooting tips. I would like to add one for your inspection and (if possible) resolution. This has been a stumper for me. Any help would be appreciated.
    Randy Martin
    randy.martin@ds-iq.com


    OLE DB provider "SQLNCLI" for linked server "linkedserver" 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 782, Level 16, State 1, Line 0
    SQL Network Interfaces: No credentials are available in the security package
    OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message "Client unable to establish connection".
    Msg 782, Level 16, State 1, Line 0
    SQL Network Interfaces: No credentials are available in the security package
  • i need help with this.

    I try to connect my project in visual studio with my database sql server express 2005 and...

    An error has occurred while established a connection to 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

    please any help to:
    juliangrijalba@gmail.com
  • Julian: If you’re trying to connect from a remote machine you will have to enable network protocols to enable remote connectivity.

    This may help.
    http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

  • Hi, randy

    Here is our suggestion:

    This might caused by the kerberos ticket in the session cache expires in the middle tier server.The situation could be first, you configure a linked server C on your machine B, and you connect to B from machine A by using SSMS and executing query of C; Secondly, you connected from A to B as your domain credential, and B connect to C using same credential based on your configuration. But if you kept the connection from A to B for a long time, your domain credential catche on B probably expires after a period of continuous use.

    Possible resolution:

    1) If your OS is WIN2K and XP, please refer follow KB artical

    818173 Authentication May Intermittently Fail
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;818173

    2) If not 1), on your machine B, please configure a new linked server by specifying the DSN equals C, and explicitly specify a sql login name and pwd in security instead of using current login credential.

    If you still face the problem, please let us know your configuration and how you make connection, if possible, attach any info in the server log.

    Thanks for your comments!
    Ming.
  • Hi, my SQL Agent 2005 service won't start. I cannot connect to the Profiler (2005) either. But both the SQl Server 2005 and sqlbrowser services are running and active. I have Sql Server 2000 on the same machine as the default instance and the SQL Server 2005 as the named instance wiht name "SQL2005".
    The error log from starting sqlagent is the following:
    [298] SQLServer Error: 21, Encryption not supported on the client. [SQLSTATE 08001]
    [298] SQLServer Error: 21, Client unable to establish connection [SQLSTATE 08001]
    [165] ODBC Error: 0, 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. [SQLSTATE 08001]
    [000] Unable to connect to server '<servername>\SQL2005'; SQLServerAgent cannot start
    [298] SQLServer Error: 21, Encryption not supported on the client. [SQLSTATE 08001]
    [298] SQLServer Error: 21, Client unable to establish connection [SQLSTATE 08001]
    [165] ODBC Error: 0, 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. [SQLSTATE 08001]
    [382] Logon to server '<servername>\SQL2005' failed (DisableAgentXPs)
    [098] SQLServerAgent terminated (normally)

    If I run sqlcmd -S instancename, I got the following error:
    HResult 0x15, Level 16, State 1
    Encryption not supported on the client.
    Sqlcmd: Error: Microsoft SQL Native Client : Client unable to establish connecti
    on.
    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.

    I have both remote connection and tcp/ip enabled.
    Any help is greatly appreciated!

    Steve
  • Hi, Steve

    First, thanks for posting detail info to help us identify possible cause. Secondly, your problem might be either corruption of encryption setting or schannel corruption on your client side box.

    1)Your client side setting probably was messed up. Please go to SQL Server Configuration Manager and click properties for "SQL Native Client Configuration", if correct, you should be able to see two flags, one is "force client encryption", the other one is "trust server certificate" and value were set "yes" or "no". If you see "No properties are available", that means your client registry key was messed up and leads to the connection error . To fix this, suggest reinstall SNAC(SQL Native Client).

    2)If you are sure 1) was not the case, that might be schannel library somehow corrupt on your client box or no encryption support in you OS, and we need to collect more info, eg, see whether there is warning or error in the eventlog 'security'.

    3)Please double check whether there is any error which indicates certificate or encryption in the server error log, and if there is, please let us know what kind of error displayed.

    Most likely, your case is due to 1), to fix the messed up setting on your client, you'd better reinstall SNAC which is in tools part of Yukon installation.

    Wish this help you!
    Ming.
  • Hi, Ming,
    Sorry it took a little while. I wanted to make sure I tried your solutions before responding. Suggestion 1) helped. I did not see those two flags so went right ahead uninstalling and reinstalling the SQL Native Client.
    Both the agent and profiler are now working.
    Thanks very much for the help.
    Steve
  • Hi,
    I am having a SQLNCLI problem similar to  Randy's issue above, but slightly different.

    I have configured a linked server C (SQL2000) on machine B (SQL2005), and I connect to B from machine A by using terminal Server and executing a simple query

    INSERT INTO mydb..mytable -- this is on Machine B
    Select * from C.mydb..mytable where ....

    This is dealing with a data set of about 13 Million and it fails with a timeout error (details at the end of this message) and it fails exactly after 10 minutes.

    Summary - Scenario 1:
    A ->termServ->B->Run distributed query involving C (in management studio) or execute SSIS package. -- Failure either way after exactly 10 minutes.

    I have this same operation going on on a different machine D, where this is part of an "Exec SQL task" inside a SSIS package.
    And the package is schedule to run nightly and it does this part in about 30 minutes.
    Last week this package started failing after 10 minutes of trying - before last week it was always successful and finished in roughly 30 minutes.

    Summary - Scenario 2:
    D -> Scheduled task involving a pkg doing a distributed query involving C -- Suddenly Started Failing after exactly 10 minutes and continued failing consistently.


    So, I looked around and found a Remote Query timeout setting of 10 minutes on ServerC. I set this to 0 (unlimited), and this fixed scenario 2.
    Scenario 1 continues to fail.

    ======Error===========================
    OLE DB provider "SQLNCLI" for linked server "ServerC" returned message "Query timeout expired".
    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider "SQLNCLI" for linked server "ServerC" reported an error. Execution terminated by the provider because a resource limit was reached.
    Msg 7421, Level 16, State 2, Line 1
    Cannot fetch the rowset from OLE DB provider "SQLNCLI" for linked server "ServerC". .
    ======================================

    Any thoughts or help is greatly appreciated.

    Thanks,
    Rajesh
  • Forgot to include my email.
    rajesh_vee@donotspam.hotmail.com

    - email modified to prevent automated spam engines. Please cut out the donotspam part. Thanks.
  • Try setting the Remote Query timeout on server/machine B, or possibly on all servers involved, and let us know whether it makes any difference.  

    FYI, setting the value to 0 (infinity) is not recommended.  Prfeerably set to to a value that is high enough for your purpose.  

    Thanks,
    Peter Gvozdjak

  • I try to connect to SQL Server 2005 using ADO:

    (1)connect to a named instance(mynamedinstance),using SQL Native Client as the driver.

    CString strConn =_T("Provider=SQLNCLI;Data Source=mypcname\\mynamedinstance;Initial Catalog=mydb;User Id=myuser;Password=mypw;");
    _ConnectionPtr conn("ADODB.Connection");
    _bstr_t bstrConn(strConn);
    if( FAILED(conn->Open(bstrConn, _T(""), _T(""), 0)) )
    {...}

    Connect successfully!

    (2)connect to the default instance, using SQL Native Client as the driver, but use DSN in the connection string.

    CString strConn =_T("Provider=SQLNCLI;DSN=myDSN;Uid=myuser;Pwd=mypw;");
    ...
    ...

    Connect successfuly!

    (3)Conncet to a named instance(mynamedinstance), using SQL Native Client as the driver, using DSN in the connection string.

    CString strConn =_T("Provider=SQLNCLI;DSN=myDSN;Uid=myuser;Pwd=mypw;");
    ...
    ...

    Connect error!!!

    Why (3) failed? Please help me.
  • Provider SQLNCLI does not recognize DSN. So, in the second case, the DSN=myDSN is ignored and, possibly,  connection successfully connect to local default instance. In the third case, the DSN=myDSN is ignored as well and connection cannot be established for named instance.

    So the short answer is “Do not use DSN when using SQLNCLI provider”.
  • Hi guys, Im getting this connection 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)]

    , but only when i move the application from my local machine (which connects fine) to a IIS server. What can the error be?
  • Hi,

    the problem that is driving me nuts is this:

    Error Message 5:

    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)

    1) You might have named pipe or tcp enabled and connect to a named instance, but SQL Browser service was not started or enabled. To enable browser, First, Use net start or go to sql configuration manager(SSCM), check whether sqlbrowser service is running, if not, start it; Secondly,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.

    =====

    I have enabbled and activated sqlbrowser but  still get the error: 26 as above.

    When I am running the >sqlcmd utility, connecting and quering the DB work fine.

    Is there any other cause and solution to the error: 26 other than the above explanation. All the resources I found online say the same thing, which obviously is not good enough for me.

    Regards,

      Agron
     
  • Sorry, I forgot to mention that my Application is not trying to access the DB remotely. The app and the DB are located in the same computer.

    The connection string is the following:

    <add key="connStr" value="data source=AGRON\SQLEXPRESS;initial catalog=INVENTORY;integrated security=SSPI;persist security info=False;workstation id=AGRON;packet size=4096;" />  

    Regards
Page 1 of 15 (217 items) 12345»