SQL Server Express and Remote Access

August 7, 2007 · 17 Comments

What a pain in the ass this has been! I've never dealt with Microsoft SQL Server before but now that Vista runs acceptably on VMware Fusion, I figured that it would be a good opportunity to install SQL Server Express as a test database. The download / install process was not bad but of course the default is that remote access is not allowed. After much Googling and much messing around, I finally got everything working but I feel inclined to comment on some of the web pages out there that provide instructions on how to do this... The correct, detailed instructions were on Microsoft's Support Site but that was not the first result in Google by a long way. All of the top results seemed to have missing or incorrect steps. A couple focused on opening up port 1433 in Windows Firewall. The correct approach is to add sqlservr.exe and sqlbrowser.exe to the Exceptions pane. At least one page focused on enabling access for a specific IP Address (and the instructions surrounding that were very complex!). The key - from Microsoft's own support page - is to use the SQL Server Surface Area Configuration tool which makes life much, much simpler (although certainly not intuitive). If I'd found that page first, I would not have wasted a couple of hours messing with a variety of control panels and utilities. I so wanted to just open up a text file in an editor and fix things - this is exactly why I find Windows so frustrating and like Unix (and Mac OS X) so much better! At the end of the day, however, I have a data source in ColdFusion 8 on Mac OS X which hits SQL Server on VMware! Yay!

Tags: coldfusion · microsoft

17 responses so far ↓

1 Peter Tilbrook // Aug 8, 2007 at 12:38 AM

I am having all sorts of fun (not) under Vista with SQL Server 2005 refusing to update crucial (in typical Microsoft manner) service packs. Looks like a re-install - which will waste hours at least.
2 Peter Tilbrook // Aug 8, 2007 at 1:46 AM

And it seems "un-installation" is more fun-packed than the initial long-winded installation.

Sigh!

At least when everything works it is stable and reliable. Just like ColdFusion 8 has been so far.
3 Sammy Larbi // Aug 8, 2007 at 2:58 AM

How timely. I read "I'm having a hard time seeing how Microsoft's commercial stack is any easier to configure than the alternative open source stacks these days" on Coding Horror (http://www.codinghorror.com/blog/archives/000925.html) just before I read this.

I've not had any trouble with it (but I haven't tried to do what you're doing). Good you posted a link though - I'm bookmarking just in case (and maybe your link will help push that page to the top of the results).
4 Matt Osbun // Aug 8, 2007 at 5:14 AM

I recently switched from SQLServer to SQLServer Express, and found a big gotcha. SQLServer uses port 1433, but Express uses 1835.

The biggest source of help that I found was Charlie Arehart's blog. He's got a great article on solving issues connecting to SQLServer with ColdFusion.

http://carehart.org/blog/client/index.cfm/2006/7/8/sql2k5_Error_establishing_socket
5 charlie arehart // Aug 8, 2007 at 5:51 AM

Hey Sean, you're definitely not alone in such challenges. In fact, my most popular blog entry is very close to this topic (not specific to Express, but the problem is generic to both editions of SQL Server 2005, and making sure TCP/IP is enabled in the config tool).

http://carehart.org/blog/client/index.cfm/2006/7/8/sql2k5_Error_establishing_socket

Is that something you didn't have to do? I also explain there my own long strange trip to find that answer, so I feel your pain.
6 Jeremy French // Aug 8, 2007 at 6:11 AM

Oye. Did you just knowingly start a Windows vs OS war?
7 Brian Kotek // Aug 8, 2007 at 6:12 AM

I dunno Sean. It might just be what we are used to. I installed SQL Server 2005 and had it running and servicing remote requests in about 10 minutes. To me (someone who has used SQL Server for years), I could counter with stories of long hours trying to coax MySQL into doing something. I spent about 3 hours trying to set up a remote MySQL box so that I could connect to it from home.
8 Ken Dunnington // Aug 8, 2007 at 6:49 AM

I was just reading a Slashdot story on Apache's supposed slip in numbers compared to IIS (http://apache.slashdot.org/article.pl?sid=07/08/06/138220) and there are many commenters who keep saying how Apache is hard to configure and IIS is so much easier, especially for 'n00bs'. Now, I haven't used IIS 7, but have set up dozens of sites in IIS 6 and 5 as well as over 100 sites in Apache, and I have to say I find this claim ridiculous. I'm with you, Sean, give me a config file I can edit in nano and a decent reference page and I'll be quite happy. Microsoft's "help" system is absolutely worthless, and the MSDN is incredibly hard to navigate. I just don't see any task that's truly easier in IIS versus Apache, especially with the tools provided by systems like Debian. Don't even get me started on trying to track down problems in IIS. Is the WIndows Event VIewer good for *anything*?

To bring it back on topic :) I find SQL Server (or at least Enterprise Manager) to be very unwieldy and unfriendly. And configuring SQL Server over MySQL? No thanks. OS X on the desktop and Ubuntu on the server makes for a happy programmer!
9 todd sharp // Aug 8, 2007 at 9:05 AM

Interesting. I found it quite easy to set up the same scenario as you Sean ( with the help of Charlie's - and other folks - post(s) ). It may be just a matter of what you use and are comfortable with. I personally find the SQL Server Studio Manager Express (or whatever the hell it's called) to be a kick ass tool - but I've been using SQL Server for years. I've just started using MySQL 5 - and originally found the tools a bit lacking - but as you get used to them you realize they're not much different then the SQL server stuff (or Aqua Data Studios for that matter).

@Ken - IIS7 _is_ easier IMO. I'm in the younger, give me a GUI crowd. I like icons and UI - not XML/text config files. Again - just a matter of likes/dislikes if you ask me...
10 Sean Corfield // Aug 8, 2007 at 10:36 AM

@Peter, I found the install to be relatively painless - a pleasant surprise.

@Sammy, I'll be honest, I posted the link partly as a reminder for myself in case of future issues :)

@Matt, the firewall issue alone wasted thirty minutes. However, CF8 can connect on port 1433 (but opening ports 1433 and 1434 - for the SQL Server Browser - was not enough, I had to add the executables to the firewall exceptions list).

@Charlie, I didn't have a problem from localhost but to enable remote access I did need to enable TCP/IP (and Microsoft recommended also enabling Named Pipes). Glad to know other people found it less than straightforward - makes me feel better :)

@Jeremy, I'm building out Vista on VMware as a full-blown dev environment. I'm in the process of trying to get BlueDragon.NET running (another blog entry will cover that). My views on Microsoft are well-known but I'm not interested in an O/S flamewar - I share my frustrations with OS X too (there's just a lot fewer of them :)

@Brian, SQL Server 2005 (non-Express) has most of this enabled by default, according to the pages I've been reading about the Express version. As for MySQL, well, my first attempts at setting that up remotely went much more smoothly than setting up SQL Server Express - and involved much less configuration work (essentially one SQL statement is all that is required to enable a remote user in MySQL - how much easier could it be?).

@Ken, I was pleased to see an XML configuration file for IIS7 (a huge step forward) but frustrated that I couldn't edit it directly. Having to open Notepad with Run As Administrator and then Open File... and navigate back to the config is a pain - why could I not simply Edit As Administrator? And why isn't there a GUI tool to fix / validate the config file?

Overall I ended up spending hours using Google to find solutions to problems that shouldn't even have existed in the first place. Most of this is about usability which has always been my big complaint with Microsoft technology. If you're used to it, you don't notice how unfriendly it is.
11 Shaji // Aug 8, 2007 at 7:10 PM

Hi Sean, you're not alone.. Customers on Window VPS has same issue. We have provided step by step instruction at http://hostmysite.com/support/vps/windows/remotesqlexp/
12 John Ramon // Aug 8, 2007 at 8:16 PM

Sean I just updated my test box to CF 8 on Apache (to do some PHP test as well) with SQL Express and MySQL. Funny thing is I had the same problem ColdFusion could not connect to the default port. I did find lots of posts about adding one number to the port or taking one away.

Turns out when I installed and set it up I found a website (i'll have to find it again and post about it) but it showed how to find the port SQL Express was using. My setup is using port 50255, not even close to the default port. Other then that it was a easy setup across the board.
13 Jonathan // Sep 24, 2007 at 10:24 AM

How to find which port SQL Express is using:

1)open SQL Server Configuration Manager
2)under "SQL Server 2005 Network Configuration" in the left pane is "protocols for SQLExpress" (also in left pane). Click on it.
3)right-click on TCP/IP in the right pane and click properties.
4) Scroll down to see the IPALL TCP Dynamic port

THAT is the port you must enter in for Coldfusion to connect properly (it is different on every install of SQL Server Express by default - just one more *push* from Grampa MS to BUY it.)
14 Sean Corfield // Sep 24, 2007 at 11:31 AM

@Jonathan, well, either my install picked 1433 by coincidence or I changed it to use that (I don't remember doing it...).
15 Guy // Feb 12, 2008 at 7:23 PM

I have a set up somewhat similar that of Sean, I have a MacBook Pro running Leopard on the Mac side and Windows XP on the other side using and VMWare Fusion for virtualization. I am using SQL Server 2005 Express and ColdFusion 8 (both on the Mac side and the Windows side). I am able to connect to SQL Server on the PC side from ColdFusion, but but have not been successful connecting from ColdFusion on the Mac side. I have tried what seems like everything, including the Microsoft article and one from Adobe. I have made exceptions in the firewall, disable the firewall, enabled TCP/IP, enabled named pipes, changed ports, etc. Seems like I have tried so many things at this point, sure I have done all the right things, but perhaps not in the right combination. I can absolutely relate to the frustration Sean felt when he wrote this post....hours wasted. Unlike Sean I have still not found the winning combination. If anyone has any suggestions or can tell me what settings they have used both in SQL Server and setting up the datasource, perhaps something will jump out at me. Thanks Guy
16 Sean Corfield // Feb 12, 2008 at 8:02 PM

@Guy, the key for me was telling SQL Server Express to always use port 1433 (because otherwise it dynamically allocates a port). After my comment in September, I didn't do that when I rebuilt my XP env recently and hit this problem. As soon as I set the port, everything worked (although I had to disable the firewall since no exception I could come up with actually allowed things to work).
17 george // Sep 19, 2008 at 6:42 PM

i have a similar setup and man what a headache!

i went through the same posts and even the microsoft help page didn't work for me. possibly because i am running sql server 2005 enterprise on my vmware fusion machine.

anyway long story short i logged in to the server locally and ran this command in a query:

Use master
Go
Xp_readerrorlog

then in the results it said it was listing on port 1138!!

i even checked the client settings and it swore it was listing on 1433!

anyway finally i too am up and running.

An Architect's View

ColdFusion, Software Design, Frameworks and more...