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 7 and 2 and type the answer here:
  • Post
  • Correction:

    2. I can connect through remote desktop when firewall is DISabled.

    Therefore its only a forewall problem.

  • its win 2003 64bit cluster with sql 2005 sp2 std ed 64 bit. SQL is running fine on one node but failing on another.. while checking agent seems to have problems.. i have disabled shared memory now but still problem is there. I did enable AgentXps but no luck..

    2008-10-09 00:23:40 - ? [393] Waiting for SQL Server to recover databases...

    2008-10-09 00:23:40 - ! [298] SQLServer Error: 50, Shared Memory Provider: Shared Memory is not supported for clustered server connectivity [50]. [SQLSTATE 08001]

    2008-10-09 00:23:40 - ! [165] ODBC Error: 0, Protocol error in TDS stream [SQLSTATE HY000]

    2008-10-09 00:23:40 - ! [298] SQLServer Error: 50, Client unable to establish connection due to prelogin failure [SQLSTATE 08001]

    2008-10-09 00:23:40 - ! [000] Unable to connect to server '(local)'; SQLServerAgent cannot start

    2008-10-09 00:23:40 - ! [298] SQLServer Error: 50, Shared Memory Provider: Shared Memory is not supported for clustered server connectivity [50]. [SQLSTATE 08001]

    2008-10-09 00:23:40 - ! [165] ODBC Error: 0, Protocol error in TDS stream [SQLSTATE HY000]

    2008-10-09 00:23:40 - ! [298] SQLServer Error: 50, Client unable to establish connection due to prelogin failure [SQLSTATE 08001]

    2008-10-09 00:23:40 - ! [382] Logon to server '(local)' failed (DisableAgentXPs)

    2008-10-09 00:23:41 - ? [098] SQLServerAgent terminated (normally)

  • My new SQL Server 2005 instance shuts down every day at 3:00am but I don't recall ever setting anything to do so. I would prefer not having to manually restart this every morning. How do I correct this?

    All I see in the error log is this:

    2006-05-13 02:30:22.82 spid11s     Service Broker manager has shut down.

    2006-05-13 02:30:22.83 spid5s      SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.

    2006-05-13 02:30:22.83 spid5s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

  • SQL does not have an "auto shutdown" feature.

    I suspect you have automatic Windows Updates turned on any Windows is rebooting.  You can verify by scanning event log (Start| Run -> eventvwr)  If you see a Windows Update event prior to your shutdown that's probably the cause.

    Recommendation is turn off automatic Windows Updates and use download automatically feature (but do not apply updates).  This way you can precisely schedule when to apply updates and reboot.

  • Thanks -- I just checked the Windows Event Log and found that, right after SQL Server shuts down, MSInstaller kicks off and tries to install/update SQL Server automatically (I think). Here are the log file messages at this point:

    * Product: Microsoft SQL Server 2005 -- Install started.

    * Product: Microsoft SQL Server 2005 -- Error 29528. The setup has encountered an unexpected error while Setting Internal Properties. The error is: Fatal error during installation.

    * Product: Microsoft SQL Server 2005 - Update 'GDR 3068 for SQL Server Database Services 2005 ENU (KB948109)' could not be installed. Error code 1603. Additional information is available in the log file C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB948109_sqlrun_sql.msp.log.

    * Product: Microsoft SQL Server 2005 -- Configuration failed.

    * Product: Microsoft SQL Server 2005 Analysis Services -- Install started.

    * Product: Microsoft SQL Server 2005 Analysis Services -- Error 29528. The setup has encountered an unexpected error while Setting Internal Properties. The error is: Fatal error during installation.

    * Product: Microsoft SQL Server 2005 Analysis Services - Update 'GDR 3068 for SQL Server Analysis Services 2005 ENU (KB948109)' could not be installed. Error code 1603. Additional information is available in the log file C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\OLAP9_Hotfix_KB948109_sqlrun_as.msp.log.

    * Product: Microsoft SQL Server 2005 Analysis Services -- Configuration failed.

    * .NET Runtime Optimization Service (clr_optimization_v2.0.50727_32) - Completed all work. Shutting down.

    Does this mean an auto-update flag for SQL Server is set somewhere? If so, why would it be failing every night? Again, I don't remember setting anything to do this but I guess I could have.

  • Ok, I did find the Windows Update section and it was indeed set to download and install every night at 3:00am. I reset it to just download and prompt me. I'm thinking that this still doesn't explain why the downloads fail though. I'll let this run through first and see what happens when I manually intervene going forward. Thanks again!

  • I see the following error in the event viewer of the SQL 2005 server. This is happening every day at between certain time interval. Please help. Thanks in advance

    Server local connection provider has stopped listening on [ \\.\pipe\SQLLocal\MSSQLSERVER ] due to a failure. Error: 0xe8, state: 4. The server will automatically attempt to re-establish listening.

  • It might be the firewall:

    http://dotnetnotforcompletedummies.blogspot.com/2008/12/its-firewall.html

  • Good Day to all,

    I really need your advice guys,

    anyway my problem is that i installed PHP4, MSSQL2005, and using IIS6 in Windows 2003 Server

    I have no problems in the installation, but when i tried to connect to MSSQL an error would appear, error below:

    mssql_connect(): message: Login failed for user 'sa'. The user is not associated with a trusted SQL Server Connection.

    and also this

    mssql_connect(): message: unable to connect to server: KPSWEB1

    KPSWEB1 is the name of the MSSQL Server.

    Please help...

    Thanks

  • MP,

    Do you see any issue with this behavior? It looks like a client made a NP connection to the server after the Pipe is created, but before it's ready to accept connection. When we try to setup things to accept connection, we could re-active the existing client connection and thing would be OK. However, looks like in your case, client closed the connection before everything is ready and we logged this error. The server is robust to create another pipe to accept new connections after this. You can verify if this is the case by using Netmon to see how the NP connection is opened/closed.

    Thanks,

    Xinwei

  • bolivar1985,

    Looks like you have only Windows Auth. Can make sure SQL Authentication is enabled?

    http://msdn.microsoft.com/en-us/library/ms188670.aspx

    Thanks,

    Xinwei

  • Xinwei,

    Thanks, i will try your suggestion...

    Thanks,

    Bolivar1985

  • Xinwei,

    It works, thank you, i just reinstalled MSSQL2005 Standard edition, then chose the mixed mode, and it run, using mssql_connect(),

    Thanks,

    Bolivar1985

  • I have installed SQL server 2005 on my system (Window Vista Home Premium). However, I am getting error messgae when I try to connect. The message is "Cannot connect to ... Login failed for user ... error:18456". I have selected Windows Authentication mode during installation. Can any body help?

  • Hello,

    I'm experiencing an issue trying to setup an ODBC connection on a Windowns 2003 Server(64 bit)using SQL server 2005(64 bit).

    It is a 32 bit application that will be using the ODBC datasource, so I am using the ODBC administrator located in C:\Windows\SysWOW64\odbcad32.exe.

    However, I am receiving the Microsoft SQL Server Login errors indicating:

    Connection failed:

    SQLState: '08001'

    SQL Server Error: 64

    [Microsoft][SQL Native Client]TCP Provider: The specified network name is no longer available.

    Connection failed:

    SQLState: '08001'

    SQL Server Error: 64

    [Microsoft][SQL Native Client]Client unable to establish connection

    Any assistance you can provide would be greatly appreciated.

Page 12 of 15 (217 items) «1011121314»