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 5 and 7 and type the answer here:
  • Post
  • hello,

    i have this connection string in web.config file

    <add name="mycon" connectionString="Server=localost; Database=\db\test.mdf;uid=&quot;sanjay&quot;; pwd=&quot;rpg007in&quot;"

    providerName="System.Data.SqlClient" />

    but in the above connection string i get error

    Cannot open database "\db\test.mdf" requested by the login. The login failed.

    Login failed for user 'snajay'.

    everything in my code is same and its working properly on y local machine but on shard hosting service i am getting this error.

    only change is above conenxcion string.

    my databse was created on my local machine i used ftp to copy it on srver created a db folder under wwwroot while my code behind file is in subdomain

    i am desperately looking for help

    after two days of search i came across ur blog and i finally feel that this is where i will get solution.

    email candychip [at ]gmail.com

  • Objective: My source database is internet database server which is located by a Ip address.  My

    objective is to insert some data from internet database to local machine. Internet database has Windows

    2003 standard Os, and sqlserver 2005 database server and local machine has Windows 2000 standard OS and

    sqlserver 2000 database server. So I write a store procedure in internet database as follows

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

    exec sp_configure 'remote admin connections',1

    reconfigure

    INSERT INTO [dba\somnath].Webpatentfiling.dbo.patentInventor1

                 (INV_NAM,INV_ADD1,INV_ADD2,INV_ADD3,NATIONALITY )

           select INVENTOR_NAME,INVENTOR_ADDRESS,

                  INVENTOR_STATE,INVENTOR_COUNTRY,INVENTOR_NATIONALITY

           from OPENDATASOURCE(

            'SQLOLEDB',

            'Data Source=123.456.78.555;User ID=sa;Password=mfklj4Y'

            ).Efiling.dbo.TBL_APP_INVENTOR

            where APP_ID='7'

    At the time of execution the following error msg come

    OLE DB provider "SQLNCLI" for linked server "dba\somnath" returned message "Login timeout expired".

    OLE DB provider "SQLNCLI" for linked server "dba\somnath" 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 65535, Level 16, State 1, Line 0

    SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

    So give me a solution

    - Somnath Dey

  • PingBack from http://nayeli.linkmediavideo.info/error17054severity16state1sqlserver2005.html

  • Can anyone tell me what is the caused of below. I am facing this error, not always....sometime only...

    My production is running Windows Server 2003 and SQL Server 2005.

    Source = .Net SqlClient Data Provider

    Error = 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.) |    at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

      at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

      at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

      at System.Data.SqlClient.SqlConnection.Open()

  • Hello,guys,

    I create a package SSIS locally, and there's a SQL task that executes a stocked procedure in ServerA, whoes domain name is AA, and the procedure selects items from a table in ServerB, whoes domain name is BB. The package SSIS failed to execute, and the error message goes like this:

    [Execute SQL Task] Error: Executing the query "exec Procedure_Test" failed with the following error: "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Can anyone tell me how to solve it, please? By the way, I used integrated authentication for the oledb connection.

    Thank you in advance.

  • i have problems connection to my SQL on the server, but not from my remote PC. The error is:

    A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server, Error: -2146893019)

    I can pay $100 if you can fix it

    trakhtenberg@stanford.edu

  • The problem seems to be with the SSL certificate being used by the server.  My guess is:

    1) Your server is loading a self-signed certificate

    2) Your server is not enforcing encryption on inbound connections.

    3) Your remote client machine is not enforcing encryption on outbound connections.

    4) Your server IS enforcing encryption on outbound connections. (this is the source of your problem)

    Since neither side enforces encryption with the remote client, it doesn't need the server to have a trustworthy SSL certificate.  But, since the server, when opening an outbound connecting to itself, demands encryption, the server needs to have a trustworthy certificate - it tries to use the self-signed cert, and you get this error.

    If that is the problem, here is a simple solution: disable client-side Force Encryption on the server.  On the machine that runs the SQL Server instance, open up the SQL Server Configuration Manager, right-click SQL Native Client Configuration, and set Force Protocol Encryption to No.  Then try connecting locally.

    Hope that helps,

    Dan

  • on attempt to login to SQL Server 2005 sp2, standard edition in SSMS OR start  in services, resulted in the following:

    "TITLE: Connect to Server

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

    Cannot connect to IBMSERVER.

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

    ADDITIONAL INFORMATION:

    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) (Microsoft SQL Server, Error: 2)

    I'm fairly new at this and have spent over 12 hours wading through technet articles that don't apply or don't resolve the issue.  SS is configured to listen at port 1433, but if the service won't start, it can't listen, right?

    More details:

    System was working until a couple of days ago, when my external app that access SQL couldn't connect to the db. Attemted to login to SSMS under user sa (SQL authentication), but got the above error message.  Attempts to login w/ Windows authentication also failed.  

    SQL Server 2005 is running under Windows Server 2008 on Virtual PC 2007 with all latest updates.

    My busines depends on this working, & I don't know where to go from here.  Can some bright guru provide some direction on what to check next?

    raymillr[AT(change to @)]gate.net

  • Hi Ray,

     From the error message and the scenario you describe, I expect that your SQL Server service isn't started (you mentioned something about it not being started as well).  You can start the service from services.msc.  It will be named something like "SQL Server (MSSQLSERVER)" (if it is a named instance, replace MSSQLSERVER with the name of your instance).

    If your service won't start, post the error message you receive when you try to start it, and if there is a new ERRORLOG file generated at %ProgramFiles%\Microsoft SQL Server\INSTANCE\MSSQL\Log\, get that and post the errors from the ERRORLOG file.

    Also, just FYI, you'll generally get a better/faster response on the SQL Server Data Access forum (http://forums.microsoft.com/msdn/ShowForum.aspx?siteid=1&ForumID=87) than on our blog.

    Hope this helps,

    Dan

  • I am developing an application in VB 2008 which uses SQL Server 2005 database. The application is running perfectly well on the development server and I can execute my options and update tables like Product Master, etc. When I build the project and install it on another machine, I get the following error:

    "SQL Server does not allow reote connections. (provider SQL Network Interfaces. error 26 - Error locating server / instance specified)"

  • Prasad,

    Can you make sure your connection string is updated correctly on the new machine? Please refer to

    http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

    for the specific error.

    Thanks,

    Xinwei

  • I am a newbie. How do I update the connection string.

    I am well and truly stuck.

    Thanks.

  • Thanks, it solved my "error 26"

  • Hi,

         I have installed SQL Server 2005 on a Vista Business. I am able to connect to SQL Server instance on Vista machine from Management Studio on other machine (Running on Xp).

         But I am not able to connect to Analysis Services default instance on Vista Business machine from Management Studio on other machine (Running on Xp).

         I did all settings with Vista firewall exceptions, SQL Surface area configuration, Security, everything.

         Please tell me do I need to modify something else on 'Vista Business'.

         Thank you.

  • I have problem in connecting to remote SQL Express instance, when Windows firewall is enabled:

    1. It's a Workgroup environment

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

    3. I can connect when I disable the Windows firewall

    4. Port 1433 is added in exceptions

    5. SQlserver.exe and SQLbroswers are added in execeptions

    What am I missing?

    Thank you

Page 11 of 15 (217 items) «910111213»