Enabling Remote TCP/IP Access to SqlExpress 2008
So it happens that SqlExpress is frequently suitable for most small to medium sized app development. Now it does lack some of the advanced features of course that come in $other$ editions. Out of the box, however, since it’s intended for use on a single machine, there are issues someone building small distributed, network enabled apps might have to deal with.
- Remote Networking is not enabled
- SQL Browser service is not typically enabled
- Firewall isn’t configured by default to allow connections
No problem. We’ll simply re-configure it so that you can now connect from remote machines.
Enabling Remote Networking
Relax, this is easy.
- Start SQL Configuration Manager
- Expand SQL Server Network Configuration
- Select the Protocols for (SqlExpress instance) node
- Set TCP/IP to Enabled
- RightMouse over the TCP/IP protocol and choose Properties
- Select the IP Addresses tab
- Scroll to the bottom of the list for the IPALL settings.
- Clear out the value in TCP Dynamic Ports so that it is empty
- Add a port number for TCP Port.  1433 is a decent number to use here unless you have other instances of SQL Server. I sometimes use 8484 also because it’s easy to remember.
- Apply these changes.
So, now this Instance of SQL Express allows connections via TCP/IP. That’s not the whole story…
Configure SQL Server to allow Remote Connection
The next step here is to tell SQL Server that remote connections to this instance are Allowed. We do this simply through SQL Server Management Studio. So, this brings up an interesting point. You really really want to have this tool installed on the machine where your SQL Server is running (when you’re doing your development). There are other ways to accomplish this, but this is the easiest way for those visually inclined.
So, now that Remote connections are allowed, and the TCP/IP port is assigned and enabled.
Ensuring that the SQL Browser Service is Running
The final step is to ensure that the SQL Browser service is configured to start and is running so that the instance can be found remotely. Maybe someone can tell me whether this is always required, or whether ‘it depends’… :)  If it isn’t always required, what conditions determine that?
Anyway, there’s a problem I’ve encountered several times but not always when installing SQL Express 2008.  Normally the SQL Browser service is not configured to run when only SQL Express is installed. You may find that in trying to start this service, you get some hokey and completely unhelpful message indicating that it can’t be started when trying to do this from the SQL Server Configuration Manager.
“The service cannot be started, either because it is disabled or because it has no enabled devices associated with it. [0x80070422]”
Well, doh! Trying to enable it, produces this message. This is a totally sweet message only Microsoft could come up with. I’m trying to Start a Service, not connect my iPod.  :) Anyway, the solution is simple. Enable the SQL Browser Service using the old fashioned Control Panel “Services” MMC application, just like you would do if you want to enable/disable some other system service.
This is OS dependent, but essentially, it’s in the machine’s Control Panel -> Administrative Tools -> Services, but since it’s an MMC Snapin, it could appear as the Services node in the Computer Management application, or other places. However you get there, the first thing to do is locate and double click on the “SQL Browser Service”. Once there, we need to ensure that the Login is set to use Local System and turn off desktop interaction.
Control Panel Services Login Configuration for SQL Browser Service
Then we go back to the General Tab in the Properties, change the startup parameters to Automatic (or delayed start) if you like and then Start the Service from here. Once you do this, you’ll be able to start/stop it from the SQL Configuration Manager utility without getting those bogus messages about not finding your hair drier or some other useless “device”.
Control Panel Services Configuration for SQL Browser Service