Connection to SQL server 2000 on a windows server 2003 domain controller

0
votes

I am trying to connect an ASP.NET 2.0 application hosted on a client machine to an SQL server 2000 instance which is installed on a domain controller running windows server 2003 (I appreciate this is not best practise, but it is something I cannot change). I am using SQL server authentication, not Windows authentication.

During debugging, the following error is displayed at the point the 'connection' is attempted within the web application (not on running any object within Sql Server):

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

I have done some research, and have checked a variety of things:

1) I am certain the conection string is correct - in fact I built a little Windows app that uses the same connection information and connection succeeds...

2) This is limited to the web application - a windows application running on the remote client machine connects just fine using the same connection string (see 1).

3) The firewall settings on both computers do not prevent the connection (see 1).

4) I can ping the windows 2003 server (see 1)

5) The SQL Server credentials definitely have the rights to the objects needed

6) If I (temporarily) put the web application on the windows server 2003 and run it, it loads and runs fine (using a browser on a separate machine, or on the client machine I am trying to connect) - suggesting this is only an issue with a remote connection, and confirming the connectivity between the machines again, and also confirming the necessary security right of the SQL Server user.

7) I can start query analyser on the client machine and using the same SQL server credentials, can run the same queries on the target database just fine.

Stumped. Please help!

2
asp.netcontrollersql-server-2000dnswindows-server-2003
Are remote connections in SQL enabled? Go into Enterprise Manager, then on the left pane, expand till you see the server instance in question. Right click and then go to Properties -> Security -> SqlServer and Windows should be selected - RogueSpear00
Yes - this was enabled, and also under Connections, Remote connections have been enabled. Whilst checking that, I noticed that the SQL Server is not part of Active Directory. I wonder if anybody knows whether this could be a problem. Furthermore, the client machine is NOT logged into the domain - but I did not think this would be an issue given the other connectivity achieved from the Windows app from the same client machine. - Marco
Are your remote machines on the same subnet as the server? You stated you can ping it - but a ping doesn't necessarily clarify traffic. - RogueSpear00

2 Answers

0
votes

You need to go into the SQL Server Configuration Manager and make sure the Named Pipes option is turned on. It is not turned on by default when you install SQL Server.

0
votes

Because of the research I did in the original question covering the ability of a windows app to connect but not the ASP.NET app to connect from the same machine, and the confirmation the web app was working on the server itself, I was convinced SQL server was set up correctly, that connectivity and firewalls were not a problem - it must have been a difference in the security credentials of the windows app versus the web app.

The windows app runs in the security credentials of the logged on windows user and authenticates using that, whereas the webapp runs in a restricted account and does not pass these credentials over when connecting to SQL server by default. I simply added to web.config, which impersonates the user account within which the web app runs, and it all worked!

Problem solved.