I've been using SQL Server Express for quite a while noa, and still prefer it to almost every other database application. I was rebuilding a development environment the other day, and discovered that I was using an ODBC "trusted connection" to connect to a SQL Server instance in Coldfusion Server. Why was this I thought?

It turns out that I'd had some problems getting Coldfusion to connect SQL Server datasource connection, so I'd used a custom ODBC connection. Looking into the connection error and having a dig around I found out a few things its worth checking when installing SQL Server.

1. First thing is to enable TCP/IP connectivity, as its not on by default. Go to Start -> All Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager. Under SQL Server 2005 Network Configuration, click on Protocols for SQLEXPRESS, click on TCP/IP, and click enable. Then restart the service, which you can do from the Configuration Manager under the SQL Server 2005 Services area, or in windows services.

2. In my experience SQL Server always used to use port 1433 by default after installation. For some reason it hadn't used this port, it was using 1523. I was quite puzzled about this as I just used accepted the installation defaults. The port is a crucial setting when you're setting up your datasources for Coldfusion, otherwise CF wont even find the datasource, let alone attempt to connect. To check your port number, go to the SQL Server Configuration Manager, go to Network Configuration/Protocols again, right click on TCP/IP, choose Properties, then click on the IP Addresses tab. Look under IPAll in the TCP Dynamic Ports section. I personally wouldn't change this as I'm not really sure why SQL Server is using a different port now. I just used it in my Coldfusion datasource setup, and it connected fine.