ID #1049

What should I do to resolve SQL Server 2005 / 2008 connectivity issues?

Description: When you start FusionRetail 6 and if it fails to connect to SQL Server 2005 / 2008. This also partly applies, if you get the message “Source: RanceLab® FusionRetail 6: Message: Unable to connect with Database Server at this moment”.

 

Basic Understanding:

  • In any case FusionRetail 6 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.
  • If FusionRetail 6 fails to connect SQL Server, the fastest way is to first try SQL Server Management Studio to connect to SQL Server using the exact same connection string used in FusionRetail 6. 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 is creating the problem.
  • 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. For the OS errors you can use "net helpmsg" to check specific OS info.


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: Sql Server is not running

    1. Make sure your sql service is running.
    2. Start the server if it is not running.
    3. If server start fails, go to ERRORLOG to see what happened there, fix the problem and restart server. You can use the following methods
      • Start > Run > type services.msc and press enter
      • Use "sc query”  |  Start >Run > CMD ; type sc query mssql$rancelabsse
      • Use "net start" | Start >Run > CMD ; type net start mssql$rancelabsse

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.)

Troubleshoot: Server naming

    1. This may occurs if you have used FQDN (Fully Qualified Domain Name,  Loopback IP or IP Address as server name.
    2. To resolve please use the <machinename\instancename> format. For example server\rancelabsse where server is the machine (computer) name and rancelabsse is the SQL Server Instance Name.

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: SQLBrowser service is not started. To resolve this use one of the following

  • Start > Run > type services.msc and press enter – find SQLBrowser and start it
  • Use "net start" | Start > Run > CMD ; type net start SQLBrowser

 

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: TCP/IP Protocol is not enable or TCP Port is not default.

  • You are not using the default port. To resolved this go to Start > Program > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager.  Now in SQL Server 2005 Network Configuration choose "Protocols for RanceLabSSE" and click properties for TCP/IP. Make Sure that the TCP Port field is BLANK.
  • Either server is not started or not listening on TCP/IP, to resolve enable TCP/IP protocol in SQL Configuration Manager.

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)

Troubleshoot: SQL Browser service is either not started or started but not active.

  • To enable browser, use net start SQLBrowser or go to SQL Server Configuration Manager(SSCM), check whether sqlbrowser service is running. If not start it.
  • You still need to make sure SqlBrowser is active. Go to SSCM, click properties of sqlbrowser service -> Advanced-> Make sure that Active field is set to Yes.

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)

Troubleshoot: SQL Browser service or SQL Server is not started.

  • To enable browser, use net start SQLBrowser or go to SQL Server Configuration Manager(SSCM), 

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)

Troubleshoot: Named Pipe is not enabled on the remote server

  • Go to SQL Server Configuration Manager(SSCM)
  • Make sure the following are enabled
    • Shared Memory
    • Named Pipe
    • TCP/IP
  • and restart instance

Error Message 8:

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-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.)

Troubleshoot: Due to connection blocked by Windows firewall. To resolve this, take follow steps:

    1. Enable SqlBrowser
    2. Add SqlSERVR.exe into Firewall exception list
    3. Add SqlBROWSER.exe into Firewall exception list
    4. Add Tcp port to Firewall exception list. (eg, Name-1433:TCP, Value-1433:TCP:*:Enabled:Tcp 1433). 
    5. For more info on Windows Firewall - see http://support.microsoft.com/default.aspx?scid=kb;en-us;287932 If you are using some third party firewall then contact the vendor for more detail.

 

Error Message 9:

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)

OR

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)


Troubleshoot: There can be many reasons. The following might help.

    1. Please check if the allow remote connection is set to yes
    2. Please check the password of SA is set to blank. If not then use the password which you have created.
    3. You might have the following entry in C:\FusionRetail6\AlterLogin.Log “Login failed for user 'Server\Administrator. Reason: Server is in single user mode. Only one administrator can connect at this time.”

 

 

Acknowledgment:  For more information on SQL protocol and connectivity, please visit

  1. http://blogs.msdn.com/sql_protocols/default.aspx
  2. http://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx
  3. http://blogs.msdn.com/sql_protocols/archive/2008/04/30/steps-to-troubleshoot-connectivity-issues.aspx

Tags: Message, SQL server, System Message

Related entries:

You cannot comment on this entry