Tuesday, March 07, 2006

SQL Server Express 2005 Remote ODBC Connections

Just downloaded and installed SQL Server Express 2005 yesterday for my development environment. So far, it looks like a suprisingly functional yet free version of SQL Server. I have been pleasantly suprised. However, there was some heartburn involved with getting it to accept Remote (i.e. from another server) ODBC connections from my development ColdFusion server (non .Net). With the help of google, the following website (and several others), and some patience, I was finally able to get it to work this morning:

http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

Here is what I learned in a nutshell:

  1. By default, SQL Server Express 2005 does not allow TCP/IP connections. You have to turn that on in the Connection Manager. The how-to is listed in the article that I mention above.

  2. By default the SQLBrowser service is turned off. You have to go into Administrative Tools / Services and turn it on. I set it to manual, and then clicked "Start", however you may wish for it to always be running.

  3. Since the computer that I have SQL Server Express 2005 running on is inside a firewalled local network that is not connected to the outside world, I don't have Windows firewall running on the database machine. However, if you do have SQL Server Express 2005 running on a machine with Windows firewall, or any other firewall running on it, you may need to try fiddling with the firewall settings to allow connections to the machine.
I think that's all I had to do. If I remember anything else, I will post it here.