Solving error connecting to SQL Server 2005 from CFMX 6.1/7 on Localhost

No, I'm not just going to recommend creating an ODBC datasource, as many others have. :-) There is a real solution. The short answer is to open the "SQL Server Configuration Manager" in SQL Server 2005, and ensure that TCP/IP is enabled as a protocol.

The rest of this entry explains additional details, such as how to find and make that change, what specific errors you get, and how I found the information, in case any of it helps others.

I should add that I don't have SQL 2000 against which to test how things are similar or different, but I do point to some CFMX docs that may apply to that version. In any case, hope this will serve all those CFML developers making the move to SQL Server 2005.

The Errors You May See

To help ensure that future readers can find this entry more readily when doing web searching, let me offer details on the error. The problem I'm referring to is if you get any of the following errors.

In the CFMX 7 admin, when adding, editing, or verifying a SQL Server 2005 datasource, you get the following error:

Connection verification failed for data source: blogcfcsql java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect

Or if you run code on CFMX 7 that tries to use such a datasource and get:

Error Executing Database Query. Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect

In CFMX 6.1 you get this slightly different error when adding or editing the DSN in the admin:

Connection verification failed for data source: blogcfcsql []java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect

When you simply verify the DSN in CFMX 6.1, it only says "error" in the status column. You need to edit and submit the DSN to see the error above.

And if you run code trying to use such a datasource in CFMX 6.1, you get:

[Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect

I don't have CFMX 6.0 installed to see what happens there.

Lastly, in CF5, while most use ODBC datasources there, if you did try to create an OLEDB datasource using the SQLOLEDB provider, in the circumstance I'm describing it would just say "failed" if you try to verify it, and "The connection to the data source failed" if you edit the DSN and submit it.

In code using the DSN you would get:

OLEDB Error Code = 17 [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

Update for CF8

Connection verification failed for data source: blogcfcsql java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket to host and port: [server]:[port]. Reason: Connection refused: connect The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket to host and port: [server]:[port]. Reason: Connection refused: connect

So, those are the errors. What's the solution? :-)

Finding the Solution

After some digging around, where folks offered all sorts of remedies (including recommending you just punt and create an ODBC datasource instead), I finally found the CFMX docs, Configuring and Administering ColdFusion MX, in its section, "Connecting to Microsoft SQL Server":

http://livedocs.macromedia.com/coldfusion/7/htmldocs/00001743.htm#1278307

There it offered a few recommendations where "the following situations can cause a Connection Refused error". The one that caught my attention was:

SQL Server does not enable the TCP/IP protocol. This problem can happen when SQL Server is on the same computer as ColdFusion MX.

Well I am running both CFMX and SQL Server on my laptop (development mode for each), so this sounded encouraging. It would also explain why different folks experience different things, if they have SQL running elsewhere and/or in non-development mode. Indeed, further reading (discussed later) shows that there are differences in implementations of SQL 2005 that would influence whether you'd get this error.

Anyway, the CFMX docs go on to say you should make sure that TCP/IP is not listed as a "disabled protocol", which it indicated can happen in a local (development) mode installation of SQL Server.

Unfortunately, the steps they show to check and correct that are not appropriate for SQL Server 2005. (I will add a comment there pointing back to this blog entry.)

But in searching the SQL 2k5 books online, I found an entry that does explain this issue (following is a local link if you have BOL installed yourself):

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ad98873f-8478-4bce-8abf-c24f5d111144.htm

It explains how things will be set for different versions of SQL Server 2005 (Enterprise, Standard, Workgroup, Developer, Evaluation, Express) and whether you have a previous SQL Server version that you are or are not upgrading. Each of these has different defaults for if TCP/IP is enabled.

The Solution in SQL Server 2005

BOL goes on to indicate that we need to go into the SQL Server Configuration Manager to configure the network protocols. There are 2 ways to do this: either right-click on the server in Mgt Studio and choose "SQL Server Configuration Manager" or use Start>Programs>Microsoft SQL Server 2005>Configuration Tools>SQL Server Configuration Manager.

From the window that opens, select the "SQL Server 2005 Network Configuration" node, and its "Protocols For [yourserver]". That lists the various protocols (including TCP/IP and named pipes), and sure enough, for me it showed disabled.

I right-clicked the TCP/IP protocol name and andose "enable", which reminded that I needed to restart the SQL Server for the change to take effect (right-click on the server in SQL Management Studio and choose "restart"). I did that, and then reran my code and all was well (and the datasource would now verify in the Admin console.)

Phew! Hope this helps someone else.

Update

On that TCP/IP protocol, where I right-clicked to enable, that right-click also offers a Properties option (which is another way you could choose to enable/disable the protocol). But the window that pops up also has a IP Addresses tab. Choosing that, I saw several various indications of IP addresses, with settings to both make them active and enabled, as well as indicate the port they should use. I tried changing various ones, but in the end found that the only solution was to change the last one, IPAll, and add my SQL Server port (1433) in its TCP Port field. After doing that, and restarting, all was well.

There may be (indeed, likely are) important security considerations that should go into enabling that option. Since this is just my local development box, in a firewall, I won't bother to investigate the, but others here should.

Some Closing Additional Thoughts

Again, some posts I'd found suggested that the solution was to use an ODBC connection instead. That surely works, but most regard an ODBC connection as less performant, and involving more communications overhead per transaction. Also, I have seen code that failed using an ODBC connection that worked fine with a SQL Server connection.

I should also note that you can change this another way, using the new SQL 2005 "Surface Area Configuration" wizard, and choosing "Configure Surface Area for [your server]", then choose "Surface Area Configuration for Services and connections" and then in its window choose "[your server]>Database Engine>Remote Connections and changing to "Local and Remote Connections" to enable TCP/IP.

I'll note that for some reason, BlueDragon's SQL Server driver (based on New Atlanta's JTurbo Type 4 JDBC driver for SQL Server) didn't have this problem. Perhaps someone with more experience in the underlying implementation can explain why the two are so different.

Finally, one other comment: if the error you're getting is instead:

Error Executing Database Query. [Macromedia][SQLServefr JDBC Driver]Error establishing socket. Unknown host: (local)

That is a different problem. The online help in the CFMX Admin suggests that you can name the server as "(local)", but I find I get the aforementioned error. Changing it to the server name or IP, such as 127.0.0.1 for my localhost, solved the problem. (The help also indicates this an option, so I'm just speaking to those who try the "(local)" value.)

One other thing: another problem could be that SQL Server is listening on a port other than what you think. Don't presume it's the standard 1433. Someone could have changed it on install, for security reasons, or some other config feature could have changed it. You can see what the port is by using that same SQL Server Configuration Manager, but rather than use the "SQL Server 2005 Network Configuration" option in the left tree, choose the "Client Protocols", then right-click on TCP, and choose Properties. It will list the "Default Port". Hope all that's helpful to some.