Yesterday, after debating about whether or not to convert my blog to MySQL so that I could have a version of it running locally, I was persuaded by my esteemed Twitter colleagues to try using MS SQL Server Express since I do have SQL Server 2005 running in production. Downloading and installing SQL Server Express took the better part yesterday what with the .NET 3.5 updates and the additional software prerequisites that were needed (not to mention that I was doing client work in parallel); but, all in all that went smoothly.
This morning, I then tried to get my local ColdFusion server to play nicely with my new shiny SQL Server Express instance. I was stumped almost immediately. When it comes to this kind of server setup, I am truly a fish out of water; but thankfully, James Buckingham was there to help me with all the right answers. He pointed me in the direction of two crucial blog posts by Matt Woodward and Ben Forta.
Matt Woodward: ColdFusion + SQL Server Express 2005. The take away from this article was that by default the TCP/IP connection is not enabled for SQL Server Express and must be activated manually using the SQL Server Configuration manager. Also, the default port is different and can be found under the TCP/IP connection properties.
Ben Forta: ColdFusion And SQL Server 2005. The take away from this article was that SQL Server Express does not allow SQL Authentication by default. In order to activate SQL Server Authentication (which is how ColdFusion talks to MS SQL Server Databases), it must be turned on in the server's security properties.
Anyway, all seems to be up and running. A big thanks to James Buckingham and the rest of my Twitter friends.