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

    I am getting this error when trying to connect to SQL server 2008 server

    OLEDB Provider for ODBC Drivers: [Microsoft][ODBC SQL Server Driver]Timeoutexpired

    Any suggestions/ideas, please?

    Thanks

    Helen

  • Guss what, i tried everything i saw for errors 26, 28, 40 when they come to me but as if magically they get solved until i suspected in my ADSL modem that i use for DSL internet and that has in it a switch of 4 ports, so i connected my development computer to a new switch and connected to it also the SQL Server workstation, and voila everything worked perfect from the start, the strange thing that when i was on the old ADSL modem, i can make file sharing and copy files but i can't connect to SQL Server.

    So i suggest that the last step after the above is to check if changing the switch make the connection work or not.

  • Hi,

    I have the Error Message 2. SQL Server was working since about 2 years and this morning it just stopped to work.

    I tried everything, even to uninstall then reinstall but now it can't be reinstalled because I have the same error when I tries to connect to configure the server.

    I really really need help.

  • Hi, I can access my sql server from my laptop, but can not do it from desktop.  Following is the error message.  I turned off firewall on sqlserver and sqlbrower is running.

    Thanks,

    -Benjamin

    TITLE: Connect to Server

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

    Cannot connect to XYCOMVMEPC\SQLEXPRESS.

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

    ADDITIONAL INFORMATION:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

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

    BUTTONS:

    OK

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

  • hi ming.. plz help me i am getting this error.. i am using sql server management express studio.. i was occupied to clear this error for 2 days.. plz help me..

    Cannot open database "cms" requested by the login. The login failed.

    Login failed for user 'SYSTEM2\IUSR_SYSTEM2'.

      thanks

             ranjith

  • TITLE: Connect to Server

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

    Cannot connect to BAO\SQLEXPRESS.

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

    ADDITIONAL INFORMATION:

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol) (Microsoft SQL Server, Error: -1)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

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

    BUTTONS:

    OK

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

  • We have been using SQL 2005 Express for a long time without issue. Today we lost connectivity with the database on our server. After uninstalling SQL and in trying to reinstall SQL I get the following error during database services:

    "SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]SSL Provider: The client and server cannot communicate, because they do not possess a common algorithm."

    I cannot look at surface configuration because there is no connection. What could possibly cause this?

  • Hi SeismoDude,

     The root cause of the problem is in this message: "The client and server cannot communicate, because they do not possess a common algorithm". This is a message from the Windows Schannel security provider, corresponding to error code SEC_E_ALGORITHM_MISMATCH. I would recommend following up on the Windows Security MSDN forum, and describing the error code: http://social.msdn.microsoft.com/Forums/en-US/windowssecurity/threads

    If I had to guess, I suspect that some security policy or update made on your database server changed the list of enabled Schannel algorithms, but I do not know how to configure that list (it is a Windows object, not a SQL Server list); people on the Windows Security forum should know how to make those changes to Windows.

  • I have two SQL 2005 servers on Windows 2008 Exterprise.  I am trying to establish a mirror between the two servers.  I am able to successfully ping both servers by name and IP address. I have turned the Windows firewall off and they are on the same subnet with no firewall between the two devices.  

    However, when I try to establish the mirror, I can connect to both servers, but when the mirror replication starts, it gives an error that it could not communicate with the server.  

    Would this be a shared memory or SQL browser issue?

  • Hi there?

    I have one question.  Is there a problem using server name like server-dc? Becuse I have HP Prolient server gl360 and sbs2008, and sql server 2005 enterprise.  But when i tried to connect using a conneciton stirng server name server-dc it keeps rejecting me.

    Any solution

    Regards

    Abnier

  • Getting the following error and FIPS is NOT Enabled on eiher the server or the client...

    A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The client and server cannot communicate, because they do not possess a common algorithm.)

  • I have installed my system the combination of Windows'2008 , Sql Server 2008 and Classic ASP3.0. I Can connect Asp With sqlserver 2008 Successfully, and I can connect Sqlserver 2008 with Crystal Reports 8.5. But I am not able to Migrate my Reports from Crystal Reports to Sql Server Report services in 2008. So Please help me to resolve the problem.

  • Hi,

    We are about to change the Password for SQL SERVER SERVICE ACCOUNT and we have encrpytion implemented my question is

    > Will this change of password would effect our encrytion keys. If yes what should be done to overcome.

    we have SQL server set on clustered (Active\Passive) Environment.

    Thanks

  • I don't know if anyone's answering questions on here or if everyone's just asking but I've apparently got a new one:

    System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

    Here's the important portion of the Stack Trace:

      at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

      at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

      at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

      at System.Data.SqlClient.TdsParserStateObject.WriteSni()

      at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode)

      at System.Data.SqlClient.TdsParserStateObject.ExecuteFlush()

      at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc)

      at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

      at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

      at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

      at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

      at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

      at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

    This error seems to appear and disappear for a .NET windows service making frequent connections to a SQL Express 2005 database. Anyone know why?

  • I have a customer that is doing a client/server installation using existing SQL express installation.  On the server we have migrated the database and on the workstation we have tried to point to the server DB and get the error “server doesn't exist or access denied”.  I have tried to disable firewall on the server machine, same results.  I even did a full install on the client and tried to use the change database option.  It will see the remote server but when you hit “fetch DB from Server” it gives the same error.  The server is running windows server 2003 and the client machine is windows XP.  Do you have any suggestions on what I can try next?

Page 14 of 15 (217 items) «1112131415