SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I have come across this error many a times, I thought why people to make struggle to find out the solution over google and then after reading all forums views and then getting into solution.You know sometime you spend much time than expected just to look into solution and then applying it which may work or may not. I found the way how to make someone happy by just providing a quick and relible solution which works for all.

“Since it a common scenario when you work with more than one machine where one is your application sever and another is your database server”. I was getting the below error messages …

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

This error encountered when you put Network Library property with your connectionstring. Adding the network library to indicate that your connection will use TCP/IP and not Named Pipes.


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

Look into the following solution either will surely work for you. The solutions basically depends upon the type of configuration used while installing SQL Server.

Solution 1:
Go to, Start >> Programs >> Microsoft SQL Server 2005 >> Configuration
Tools >> SQL Server 2005 Surface Area Configuration >> Surface Area
Configuration for Services and connections.

Within this check whether "Local and remote connections" is choosen.
If not choose it :)

· Open the "SQL Server Configuration Manager" (under Configuration Tools)

· Expand the "SQL Server 2005 Network Configuration"

· Select the "Protocols for "

· Set the Named Pipes To Enabled

SOLUTION 2:

· Click Start, click Run, type cliconfg, and then click OK.

· In the SQL Server Client Network Utility dialog box, click the Alias tab, and then click Add.

· In the Add Network Library Configuration dialog box, under Network libraries, click TCP/IP.

· In the Server alias box, type the IP address of the computer or the name of the computer that is running SQL Server, and then click OK.

SOLUTIONS 3:

· Enable the TCP/IP protocol using the Surface Area Configuration Utility

· Make sure the TCP/IP protocol is enabled in the SQL Server Configuration Utility

· Make sure the SQL Server browser is started. Note this step is optional. It is possible to set the SQL Server instance to use a fixed IP address - but this is non-standard for named instances

· Make sure SQL Server and SQL Server Browser are exempted by the firewall on the server machine. This is done by putting sqlservr.exe and sqlbrowser.exe as an exception in the windows firewall.

· Note: In order to get things to work. You might need to completely reboot the server machine after making the changes. There have been reports that starting and stopping the SQL Server and Browser software is not enough.

SQL Server common errors and their solutions.

Hi All,

I am just sharing my solutions for the errors which might come to you while dealing with SQL 2005. I come across these issues and solved them by referring forums, some blogs, MSDN. I realised it is also better if we get all our solution at one place instead just getting frustrated when things doen’t work.

Error 1: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)

Solution: This error occurs when firewall on the server refuses the connection. You should be careful and should configure the firewall on the server to accept connections.

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

Solution: There could be many possible reason of the above error.

The firewall on the server if refuses the connection.

If the SQL Server instance name given is not valid.

If the sqlbrower service is not started,

To fix the error you need to appaoch the following actions as part of your solution.

Configure the firewall on the server instance of SQL server to open SQL Server browser port.

Run the server Sql Server browser

Check the Sql Server instance name which you have specified in your connection string.

Enable the Sql Server remote conection using Sql Server Surface Area configuration

Error 3: Case expressions may only be nested to level %d.

Solution: SQL Server 2005 allows only 10 levels of nesting in CASE expressions, So you should be careful while writing a Sql script. You have to reduce the case statements to 10 or less.

Error 4: 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

Solution: This is very common error usually appears for each developer once in a lifetime. You just need to use Sql Server area configuration to accept remote connections.

Error 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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)

Solution: Dear you fogot to start your server, start it now and you are done.

Error 6: Could not obtain exclusive lock on database '%.*ls'. Retry the operation later.


Solution: Sometime a database operation may require exclusive access to it. In that case you need to make sure that the connections to the database is not shared., Make sure you disconnect all the existing connectiosn to that database and trye again, I hope you are done it.

Error 7: Cannot open user default database. Login failed.

Solution: The SQL Server login was unable to connect because of a problem with its default database. Either the database itself is invalid or the login lacks CONNECT permission on the database, Use ALTER LOGIN to change the login's default database. Grant CONNECT permission to the login.

Error 8: Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.

Solution: Starting with SQL Server 2005, The RESTORE VERIFYONLY statement checks the available storage space on the disk to which the database is to be restored.

The available disk space may be insufficient to restore the backup being verified. Restore the backup to a location with sufficient disk space, or provide more space on the disk.

Error 9: The repair statement was not processed. The database cannot be in read-only mode.

Solution: This error happenes when one try to repair the database and the database is in readonly mode. You should make sure that the database also have write access to. To do it set the databasse to read-write by using Alter Database and then return the DBCC command