This morning I've been working with a new managed hosting provider called Rackspace to configure a server for a client. So far, I'm extremely impressed with the response and customer service that I've received from the folks there, but that's a completely different post...

My dilemma this morning has been connecting to the instance of SQL 2005 Express for remote management. When I started this adventure, I was unable to connect to the server using SQL Management Studio, with a .udl file or in my application. I know that the firewall will let me through without restriction (and verified this with Rackspace) so the problem had to be somewhere in the server-side instance of SQL Express.

A little bit of digging on the web pointed me to a couple of solutions that were appropriate for other versions of SQL, but nothing specific to 2k5 until I happened across the post at "The Caseys" (http://caseys.kcfilms.com/2006/01/the_dreaded_dbnetlibconnection.php). I hadn't started going through the Application event log yet, but something in their post encouraged me to do so...

What I found was that even though I had run the surface area configuration tool that comes with SQL Express and told it to allow remote connections, I made the mistake in assuming that SQL Express still uses 1433 for the default TCP/IP port to listen on. Apparently, it does not. When reading through the Application event log, I found that SQL was listening for TCP connections as expected, but it was listening for them on port 1035 instead.

There is another configuration tool that ships with SQL Express (I assume it ships with the full version as well) called "SQL Server Configuration Manager" which allows the customization of the SQL services, network configurations and client configurations.

The solution to my problem was found in the Protocols for SQLEXPRESS --> TCP/IP. Scrolling to the IPAll section showed me the 1035 port number.

Changing this to 1433 allowed my clients and my connection strings to connect as expected.

If you run across the ConnectionOpen(Connect()) error on SQL 2005 Express and you're mostly certain everything else is configured properly, take a look at your Application log to see what port SQLEXPRESS is expecting connections on, and what port you're trying to connect with. From what I've found, changing the value from 1035 to 1433 will permit connections as expected.