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 1 and 6 and type the answer here:
  • Post
  • Hi, kihh

       Could you describe more specific about your problem? What if you set max connections to non 0? Are you able to connect to Express successfully? Or you faced some data operation issue? You can try to use same client app against SQL Server Standard or Enterprise or Dev sku, if the problem disappear, that may be caused by Express.

    Following Forum can help you w/ that:
    http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=385&SiteID=1

    Good Luck!
    Ming
  • Hi,
    I get the above mentioned: "Login failed for user ''. The user is not associated with a trusted SQL Server connection." error.

    But I get this error when connecting through a website on an IIS webserver. When connecting through a normal app the connection works fine.
    So the problem seems to be with IIS.

    I have a local account on both machines with the same password. The SQL server is set to mixed mode authentication. I am sure that the connection string is correct.

    The SQL server 2000 is running on a windows 2003 server. I am running win xp and IIS 5.1.

    Any help would be greatly appreciated.

    thanx
    riaan.
  • We testig SQL server 2005 with 20 GB database for check limit of users who can login to SQL server. But we recive an error:
    "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: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
    A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)"

    We change SQL settings (max worker threads from 0 to 2048 (default was 0 - for 64 bit platform and 8 processors is value=576)

    Is this action ok (we use long query)
  • Hi,

     1) Is the error you saw consistently or intermittently? If not consistently, could you reduce the CPU assumption when the problem occured? and see whether that helps?

     2) Can you open SQL Server Profile and trace which client operation trigger the connection closed, did you see any error info in the server error log or system eventlog when problem occured?

     3) Did the problem occure after your adjusting the "max worker thread"? or not? From books online, your configuration of that seems OK based on your system configuration, the problem probably during data operation, client or server close the connection. To open sql trace file and monitor SQL Server error log will help you get clue.

    Good Luck!
    Ming.
  • With shipping SQL Server 2005, we heard from customer feedback about suffering make successful remote...
  • With shipping SQL Server 2005, we heard from customer feedback about suffering make successful remote

  • Hi

    Can you please help me with this problem

    .Following is the problem I am facing when trying to remotely log into  SQL Server 2005

    ·         Got an error when logging in with SA::St0pLand0 – There was no process at the end of the pipe (or some such)

    o   Followed KB info to activate named pipe and TCP/IP access, restarted, getting same error

    ·         After a couple attempts, SA account gets locked out

    o   Run script: Alter Login SA With Password = ‘St0pLand0’ unlock

    o   Indicates success

    o   Access sa login properties under Security\Logins – status area, shows “Login is locked out” checkbox checked.

    ·         Also server seems VERY slow to respond at some points

  • Hi

    Can you please help me with this problem

    .Following is the problem I am facing when trying to remotely log into  SQL Server 2005

    ·         Got an error when logging in with SA::St0pLand0 – There was no process at the end of the pipe (or some such)

    o   Followed KB info to activate named pipe and TCP/IP access, restarted, getting same error

    ·         After a couple attempts, SA account gets locked out

    o   Run script: Alter Login SA With Password = ‘St0pLand0’ unlock

    o   Indicates success

    o   Access sa login properties under Security\Logins – status area, shows “Login is locked out” checkbox checked.

    ·         Also server seems VERY slow to respond at some points

  • Hi, on a web application that I develop, I just upgraded to a new database server running SQL Server 2005 and Windows Server 2003 Enterprise Edition.  The previous database server ran SQL Server 2000 and Windows Server 2003 Standard Edition.  Since this upgrade, the application has been causing "timeout" errors all over the application.  I've been stumped.  Here's the error mssage:

    Microsoft OLE DB Provider for ODBC Drivers error '80040e31'

    [Microsoft][ODBC SQL Server Driver]Timeout expired

    I'm hoping there is some sort of a setting that can be modified to increase the maximum number of connections between the web server and database server.  This is a fresh version of Windows Server 2003 and SQL Server 2005.  Any support that can be provided will be greatly appreciated.

  • I have the same problem that many of the others where i'm getting the error about connecting remotely not allow.  I tried all the solutions, but still have the problem. My issue is just a little different than the others. The program runs fine if I were calling it directly, however, i added a login page and change authentication to "forms". That's when i get the error.  Any ideas?

    Thanks

  • App: Classic ASP

    Migrated from SQL2K -> SQL2K5

    Legacy ConnString

    conn.open

    "Provider=sqloledb;Data Source=CLUSTERSQL,1433;Network Library=DBMSSOCN;Initial Catalog=AjithsDB", "uid", "pswd"

    Still works for SQL2K5. DataShape gives me the following error

    MSDataShape error '80040e14'

    Provider command for child rowset does not produce a rowset.

    So I tried ConnString as follows but it fails

    conn.open "Provider=SQLNCLI;Server=CLUSTERSQL;Database=AjithsDB;", "uid", "pswd"

    Also tried

    conn.open "Provider=SQLNCLI;Server=CLUSTERSQL,1433;Database=AjithsDB;", "uid", "pswd"

    I am not finding any examples of how to specify a port for SQLNCLI provider.

    Please let me know if there is a workaround.

    Thanks

    Ajith

  • Hi Ming,

    I am having a peculier problem when using Site Manager for MCMS 2002 and i am using SQL Server 2005(standard) sitting on other side of firewall.  I am able to access data sizes of 30 MB and below.  When it exceeds, i am getting the following error message:

    Import Preview

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

    Report Information

    Package file: C:\PROGRA~1\MIB84C~1\Server\Temp\sdupload1.sdo  

    Generated on: 11/8/2006 6:33:21 PM  

    Report generated by: WinNT://NYCEDC-BAT-WEB1/MCMSAdmin  

    Client Machine: NYCEDC-BAT-WEB1  

    Client Version: 5.0.5054.0  

    Server URL: http://192.168.71.100:80/NR/System/Marshalling/  

    Server Version: 5.0.5054.0  

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

    Error Information

    An error has occurred that has interfered with the generation of this report. Please try the operation again, and contact your system administrator should this error persist.

    Error ID: -2147352567  

    Source File:  

    Source Method: .Net SqlClient Data Provider  

    Source Line: 0  

    Description: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)  

    Recommended Action:  

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

    Can you help in resolving this error?

    Thanks

    Mani

  • Hi, Mani

       Does this happen consistently or intermittently? The problem looks like either you lost network access to the server temporarily or sql server reject new connection.

       You need first identify whether it is SQL Server issue or pure network issue.

    1) You can check event log SQL Server to see whether transaction log full or server ran out of disk place.

    2) Open SQL Server Profile, start a new trace, redo your client operation, watch what were server doing?

    Or if you think this problem can only occured when you access data size larger than 30MB, please post your question to this forum, provide your detail data operation info.

    http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1

    3) If the error just happen intermittently, it might be due to bad connection, and you can see following KB article to troubleshoot:

    http://support.microsoft.com/kb/325487

    Good Luck!

    Ming.

  • Thanks Ming for your suggestion.  Do you think the firewall plays a part in this?  Is it invalidating the connection?

    Thanks

    Mani

  • Hi, Mani

       Did you enable your firewall during running your client application? What if you turn off firewall, see whether the problem repro?

      BTW, for remote connection troubleshooting, you can see the following blog:

      http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

    Good Luck!

    Ming.

Page 6 of 15 (217 items) «45678»