Scenario : I have a .NET 1.1 console application that connects to a SQL 2000 database, queries some data and spews it out to a text file. This is successfully running on both Windows 2000 Server and Windows 2003 server. I decide to migrate the project to .NET 2.0, so open the project in VS2005, use the conversion wizard and rebuild. The newly built app works fine on Windows 2003 but not Windows 2000, where I get a connection timeout error. The .NET 1.1 version of the app still works fine on both boxes. I can also create a DSN to the database from both boxes that connects successfully. If I change the connection string in the .NET 2.0 app to use the IP address of the database server rather than its friendlier computer name, I still get a connection timeout error.
Diagnosis : The .NET 2.0 app is failing to make a connection to the database through either TCP or Named Pipes.
Comments : A lot of blog posts and indeed blogs have dedicate themselves to diagnosing connection issues between .NET 2.0 apps and SQL 2005 but the same principles apply here. The fact that the .NET 1.1 app and DSNs work fine indicate it's a client-side connection issue rather than one on the server-side. If you didn't know that though, you might want to check out the following issues.
- Firewall : Client and database should be communicating on port 1433 and 1434, so make sure that these are open from the client side. If you haven't got a tool that can do this already, Microsoft make available a small port scanner called PortQry and, if you don't like command line apps, a GUI frontend called PortQryUI which automatically runs scans on common port groups for certain tasks. It's not brilliant and you could use nmap or something similar if you want but it does the job.
- Server Config : Unless you've deliberately switched them off, SQL 2000 will be listening via shared memory protocol (used only when you reference a database using (local) or (local)\instancename), named pipes and TCP. In SQL Enterprise Manager, right click the database you're trying to connect to and select properties. The General tab of the ensuing dialog will have a button named Network Configuration which you should click to brign you to the SQL Server Network Utility window where you can check what protocols are enabled and how they are enabled. Assuming both named pipes and TCP are up and running, hit properties for each and record the name of the pipe and the tcp port being used for comms to compare against your client config.
- Client Config : Your first port of call should be the connection string. Make sure it is correct. If it's not that, run cliconfg.exe from the command line. This application is the client-side equivalent of the Server Network utility. .NET 2.0 seems to need more clarity when it comes to connections than .NET 1.1 especially with pipes, so there's now a handy guide at http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q328306 to work through and see if you can debug the problem yourself. Remember to compare client settings with those you records from the server. You could also check your installation of MDAC for inconsistencies using the guide at http://support.microsoft.com/kb/307255/.
If you can't figure out the problem from here, you can also try the forums at SqlJunkies.com, the microsoft.public.sqlserver.connect newsgroup and the SqlProtocols team blog, all of which I used to trace down my own problem and are well worth a read as well.
My own solution was finally reached by building a clean Windows 2000 VPC, making sure I could run the same .NET 2.0 app in that and comparing client settings. Sure enough, the named pipe on the Win2k box was incorrect as can often be the case for access to a clustered database, and my TCP setting was also incorrect via an alias built to the database. I'm still not sure why the .NET 1.1 version of this app continued to work despite this, but there you go. Hopefully if you come across a similar problem, you'll at least have a head start on its diagnosis and solution.