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 8 and 1 and type the answer here:
  • Post
  • I am trying to connect to a internet based webserver [sql14.aquesthosting.com] using SqlServer 2005 on a Windows XP 64 bit version.

    I have activated remote connections, configured TCP protocol, opened the windows firewall {even turned it off for some tests].  I have spent the better part of a day reading Google and the archives of this [most helpful] blog.

    I originally had the 64 bit version of Sql Server 2005 on this machine, but got the impression that Report Server does not work properly on this version.  I uninstalled SS 2k5 64 bit version, and installed the 32 bit version of SS2k5

    I have a 32 bit tablet computer connected to the same DSL line, with the {nearly as I can tell) same configuration and it connects perfectly -- and quickly.

    Are there any special considerations for connecting with a 64 bit operating system?

  • Hi, Mark

       Could you give more specific error when you connect from 64bit client? It should not make difference compared to 32bit if you

    were making remote connection.

    Good Luck!

    Ming.

  • We are getting the below error in a production environment. What would be the main cause of this issue? The message “Not enough storage is available to complete this operation” does mean the disk space on the server?

    Exception Message: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - Not enough storage is available to complete this operation.) (type SqlException)

    SQL Exception Class: 20

    SQL Exception Number: 14

    SQL Exception Procedure:

    SQL Exception Line Number: 0

    SQL Exception Server:

    SQL Exception State: 0

    SQL Error(s):

    Thanks

    -Sundar

  • Hi, Sundar

       1) Can you check any info in server errorlog when the problem repros?

       2) Can you open sql profile to see whether large or incorrect data operation caused the problem? As far as I know, the error does not necessarily mean run out of memeory.

       3) Can you collect ETW trace and send to us to further investigate?

    Here is a guide to collect ETW trace.

    http://blogs.msdn.com/sql_protocols/archive/2006/08/04/688396.aspx

    Thanks!

    Ming.

  • Hello,

    I am having a somewhat odd problem.  

    I was unable to create a connection to a SQL 2005 instance on a win2K3 server using an ASP.Net Framework 1.1 app running on IIS/Visual Studio 2003 on XP machine.  

    There are both a SQL 2005 and 2000 instance on the server.  The SQL 2005 instance is a named instance.  I decided to try configuring the two instances to listen on different ip addresses and ports as follows.

    SQL 2005

    Server is listening on [ 'any' <ipv4> 2602].

    Server is listening on [ 192.168.2.99 <ipv4> 1312].

    SQL 2000

    SQL server listening on 192.168.2.9: 1433.

    SQL server listening on 192.168.2.99: 1433.

    SQL server listening on 127.0.0.1: 1433.

    By specifying the ip address and port for the server instance in my connection string I can connect to the 2005 instance but not the 2000 instance.

    Connection String:

    Data Source=192.168.2.9,1433;Initial Catalog=DB;Persist Security Info=True;User ID=USER;Password=PASSWORD;Network Library=dbmssocn

    However, I can connect (same username and password) to either instance without error using the Management Studio or the Visual Studio 2005 data connection browser but only if I specify the ip address and port of the desired instance.  If I use the instance enumerated in the drop down, the connection is active refused.

    I am imagining that it must be a kind of tcp/ip conflict.  But I do not know how to resolve this.

    Please advise.

  • One more thing:  I cannot connect to the 2000 instance using Enterprise Manager.

  • No, actually I can register the 2000 instance in Enterprise Manager if I specify the ip address, not the name.

  • I think your problem is the same one I'm having. Using SQL 2005 Mgmt Studio & trying to connect to a 2k DB using the .Net SqlClient Data Provider. Ent. Manager uses a different data provider & I was hunting for a way to set which data provider to use when connecting to various SQL servers. If the host allows the .net data provider you could probably connect to your db, just fyi

  • I am having the same problem as Mani above.  I can connect to my remote SQL Server 2005 just fine, but when I try to add a new table in Management Studio I get a (TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64).

    It doesn't seem like it could really be a connectivity issue as I can access all other parts of the DB both before and after attempting this.  (It does it constantly, not intermittently)

  • I recently upgraded sql express 05 to developer edition (win xp pro laptop).  After struggling to get the upgrade to work, i finally unistalled all 2005 stuff and reinstalled developer edition from scratch using mixed authentication.  Everything was great and i could connect to my databases no problem.  When i started up my pc this morning I cannt start any of the services in the surface area configuration manager (MSSQLServer, SQL Agent, SQL Browser etc).  I have been stuck on this for 4 hours, do you have any advice?  Thanks!  Do I need to change the login info for those services?  How do I do that?

  • Jeff,

    It would be helpful to know if the Windows Event Log has any error events for this problem and what the error message is.

    Were the SQL services configured to run under some user account or a service account (like "Local System", "Local Service", or "Network Service")?  If they were configured to run under a user account, did that user account password expire?

    If you would like to view/modify the account in which these services run under, I recommend using the "Configuration Manager" tool and look under the properties for each of these services.

    HTH,

    Jimmy

  • Hi, Phil

       To resolve your problem, please take a look at following forum post:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=984492&SiteID=1

    Good Luck!

    Ming.

  • Hi all,

    I've got a little problem.

    I'm working on a ASP.NET project. The applications has 4-layers. When I look in the Data Access Layer and test the typed dataset, then the "Preview Data" works fine. I see the right records from SQL Server 2005.

    But when I run the app (default.aspx) in debug-mode, then the system gives the following 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)"

    Please help.....

  • Hi, Johan

       Did this happen intermittently or consistently?

       1) If consistently, have you tried the tips in "Error Message 1" section of this blog? Plus, did you enable sql port in firewall if you were making remote connection?

       2) If intermittently, can you open sql profile and see when the connection failed, is it during data operation? then you need check server errorlog to trace more detail.

    Good Luck!

    Ming.

  • ASP.NET application, Visual Studio 2005 and SQL Server 2005 are on the same development machine (WinXP Pro SP2).

    Does the application use "remote connections" in this configuration?

Page 7 of 15 (217 items) «56789»