Setting up a ColdFusion Developer's Environment with SQL Server

January 6, 2011 - With the persistence of a true ColdFusion guru and bonafide ColdFusion zealot, that is, Rick Tuinenburg, I am now able to use ColdFusion and SQL Server on my Windows 7 workstation. Rick's website: Rick has stuck with me on this struggle for 2 days trying to get SQL Server to work with the CF Administrator. What I am referring to specifically is the Data Sources set up in ColdFusion's Administrator page. I use this URL to get to that page on my workstation: http://localhost:8500/CFIDE/Administrator/Index.cfm
If you are not using the internal ColdFusion default server, you should remove the Port # "8500" from the URL address and its preceding colon ":".

You need to set up your Data Source Name (DSN) in the ColdFusion Administrator.

I can see that many ColdFusion developers have and will still have the same issues with setting up Microsoft's SQL Server with ColdFusion. Why is this? Why is it so difficult? We saw the same thing at the 13 Nov 2010 ColdFusion DevCamp held in Chandler, Arizona. A good percentage of the developers at this event had the same exact issues. It seems that because there are so many things that you must attend to in the SQL Server/ColdFusion setup, that if you miss just one thing, it won't work with ColdFusion.

Here are a couple links to address the problems:

(1) SQL Server needs to be set to Authenticate for both Windows and SQL Server Authentication, commonly known as "Mixed Mode".
(2) If you have several instances of SQL Server, this could become more difficult to set up.
(3) You need to set the IP Ports to 1433 in the IP Addresses tab of the TCP/IP Properties Dialog Box and make the ENABLED box YES. We used SQL Server Configuration Manager to take care of this. You have to restart the SQL Server instance service after you make the IP Port and Dynamic Port changes. You need to also ENABLE the TCP/IP Protocol on the Protocol Tab page.
(4) You need to remove any values from the Dynamic Ports. The default value is 0.
(5) You should have a Username and Password for SQL Server. We used the default "sa" username and gave it a password.
(6) You should also have a Username and Password for the database you want to connect to.
(7) Contrary to the above-referenced URL, we did not put in port # 1433 for IP ALL, as this had a bad effect on SQL Server Service.
(8) We turned the Windows Firewall off. I know that we could set the Firewall to let the traffic through on Port 1433.
(9) The 2nd link says to turn off the Browser Service, if you change the TCP IP Ports to something other than 1433, as it would announce the change and break security.
(10) The above were our considerations for setting up a developer's environment, not for production use.

I'd like to thank Rick Tuinenburg for his help this week (January 2011). He has been a true friend to me and is a strong ColdFusion advocate.

Cecil Champenois, 7 Jan 2011.