SQL Server Express and Remote Access
August 7, 2007 · 19 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!