Several users have wanted to know if ColdFusion supports SQL Server 2005. And the answer appears to be yes. I am using ColdFusion MX 7.01 with the default SQL Server driver, and am connected to SQL Server 2005, and so far so good. Microsoft does have a new SQL Server 2005 JDBC driver in beta, but thus far I have not installed it, and not needed to. All testing thus far has worked flawlessly, using basic statements as well as browsing tables and schemas via RDS.
The only two issues I ran into were minor configuration and security setting defaults that needed to be tweaked.
First of all, by default SQL Server 2005 has TCP/IP connections disabled. To enable TCP/IP support, use the SQL Server Configuration Manager tool, select SQL Server 2005 Network Configuration, select Protocols, double-click on TCP/IP, and turn on Enabled.
The next gotcha was the user account. I created a SQL Server user account for ColdFusion, but by default SQL Server 2005 only uses Windows Authentication (which is generally not how ColdFusion would authenticate). To enable support for SQL Server Authentication, right-click on the server in Microsoft SQL Server Management Studio, select Properties, Security, and set Server Authentication to SQL Server and Windows Authentication.
And that seems to do the trick.
(1) You define a "static port" in SQL Server Configuration. (A "dynamic port," it seems, will not be found by ColdFusion but DON'T quote me on that... I just used '127.0.0.1' as the IP. (Normally, SQL Express uses "dynamic ports" and relies on the locator-service to help apps find it.)
(2) As Administrator, give the NT/SYSTEM account permission to "Connect SQL." This is the default account used by the ColdFusion service.