Connecting SQL Server 2008 to ColdFusion

Tuesday, February 10, 2009

When I installed SQL Server 2008 at home, I had no problems with the installation or with hooking ColdFusion into it. At work, a different story. Here are the steps I took, the errors I encountered, and ultimately how I was able to get it all working.

Installing SQL Server

When it prompts you for an install directory, it'll be something like "c:\downloads\x86". do not just accept this as the default. Add "\sqlserver2008" or something onto it, such that your destination is c:\downloads\sqlserver2008\x86. You run the installer as normal. It'll probably prompt you to install the latest version of .net. This will take a while. You'll need to reboot. When this happens, go through the normal install rigamarole (click Next a dozen times, etc). The first roadblock I hit was this message: "The SQL Server 2005 Express Tools are installed. To continue, Remove the SQL Server 2005 Express Tools." So I hit the Back button a few times, but there was nothing in the "Choose components" box indicating sql server 2005 Express Tools. Off to google I went, and found this right away. So, fix #1 was to uninstall SQL Prompt. Done. Moving right along.... The rest of the install went well. One thing though: When I had to chose the instance name, it wouldn't let me choose the Default Instance, saying I already had an instance with that name. So I used "mesher" as the instance name. This is important for later.

Connecting to ColdFusion

At a minimum, you'll need to enable TCP connectivity for sql server.

This is simple:

1. Start -- All Programs -- Microsoft SQL Server 2008 -- Configuration Tools -- SQL Server Configuration Manager

2. in the "SQL Server Network Configuration" section, find your "instance" and look for tabs for Protocols. Shared Memory should be enabled, but the others probably won't be. Right click on "TCP/IP" and enable it. Do the same for the "SQL Native Client" section.

At home, this is all I had to do to get CF talking to SQL Server 2008. At work, this is where the fun started.

I initially tried setting up a DSN in CFAdmin, with the server name of "bltr-mesher2\mesher", port 1433, but kept getting the "The requested instance is not running or isn't valid" error message from CF. This part bears some detail. Note that I used "\mesher" to denote my "mesher" instance. Remember... it wouldn't let me use the default instance when I installed. So right about this point, I was stumped. I KNEW the server name was right. So it must be a port problem. I didn't have any port problems when I did this at home, and as far as I knew, sql server always ran on port 1433. So what gives? I opened a cmd prompt and ran netstat, but didn't see anything suspicious. I tried removing the instance name, tried changing the port, but no luck. Back to google where I found Matt Woodward's post, about SQL Server 2005 and CF. The port settings described in this post were part of my problem. So I went to TCP/IP in the config manager, right clicked, selected "properties", then the "IPAddresses" tab, and sure enough, I had 0 in the "dynamic port" boxes and the port numbers were screwy. (When I installed 2008 at home, I had no such port BS to deal with.)


So I blanked out the dynamic port boxes, added "1433" for the port in IPAll, and restarted.


Still no dice. WTF? I played around with various things, googled and whatnot, telnetted in at port 1433 (worked fine), but couldn't figure it out. Finally, on a whim, I removed the instance name again from the "Server" field in CFAdmin. And it worked. I have no idea why this works. I'm sure there's a perfectly valid reason. I just don't know it.


1. When I got the error about SQL Server 2005 Express tools, I uninstalled SQL Prompt

2. When I chose my instance name, I wasn't able to select the Default instance. If you can select the default instance, do so unless you have a good reason for not doing so.

3. You have to go into SQL Server Config Manager and

a. Enable TCP/IP

b. Blank out dynamic ports and set your port number to 1433

4. After all this, if you're still having trouble connecting CF to SQL Server, and you've used an instance name, try removing the instance name from the DSN "Server" setting.


Dan said...

That sort of makes sense. You can only have one instance listening per TCP port, can't you? So the instance name doesn't really matter when connecting with TCP, because there's only one instance that can listen there.

Marc Esher said...

the part about only one listening instance on a port makes total sense. i'd like to see the if() statement, wherever it is in the driver or sql server or some other communication layer, that's saying "if there's a named instance and it's listening on a port (default port only?), then if the instance name is passed in the JDBC url, go apeshit".

cuz that's what it looks like from the outside.

Anonymous said...

Kudos to you for figuring this out. I spent hours looking for someone to explain this problem as I migrated to SQL 2008.


Marc Esher said...

awesome. glad to help.

Anonymous said...

Oh you have made my day with this :)

Removed the instance name and bam we have a working connection!

Off I go to play now - cheers very very much.

Marc Esher said...

@anonymous I'm glad it helped!

Anonymous said...


Mo Barger said...

I am assuming you are running CF8. Do you know if a different driver needs to be installed for cfmxe 7x?

Marc Esher said...

Not Sure, Mo. are you hitting certain errors when trying to do this?

opc.three said...

What a jip...the built-in SQL Server driver provided with ColdFusion *requires* a port number be supplied even for named instances. All you should really need to provide is the server and named instance (e.g. server\instance) in the server field. The whole point of introducing named instances was to relieve the need for knowing port the difference between typing a domain name and an IP address. There are plenty of JDBC drivers out there for SQL Server that support named instances...get with it Adobe. DISCLAIMER: this is what I have found in CF 8 and 9 using the default SQL Server driver trying to connect to SQL 2008...someone please correct me if I am wrong.

boomi said...

Hi..I am getting the below error.please help me out.

07:44:57,393 ERROR [STDERR] Exception in thread "Thread-20"

07:44:57,393 ERROR [STDERR] java.lang.LinkageError: javax/net/ssl/SSLSocketFactory

07:44:57,393 ERROR [STDERR] at

07:44:57,393 ERROR [STDERR] at

07:44:57,393 ERROR [STDERR] at

07:44:57,393 ERROR [STDERR] at

07:44:57,409 ERROR [STDERR] at

07:44:57,409 ERROR [STDERR] at coldfusion.server.j2ee.sql.pool.JDBCPool.createPhysicalConnection(

07:44:57,409 ERROR [STDERR] at coldfusion.server.j2ee.sql.pool.ConnectionRunner$

07:44:57,409 ERROR [STDERR] at Source)

07:44:57,424 ERROR [STDERR] A non-SQL error occurred while requesting a connection from DATASOURCE NAME

07:44:57,424 ERROR [STDERR] Timed out trying to establish connection

07:45:00,049 ERROR [STDERR] Exception in thread "Thread-21"

07:45:00,049 ERROR [STDERR] java.lang.LinkageError: javax/net/ssl/SSLSocketFactory

07:45:00,049 ERROR [STDERR] at

07:45:00,049 ERROR [STDERR] at

07:45:00,049 ERROR [STDERR] at

07:45:00,049 ERROR [STDERR] at

07:45:00,049 ERROR [STDERR] at

07:45:00,049 ERROR [STDERR] at coldfusion.server.j2ee.sql.pool.JDBCPool.createPhysicalConnection(

07:45:00,049 ERROR [STDERR] at coldfusion.server.j2ee.sql.pool.ConnectionRunner$

07:45:00,049 ERROR [STDERR] at Source)

07:45:00,049 ERROR [STDERR] A non-SQL error occurred while requesting a connection from DATASOURCE NAME

07:45:00,049 ERROR [STDERR] Timed out trying to establish connection

Jose Galdamez said...

I can't thank you enough for this blog post. After spending over an hour trying to make my ColdFusion 9 data source work, I finally googled "sql server 2008 coldfusion" and this was the first result to come up. The part that was holding me up was the setting of the 1433 port numbers in SQL Server Configuration Manager. I had tried setting it only on IP2 prior to reading this post, but setting that alone doesn't do the trick. Setting TCP Port for IP1, IP2, and IPAll was when it finally worked.

Incidentally, I went with what the Adobe docs said about using as the server address. That works too, but I prefer leaving it blank since it's less of a hassle.

Marc Esher said...


Glad it helped! Though I need to emphasize the debt I owe to Matt Woodward, who wrote about that quite some time before I found it. Turns out my problem was a bit more complicated, but Matt's post was definitely part of the solution.



Steve said...

Thanks for the help, bud. Worked like a champ.

Rich P. said...

Marc - thanks very much for posting these steps! I spent a few frustrating hours trying to get the MSSQL - CF connection working (using SQL server 2012 and CF 9), but now I'm finally able to move forward on my CF project. Greatly appreciated!

Sanjeev said...
This comment has been removed by the author.
Sanjeev said...
This comment has been removed by the author.
Sanjeev said...

One Important thing to note is that when using CF7 to connect to a clustered SQL2008 server, DO NOT use a account which has a period in it.

Ex: if you are using a account like this "", there is a good chance that it will fail. So always use a account with no period in it. So it should be just "accountname".

I do not know if the drivers hate a period, but it was the case in our deployment.