SQL Server Express Edition and Dynamic Ports

by Jon Davis 17. May 2009 14:13

While transferring to my new web host, I ran into a roadblock that had me stalled for a day or two while I tried to figure out what was going wrong. I like to use my own laptop here at home when managing SQL stuff, so I poked a special hole in the Windows Firewall on my new VPS instance to just allow my IP on port 1433, while enabling TCP/IP for SQL Server and restarting the service. But I could not connect!

I spent several hours poking at it, rebooting it, turning off the Windows firewall completely (temporarily), trying to get connected to that darn SQL Server instance I had installed on my new VPS, but it just would not connect. Then I noticed that the client tools (namely SSMS) on the VPS itself could not connect to ITSELF on the TCP/IP stack, it could only connect on Shared Memory or Named Pipes. What is going on?!! Could it be a bad OS image on the VPS?

Eventually it got resolved. The VPS hosting company was very helpful in assisting me on the matter, and at the same time I got some replies on a SQLServerCentral.com forum post that narrowed down to the same problem: Dynamic Ports.

This SQLServerCentral.com forum reply was the critical new knowledge for this weekend.

Hello Jon,

SQL Server Express (2005 & 2008) defaults to Dynamic Ports, whereas the Default Instance of other Editions listen on Static Port 1433 (by default). 

Having a Zero in the Dynamic Port configuration will have been overriding the Static Port that you entered, and therefore causing your connection problems.

Regards,

John Marsh 
 

I'd never heard of dynamic ports before. And the reason why I've never run into this issue before was because I've always used the full version of SQL Server that comes with MSDN licenses, and that version, by default, does not use dynamic ports by default, it uses Port 1433, which I expected SQL Server to default to in my case. But for both SQL Server 2005 and SQL Server 2008, the Express edition defaults to use Dynamic Ports when TCP/IP connections are enabled. So all my setting of Port 1433 in the Configuration Manager was completely ignored because it switched right back over to Dynamic Ports. To make things even more confusing, to enable Dynamic Ports, the setting is set to '0'. That's '0', as in the programmatic standard for FALSE, just not in this case, as is thoroughly documented in SQL Server documentation. Heh heh.

So with the Dynamic Ports' setting of '0' removed, I'm up and running. And now this blog (not to mention other, more legitimate sites I run [sic?]) is finally (as of Sunday afternoon) moved off my home PC and onto my new VPS.


 

Powered by BlogEngine.NET 1.4.5.0
Theme by Mads Kristensen

About the author

Jon Davis (aka "stimpy77") has been a programmer, developer, and consultant for web and Windows software solutions professionally since 1997, with experience ranging from OS and hardware support to DHTML programming to IIS/ASP web apps to Java network programming to Visual Basic applications to C# desktop apps.
 
Software in all forms is also his sole hobby, whether playing PC games or tinkering with programming them. "I was playing Defender on the Commodore 64," he reminisces, "when I decided at the age of 12 or so that I want to be a computer programmer when I grow up."

Jon was previously employed as a senior .NET developer at a very well-known Internet services company whom you're more likely than not to have directly done business with. However, this blog and all of jondavis.net have no affiliation with, and are not representative of, his former employer in any way.

Contact Me 


Tag cloud

Calendar

<<  November 2018  >>
MoTuWeThFrSaSu
2930311234
567891011
12131415161718
19202122232425
262728293012
3456789

View posts in large calendar