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

    I have recently installed SQL Express on my Web Server. I am running .Net 1.1. I am using the ADO.NET SqlClient. The SQL Server and the Web Server are on the same machine.  Only local connections and shared memory are enabled.  I have no trouble connecting to the server via Management Studio or sqlcmd.  When I attempt to connect via ADO.NET, I receive a SQLException which says simply "Invalid Connection".  This is the connection string:

    Server=.\SQLEXPRESS;Database=SomeDb;trusted_connection=sspi;

    What am I doing wrong?  I would prefer not to enable remote connections or other protocols if possible.
  • Hi,

     The error you saw indicates problem inside your client app,since you can connect through Whidbey and SNAC.
     If possible, could you provide your application? Or could you try follow script and see what happens?
    ***********************************
    using System;
    using System.Net;
    using System.Data.SqlClient;

    namespace Sqlclient
    {
    /// <summary>
    /// Summary description for Class1.
    /// </summary>
    class LPCPrefixFallback
    {

    /// <summary>
    /// The main entry point for the application.
    /// </summary>
    [STAThread]
    static void Main(string[] args)
    {
               string strConn = @"Server=.\SQLEXPRESS;Trusted_Connection=TRUE;";

    SqlConnection myConn = null;
    //
    // TODO: Add code to start application here
    //


    Console.WriteLine("Conn WIHOUT PRIFIX...");

    try
    {
    Console.WriteLine("connection {0}",strConn);
    myConn = new SqlConnection(strConn);
    myConn.Open();
    SqlCommand curComm = new SqlCommand("select net_library from sysprocesses where spid=@@spid", myConn);
    string netLib = (string) curComm.ExecuteScalar();
    Console.WriteLine("Net library: " + netLib);
    }
    catch (Exception ex)
    {
    myConn.Close();
    Console.WriteLine("ex->" + ex.Message );
    return;
    }
    }
    }
    }
    *************************************

    Thanks!
    Ming.
  • Also, please try "odbcad32.exe" through command line, and type ".\SQLExpress" as server name, if connection works, that means your .NET app has problem.


    Thanks!
    Ming.
  • Hi,
    I've got a connectivity issue, on a server that is running both SS2000 and SS2005.

    Each has a named instance.

    * The client application can not connect to the SS2000 named instance. This is the problem.
    * I can access the instance remotely, but only if I create an alias on the client (in IP, Ispecifying the port of the instance)
    * The client application can not connect reliably using an alias. (sometimes it works...)

    The problem seems to be SS2000's SQL Server Browser service, which keeps "terminating unexpectedly" (messages in the System Log) and gets re-started every 60 seconds.

    I have no idea why this service is crashing. I guess it is needed to ensure visibility of the named instances, in this co-existence scenario.

    So, I guess I need some clues on how to troubleshoot the browser service, or else a workaround for the instance visibility. ...???
  • wewe
  • Hi,

    I've installed SQL Server 2005 Exprees on my PC and I keep getting this error when trying to connect to a database:

    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)

    I went through the steps to try and correct it, including others, and I still am unable to connect
    .
    I am able to connect to the sever using the Management studio express

    Any help would be greatly appreciated

    Here's the error log:


    2006-05-13 02:22:40.69 Server      Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
    Apr 14 2006 01:12:25
    Copyright (c) 1988-2005 Microsoft Corporation
    Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 2)

    2006-05-13 02:22:40.69 Server      Error: 17054, Severity: 16, State: 1.
    2006-05-13 02:22:40.69 Server      The current event was not reported to the Windows Events log. Operating system error = 1502(The event log file is full.). You may need to clear the Windows Events log if it is full.
    2006-05-13 02:22:40.69 Server      (c) 2005 Microsoft Corporation.
    2006-05-13 02:22:40.69 Server      All rights reserved.
    2006-05-13 02:22:40.69 Server      Server process ID is 3780.
    2006-05-13 02:22:40.69 Server      Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
    2006-05-13 02:22:40.69 Server      This instance of SQL Server last reported using a process ID of 2928 at 5/13/2006 2:22:33 AM (local) 5/13/2006 6:22:33 AM (UTC). This is an informational message only; no user action is required.
    2006-05-13 02:22:40.69 Server      Registry startup parameters:
    2006-05-13 02:22:40.69 Server       -d c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
    2006-05-13 02:22:40.69 Server       -e c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
    2006-05-13 02:22:40.69 Server       -l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
    2006-05-13 02:22:40.69 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
    2006-05-13 02:22:40.69 Server      Detected 2 CPUs. This is an informational message; no user action is required.
    2006-05-13 02:22:40.88 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
    2006-05-13 02:22:40.90 Server      Database mirroring has been enabled on this instance of SQL Server.
    2006-05-13 02:22:40.90 spid5s      Starting up database 'master'.
    2006-05-13 02:22:41.02 spid5s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
    2006-05-13 02:22:41.15 spid5s      SQL Trace ID 1 was started by login "sa".
    2006-05-13 02:22:41.16 spid5s      Starting up database 'mssqlsystemresource'.
    2006-05-13 02:22:41.19 spid5s      The resource database build version is 9.00.2047. This is an informational message only. No user action is required.
    2006-05-13 02:22:41.38 spid5s      Server name is 'BLEU-13\SQLEXPRESS'. This is an informational message only. No user action is required.
    2006-05-13 02:22:41.38 Server      Virtual Interface Architecture protocol is not supported for this particular edition of SQL Server.
    2006-05-13 02:22:41.38 spid8s      Starting up database 'model'.
    2006-05-13 02:22:41.52 Server      A self-generated certificate was successfully loaded for encryption.
    2006-05-13 02:22:41.54 Server      Server is listening on [ 'any' <ipv6> 1027].
    2006-05-13 02:22:41.60 spid8s      Clearing tempdb database.
    2006-05-13 02:22:41.82 spid8s      Starting up database 'tempdb'.
    2006-05-13 02:22:41.88 spid11s     The Service Broker protocol transport is disabled or not configured.
    2006-05-13 02:22:41.88 spid11s     The Database Mirroring protocol transport is disabled or not configured.
    2006-05-13 02:22:41.88 spid11s     Service Broker manager has started.
    2006-05-13 02:22:42.32 Server      Server is listening on [ 'any' <ipv4> 1027].
    2006-05-13 02:22:42.32 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].
    2006-05-13 02:22:42.32 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].
    2006-05-13 02:22:42.32 Server      Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
    2006-05-13 02:22:42.32 Server      The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
    2006-05-13 02:22:42.32 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.
    2006-05-13 02:22:42.33 spid5s      Starting up database 'msdb'.
    2006-05-13 02:22:42.57 spid5s      Recovery is complete. This is an informational message only. No user action is required.
    2006-05-13 02:24:02.45 spid51      Starting up database 'ReportServer$SQLExpress'.
    2006-05-13 02:25:02.40 spid51      Starting up database 'ReportServer$SQLExpress'.
    2006-05-13 02:26:02.40 spid51      Starting up database 'ReportServer$SQLExpress'.
    2006-05-13 02:26:02.72 spid52      Starting up database 'ReportServer$SQLExpressTempDB'.
    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.
  • Hi,

     1) You were making local connection to SQLExpress, noticed it is a named instance, hence you should specify instance name in your connection string, eg:
    "Provider=SQLNCLI;Server=<MachineName>\SQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI "; What does your connection string look like?

    2) You were connecting through SqlClient provider,please do:
    "sc query sqlbrowser", make sure sqlbrowser is running.

    3) go to "..\programe files\microsoft sql server\90\tools\binn", run "osql /S \\.\pipe\SQLLocal\SQLEXPRESS /E", can you connect?

    If you still face problem, please follow the guidline and provide more detail info, especially how you make connection in your case:

    http://blogs.msdn.com/sql_protocols/archive/2006/04/21/581035.aspx

    Thanks!
  • Hi, I am getting the following error when ever I am trying to connect to sql server 2005

    "An error has occured 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) (.Net SqlClient Data Provider)"

    I tried to ping the server "ping xx.xxx.xx.x" and it works fine, but however when I try to "telnet xx.xxx.xx.x 1433" this however fails.  When I log on into the physical box where SQL server is installed then I can connect, however if I try to connect from another machine, this error happen, Can any one please shed some light on this error, I am sure its some setting being incorrectly set, Arrrrrrrrrrrrrrrrhh
  • Hi, Ricky

       1) Did you install SQL Express 2005? if so, please how to enable remote connection according to below blog:

    http://blogs.msdn.com/sql_protocols/archive/2006/04/21/581035.aspx

       2) You can not telnet indicates that either you did not enalbe TCP or server did not listen on port 1433, it depends on whether you install sqlserver as a default instance and named instance, if it is named instance, please first, enable sqlbrowser services; secondly, enalbe named pipe and tcp and restart sql server make sure it started, check which port sql server is listening on, then try telnet by specify the port.

    3) double check your connection string, see whether it specify the correct instance, if it is default instance, you can specify the <machinename>, if it is named instance, you need to specify <machinename>\<instancename>.

    Good Luck!
    Ming.
  • Hi...!

    I need to log remotely to the SQL EXPRESS 2005. lets say i dont have any tools like SQL SERVER MANAGMENT STUDIO in da machine which is SQL EXPRESS installed. how can i access it from another machine which has SQL SERVER MANAGMENT STUDIO ?
  • Hi, Ranga

       Please check following blog

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

    There is a section called "Configure Express if you want to mak remote connection."

    Good Luck!
    Ming.
  • Okay, we have a similar problem that I haven't seen specifically mentioned here, so if there's another blog or resource I should be using, please let me know.  Our problem:

    Customers and DB Admin staff can connect to SOME 2005 instances, but not others.  The problem instance will be different for each person having a problem, but others can connect fine to that same instance.  In other words, it appears to be a random thing.  Everybody but X can connect to Server1, everybody but person Y can connect to Server2.  X can connect to all other servers but 1, and Y can connect to all others but 2.  All our SQL instances are set for TCP/IP over a specified port, not the default.  Named Pipes is disabled.

    Any help would be appreciated!


    Jack Burgess
    State of Ohio
    jack.burgess@ohio.gov
  • This is helpful but I still can't get it working. Here's the scenario:

    Server A runnign SQL 2005 Standard (Two instances - 1 and 2).

    Instance 1 hosts many customer databases
    Instance 2 hosts an application db

    I am writing sp's and functions to eventually be called from the application.

    In order for most of them to work I need to have the application pass me the name of the customer database among other parameters.

    THen I want to run a sp (or funtion) on the specified client Db. Example:

    Declare @ExString varchar
    Declare @ClientDb nvarchar(50)
    Declare @Id int
    Declare @TimeZoneOffset varchar

    set @ClientDb = 'CLIENT_DB1'
    set @Id = 123

    set @ExString = 'SELECT Time_Zone_offset FROM ' + @ClientDb + '.dbo.TimeZone_tb WHERE Id =' + @Id

    Execute (@ExString, @TimeZoneOffset OUTPUT)
    AT [Instance1];

    I am trying to execute this from SSMS either on my workstation or on the server itself.

    I have setup Instance1 as a linked server and security is set to "Being Made using this security context:" with a valid SQL user account for the database on Instance1.

    The message returned is:

    Msg 7411, Level 16, State 1, Line 12
    Server 'INSTANCE1' is not configured for RPC.

    My questions are:

    A. Am I approaching this the best way (using execute to pass the client database name? Almost everything I have to write needs to be database non-specific because of the number of client databases. Is there another, better way?

    B. How do I configure RPC so that this will work?

    Thanks for the prompt reply!
  • Got the RPC thing working. It also looks like sp_executesql will solve this issue.

    But one question remains, is this the best way?
  • Hello,

    I have a problem that is disturbing to me.

    I have installed SQL Server 2005 in a W2K3 joined to a workgroup. I'm using a local user account  to execute the service, the Windows Authentication mode and local accounts for users.

    Everything is working, unless I cannot connect from the same machine using FQDN or IP address. ie: from the server I can connect, without problems, using any of this:

    tcp:localhost,1433
    tcp:machine,1433

    but I cannot be connected like

    tcp:machine.domain.com,1433
    tcp:x.y.z.t,1433

    From other computers (using the same user + password) it works using anyone of the options (IP address, short name and FQDN).

    Although I do not understand which is the problem, did not worry to me until I have seen that I cannot setup the Database mirroring, since it gives that error to me.

    Any help would be appreciated

    0) [Server] The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

    ...

    1) [Logon] Error: 17806, Severity: 20, State: 2
    2) [Logon] SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 158.42.250.74]
    3) [Logon] Error: 18452, Severity: 14, State: 1
    4) [Logon] Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: x.y.z.t]
Page 4 of 15 (217 items) «23456»