Recently I installed Windows 8.1 in a virtual machine so I could set up IIS, ColdFusion (Developer version) and SQL Server (Express), which would match some of my client’s hosting well enough to use as a test environment.
SQL Server Express and ColdFusion developer edition can be used for free by developers, which makes this a nice, low cost development environment.
I hit big problems trying to get ColdFusion to talk to SQL Server Express, so I thought I ought to document the setup process for next time I tried and hit these problems. Sorry if you’re reading this and some of the notes are not detailed enough, I’ve set up ColdFusion and SQL Server enough times that the basics have stuck, if you need more help you might find it useful to search YouTube for help videos.
Setting up SQL Server Express 2014
Download SQL Server Express 2014 and running the installer. This all worked fine so just Google for wherever Microsoft are putting the installers now (which is a different place whenever I look, which is several years apart.) Try to find out if you’ve got a 32bit or 64bit version of Windows first, as you need to download the version which matches your Windows.
Setting up IIS
Go in to Windows settings > Control Panel > Programs > Turn Windows features on and off
I’m not sure I needed all of these, but I ended up turning them on while trying to solve problems:
Tick all of these (where nested, tick the ones inside the nest, not just to install everything):
.Net framework 3.5
.Net framework 4
Within Internet Information Services:
– Web Management Tools:
– – IIS 6 Management Compatibility
– – – IIS Metabase and IIS 6 configuration compatibility
– – IIS Management Console
– – IIS Management Service
– World Wide Web Services:
– – Application Development Features:
– – – .Net Extensibility 3.5
– – – .Net Extensibility 4.5
– – – ASP.NET 3.5
– – – ASP.NET 4
– – – CGI
– – – ISAPI Extensions
– – – ISAPI Filters
– – Common HTTP Features:
– – – Default Document
– – – Directory Browsing
– – – HTTP Errors
– – – HTTP Redirection
– – – Static Content
– – Health and Diagnostics:
– – – HTTP Logging
– – Performance Features:
– – – Dynamic Content Compression
– – – Static Content Compression
– – Security:
– – – Request Filtering
Setting up ColdFusion 11
Download from http://coldfusion.adobe.com
Run the installer
Choose the option to install a standalone web server, then, later in the install options you can choose to connect it up to IIS.
Setting up a database user in SQL Server Express 2014
In SQL Server Management Studio
Create a database:
Right click on Databases in the left column ‘Object Explorer’ > ‘New Database…’ and run through the short form
Create a user:
In left column ‘Object Explorer’, click on Security, right click on ‘Logins’ > ‘New Login…’
Add a new user, e.g. ‘CFUser’
Choose SQL Server authentication, give it a password.
Uncheck ‘Enforce password policy’
In the ‘Default Database’ drop down, change it to your new database
On the left hand ‘Select a page’ click on ‘User Mapping’
Tick the your new database, further down add them as a type of user to the database – ‘db_datareader’ & ‘db_datawriter’
Configuring Windows Firewall to allow access to SQL Server
As per these instructions from Microsoft I ran WF.msc then set up an Inbound Rule to allow TCP on port 1433 for local use.
Configuring security to allow ColdFusion to get data from SQL Server Express 2014
Apparently by default, SQL Server Express doesn’t allow remote connections, but configuring it to allow a remote connection so ColdFusion could get data from it was very hard, as the 2014 version of SQL Server Express is more locked down than previous versions. I wouldn’t have got it working without this Stackoverflow question about SQL Server Express 2012.
Open ‘SQL Server Configuration Manager’ (by searching for ‘SQL Server configuration’ on the Start screen.)
Under ‘SQL Server Network Configuration’ > ‘Protocols for SQLEXPRESS':
Change ‘Named Pipes’ to ‘Enabled’ (by right clicking) (I’m not sure this step is necessary, as I found it in a bit of advice while I was still trying to get everything working.)
Change ‘TCP/IP’ to ‘Enabled’, then right click again and choose ‘Properties’
Under ‘IP2′ set the IP address to be that of the computer’s IP address on the local subnet (I found this out by running ‘netstat -a’ on the command line and looking down for port 1433 while I was trying something else, I’m sure there’s an easier way of finding it.)
Scroll down to the settings for IPAII.
Make sure ‘TCP Dynamic Ports’ is blank (not the 5 digit number that mine had in there by default.)
Make sure the ‘TCP Port’ is set to ‘1433’ (mine was blank by default.)
You may also need to go to ‘Services’ (by searching for it in Windows) and turning on the SQL Server Browser service (and setting it to run automatically) – I already had mine turned on during other debugging, I’ve read different advice on whether it should be on or off.
Some of the settings for SQL Server don’t take until you’ve re-started the SQL Server service. I think in the end I restarted Windows to be sure things were going to take long-term.
After all of this, I was able to go in to ColdFusion administrator and successfully set up a datasource using the database user I’d set up. Just getting SQL Server and ColdFusion to talk to each other was 3-4 hours of messing about with my settings, hence writing up these notes to make it easier next time.