Spreading the good word - Musings and ramblings from the world of technology and the web

Connecting ColdFusion 8 to SQL Server 2008 / 2005

Posted by: Tim   |   Posted on: 26 June 2009 - 03:10 PM   |   Thread: Coldfusion


If like me you've just installed SQL Server 2008 Express and are frantically wondering why ColdFusion 8 is refusing to connect to your data sources, believe me we're not alone. It seems like this problem is par for the course, and there's a fair bit of info out there and quite a few tutorials. The reason I'm posting this tutorial is that firstly all the posts are concerned with Server 2005 and although they got me most of the way, the final fix wasn't mentioned anywhere. Also to get the final fix I found I was piecing together bits from different posts, comments and colleagues so the plan here is to get all the info into one cohesive idiot proof tutorial. Much of my initial insight into this problem came from this great post by Matt Woodward.

Here's the error I had:

java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]
[Macromedia][SQLServer JDBC Driver]The requested instance is
either invalid or not running.

If yours is slightly different it may still be resolved by completing the following steps.

So you've installed SQL Sever 2008 and set-up your database...

Step 1 - Enable TCP/IP
TCP/IP is not enabled by default so the first task is to open up "SQL Server Configuration Manager" > Expand "SQL Server Network Configuration" on the left hand menu > Click on “Protocols for SQL Express” > in the right hand window right click TCP/IP and select enable. You'll need to restart the server for this to take effect but we'll be getting to that in a moment.

Step 2 - Get the right Port
By default SQL Server 2008 Express will not run on port 1433, in fact from what I've read it seems to be different for each machine (but don't quote me on that). To find the correct port right click TCP/IP > select properties > click the IP Addresses tab > scroll down to IPAII > now make a note of the number in TCP Dynamic Ports, this is what we'll use in the ColdFusion administrator when we're setting up the data source.

Step 3 - Restart the server
Now for all this to take effect we have to restart the server so click on "SQL Server services" in the left hand window > right click the "SQL Server (SQLEXPRESS)" in the right hand window > select restart.

Step 4 - Windows Firewall
If you have windows firewall enabled you'll have to add SQL Server as an exception to the firewall rules. To do this go to "Control Panel" > "Security Centre" > "Windows Firewall" > click on the Exceptions tab > click on Add Program, then Browse, and locate the SQL Server executable, which on my machine was under C:\Program Files\Microsoft SQL Server\MSSQL.10.EXRESS\MSSQL\Binn\sqlservr.exe.

Step 5 - Server security
By default the server will be set to "Windows Authentication Mode" we want to make sure that we can access it using "SQL Authentication" also. Open "SQL Server management Studio" > right click your sever and select "Properties" > from the left hand list select "Security" > tick the "SQL Server and Windows Authentication Mode" and then OK.

Step 6 - Check login enabled
Whilst in "SQL Server management Studio" open the "Security" folder in the left hand window > open the "Logins" folder > select the login you're going to use (in the ColdFusion administrator later on) > right click and select "Properties" > from the left hand list select "Status" > check that "Login" is set to enabled > and then OK.

Step 7 - Restart the server (again)
Just to make sure all changes have taken effect lets restart the server again as we did in step 3

Step 8 - Connecting the datasource
Right we're on the final stretch! Firstly boot up your ColdFusion administrator > select "data sources" from the left hand menu > enter the name you wish to give your data source > select "Microsoft SQL Server" from the drop down > click Add > Enter your username, password and the name of your database > in the port box enter the port number we got in step 2 > enter 127.0.0.1 into the server box > click Submit. I tried entering the instance name of the server but for the life of me couldn't get it to work.

Hope this works for you, any errors or general comments let me know, cheers!

An assist from across the pond? Thank You FREDTERP


Comment by: FREDTERP   |   Comment date: 11 December 2009 - 03:52 AM

You've saved me! Thanks!


Comment by: Greg in Mason, Ohio   |   Comment date: 01 October 2009 - 04:21 PM

Great tutorial thanks! Went through the steps one by one, an awkward problem soved!


Comment by: Matt   |   Comment date: 29 June 2009 - 04:52 PM

 
 
 
Back

Threads

Recent posts

Recent comments

  • FREDTERP: An assist from across the pond? Thank You FREDTERP ...more

  • Greg in Mason, Ohio: You've saved me! Thanks! ...more

  • Matt: Great tutorial thanks! Went through the steps one by one, an awkward problem soved! ...more

  • Dougal (BT): Do your self a favour and use Hardlight's services! I couldn't recommend Hardlight's services more. I had the pleasure  ...more