SQL / Asp.net Issue :(?

Question

Web site developed using ASP.Net 2 connects to sql server on a different machine. Problem is that it is able to connect at times and it fails to do so at other times, quoting error 40. We are able to connect to the same instance of sql server from query analyser.

The issue surfaces whether we use sql server 2000 or 2005.

Funny thing is the inconsistency! If it does not connect at all one can check setup or connection string or authentication issues.

Quoting Error:
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)

Answer 1

are you using an IP or machine name in your connection string?



could be a DNS issue if its machine name and it can be resolved due to network settings.



If its IP then it could be security settings (firewall) that may have blocked the sql port 1433?



The other issue is to ensure in the Sql Server Surface Area Config tool you have select "Local and remote connections" and "using both tcp/ip and named pipes" option.

Answer 2

You can use Sql Server Surface Area Configuration Tool which can be found in Start Menu Sql Server Configuration section. By default Sql 2005 indeed refuses remote connections and you need to run this utility to enable remote connections.



The funny thing is that this is a server and this shouldn't be by default settings. I mean, who wouldn't want to access the Server remotely?

Answer 3

Try using the client configuration tool to switch the default protocol to tcp or specify that in the connection string. Named pipes is relying on other servers in the domain to authenticate the user hence it can be more susseptable to failures.

Check your surface area configuration tool in SQL2005 to ensure the correct protocols are allowed.

Answer 4

It would help to know the flavor of SQL you're using, but I'm going to try to stick to transact-SQL witch should be pretty much universal For 1 you don't need to isolate the first 4 numbers, if you did it would be as simple as left(Date,4) but that's not necessary. I'm assuming a simple where statement isn't working because the data is stored as a var char or a char, but then you can just convert. SELECT * FROM EMPLOYEES WHERE convert(datetime,EMPLOYMENTDATE) >= '1/1/1999' #2 SELECT SUM(Salary)/(SELECT count(emloyeeID) FROM department where Department="Department name") FROM Employees inner join department on employees.employeeid = department.employmentid HAVING SUM(Salary)/(SELECT count(emloyeeID) FROM department where Department="DEpartment name") >= 10000 #3 SELECT EMPL.EMPLOYEE_NAME EMPL.SALARY MGR.EMPLOYEE_NAME MGR.SALARY FROM EMPLOYEES EMPL inner join Employees MGR on EMPL.MGR_ID = MGR.EMPL_ID WHER EMPL.SALARY > MGR.SALARY You can send me an e-mail if you have any questions on these.