ColdFusion does not connect to the default install of SQL Server Express 2005. After much swearing I discovered that you need to enable TCP/IP (which is disabled by default).
To enable it go to Start -> Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager. In the SQL Server 2005 Network Configuration, click on Protocols for SQLEXPRESS. Click on TCP/IP, and tick the box to enable it. Note: You will need to restart the SQL Server Service, which you can do from Services in your Control Panel.
If you have a firewall, then you need to allow SQL Server. It is a good idea to lock TCP/IP connections down to one port for security. SQL Server 2000 used port 1433. To set the port, clear the value for “TCP Dynamic Ports” and enter the port you want to use in the “TcpPort” box. Note: you will need to use the same port in the ColdFusion CFIDE administrator.
now musing at www.aliaspooryorik.com! 
Wow, absolutely *perfect* timing on this post. I was just looking into how to do this last night, then I come across your site today. I thought I was in for a struggle, then a few quick changes and it works like a charm. Thanks.
There are a few issues (including the ones above) when connecting Coldfusion 8 to SQL server 2005 or 2008 for the first time. I pieced them together into what I hope is a catch-all tutorial, a step-by-step guide. So if the above won’t work then try this out: http://www.hardlightmedia.co.uk/index.cfm?fuseaction=blog.post&id=10