[Looking for Charlie's main web site?]

Solving error connecting to SQL Server from ColdFusion on Localhost

Are you getting the error, "Connection refused" or "Error establishing socket to host and port", trying to connect to a SQL Server database from ColdFusion?

The short answer is:

Open the "SQL Server Configuration Manager" in SQL Server, then "SQL Server Network Configuration", and its "Protocols For [yourserver]" option. Open it and ensure that TCP/IP is enabled as a protocol. If not, enable it, and restart SQL Server.

And if it is enabled, right-click on that TCP/IP option, choose the "IP Addresses" tab, and among the listed features, check if "ipall" (among the last in the list) has the port set to 1433 (or whatever is your SQL Server port), then restart SQL Server. More detail below. If you don't want to enable the "ipall" option, check the other entries to find the IP you're using, and ensure it's "enabled" and the "tcp port" is set to 1433.

And if it's "still not working" after you make these changes, do make sure that the problem is not that you're now getting a different error, like "Cannot open database "yourdbname" requested by the login. The login failed." :-) That just means you have a new and different problem to solve, now that this one is resolved.

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 this worked for SQL Server 2005 and 2008. (I don't have SQL 2000 against which to test how things are similar or different, but I do point to some old CF docs that may apply to that version.)

Update: while I wrote this post in 2006, for CF 6/7 and SQL Server 2005, it applies generally to later versions of both.

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 CF admin (7 and above), when adding, editing, or verifying a SQL Server datasource, you may 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 CF (7 or above) 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

Here's what the error is in CF8 might look like:

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

And in still later versions:

Connection verification failed for data source: test java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]Error establishing socket to host and port: localhost:1433. Reason: Connection refused: connect The root cause was that: java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]Error establishing socket to host and port: localhost:1433. 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 back in 2005/6 recommending you just punt and create an ODBC datasource instead), I finally found the CF docs, Configuring and Administering ColdFusion, in its section, "Connecting to Microsoft SQL Server":

http://livedocs.macromedia.com/coldfusion/7/htmldocs/00001743.htm#1278307 (this was the CF7 doc link, back when I wrote this post originally in 2006)

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 and above 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.

(Update: In Windows 8 and 10, you may find instead that you need to go to control panel>computer management, then find it under "services and applciations". Or run SQLServerManager12.msc, for SQL Server 2014, at least.)

From the window that opens, select the "SQL Server 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

I was working on another machine where I had this error, and this time, I had to take one more step in enabling the port, as the step above just wasn't enough.

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 (in the 2005/6 timeframe, when this was first written) 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.
  • 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.)

  • 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.

  • Finally, if things are "still not working" after you make these changes, do make sure that the problem is not that you're now getting a different error, like "Cannot open database "yourdbname" requested by the login. The login failed." :-) That just means you have a new and different problem to solve, now that this one is resolved.

Comments
Charlie,
I got my SQL Server 2005 Express working on my laptop in probably less time that it took for you to cover all of the errors! I applaud you for being thorough.

I was used to using the management utility from SQL Server 2000, so jumping over to check the connection types was logical. I did find it very curious that a dynamic port is generated. I deployed SQL Server 2005 Express on several system and each system had a dynamic port.

Now, I did not use local or localhost for my server name, but rather the name of the laptop itself.

As with SQL Server 2000, I always recommend Mixed Mode authentication. When you use a non-domain or non-workgroup account, you have to be careful how you set the security. You have several ways to do security.

You need to set the default schema for your new user. The schema that I usually use is datawriter and datareader. Now you can change the security yet again from the database security and can give ownership to a given role. I find that it is not wise to make a user login an owner of a schema role.

Also, I saw that little red lines were showing while typing this entry. I do like the Google spell check API. =)
# Posted By Teddy R Payne | 7/10/06 2:40 PM
I've been googling to find the answer since 3 weeks ago and this is the closes I can find.
Unfortunately, what was written here was problem with MSSQL 2005.
I need solution for the exact same problem but for MSSQL 2000. Can anyone shed a light for me please?
# Posted By alec | 7/16/06 5:17 PM
Alec, did you read my entry in its entirety? I said, specifically to your needs:

"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."

and then later

"... I finally found the CFMX docs, Configuring and Administering ColdFusion MX, in its section, "Connecting to Microsoft SQL Server": http://livedocs.macr...

So that should show your answer. Let me/us know how that goes.
# Posted By charlie arehart | 7/16/06 10:17 PM
Charlie,
I read your entire entry and also the macromedia livedoc (http://livedocs.macr... )
I have my SQL 2000 and CFMX 7 installed at the same machine, I checked TCP/IP on my SQL, it's enabled and the port listed (by default) at 1433.
I followed exactly what written on macromedia livedoc (from the above link), setting up the database in the SQL Server Enterprise manager, setting up the database in the ColdFusion MX Administrator and Troubleshooting SQL Server connections but yet I still get the error.
I don't know what to do anymore.
# Posted By Alec | 7/17/06 3:35 PM
ok, thanks for the clarification. just needed to check. as i'd also said, I don't have sql2k to test, so was just pointing out resources. :-)

Are you getting the exact message I show in "the errors you may see" in the entry above? And both in the Admin and in your code? Might help for me and others reading this and trying to help.

If we can't resolve it, then I guess one solution is to "punt" (as I alluded to it above): create an ODBC datasource instead, and tell CFMX 7 to use that. Does that work? If it doesn't, then it also tells you that you have something else going on.

If we can't resolve it here, you may need to take this to the adobe forums or one of the cf lists to get a wider audience to consider the challenge. you may want to point them back to this entry (and tell them to read our comments) to give them background. I'll like to hear your thoughts on the questions I ask here, though.
# Posted By Charlie Arehart | 7/19/06 12:30 PM
Just as an FYI. My default install of SQL 2005 Express on my localhost (win xp pro) was listening on port 2530 for whatever it's worth. that did the trick. (after of course what you suggested in this blog)

Thanks!
Josh
# Posted By Joshua | 7/19/06 5:00 PM
Charlie,
Here is the error message when I tried to set up datasource from administrator:
Connection verification failed for data source: FDPAdmin
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

Another thing, I tried to set the datasource through ODBC (start>>setting>>control Panel>>Administratove Tool>>Data Source (ODBC)> and got a successful verification.
Then, I uninstalled CFMX 7 and re-installed it again, still got the same result!

If I have successfully created a data source through ODBC, does it mean I can start working without having to bother with cf administrator anymore?
I've always use administrator to set datasource and never use ODBC
Please advice, thanks!
# Posted By Alec | 7/19/06 8:05 PM
Hi Charlie,
just to verify my above entry:
First, I set up datasource through ODBC and got a successful verification, since setting up datasource through ODBC has been successful, I assumed there is nothing wrong with my SQL 2000 installatio (name pipes and TCP/IP are enabled, and the port listed as 1433 ans SQL server agent is running).
So, I wanted to do some testing by setting up a different datasource through CF administrator again.
I open up my CF administrator (start>>program>>macromedia>>CF 7>>Admin) and what I got was "Page not found!!"
So I uninstalled CFMX 7 and re-installed it again without problem, then I set up data source through CF Administrator again, still get the same error.
What is wrong with CFMX 7!!!
# Posted By alec | 7/19/06 8:18 PM
Charlie, sorry for keep adding my entry (there is no edit button). I've tested my data source set up through OdBC and it is still good but when I opened my application I got:

Error Occurred While Processing Request
Data source ELCAdmin could not be found.
Please try the following:
Enable Robust Exception Information to provide greater detail about the source of errors. In the Administrator, click Debugging & Logging > Debugging Settings, and select the Robust Exception Information option.
Check the ColdFusion documentation to verify that you are using the correct syntax.
Search the Knowledge Base to find a solution to your problem.

Browser Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)
Remote Address 127.0.0.1
Referrer
Date/Time 19-Jul-06 08:26 PM
# Posted By Alec | 7/19/06 8:27 PM
Alec, if i understand what you're saying here, you've been failing to do a vey significant 2nd step in the process of setting up a datasource using ODBC in CFMX. Once you created the DSN in the WIndows ODBC Control Panel, you then have to add the datasource in CFMX but not using the same "SQL Server" database type that's offered in the dropdown while adding a new datasource, but rather using the "ODBC Socket" option in that dropdown list. Then you simply point CFMX at the DSN that you had created in WIndows.

I'm sorry my entry here wasn't more explicit on that process, but of course it wasn't really an entry on all the ways to add a datasource. :-) Still, it's understandable if you just never heard or learned that there was that ODBC Socket option. Give that a try.

Also, you do NOT want to conclude that "since setting up datasource through ODBC has been successful, I assumed there is nothing wrong with my SQL 2000 installation". Not at all. I said at the beginning of the entry that many people solved the "problem" I describe by using ODBC instead. Clearly, then, using ODBC doesn't show that "nothing wrong with my SQL 2000 installation", at least regarding this security issue. The Windows ODBC SQL Server driver simply uses a different mechanism for connecting to the database than the SQL Server JDBC driver that underlies CFMX.

When you change to using the ODBC driver in CFMX, you cause CFMX to pass the request through that ODBC driver and thus it can circumvent the problem being described in the entry. It may well solve the problem for you, or perhaps not.

Finally, I do wish that we could understand what part of this recommended solution didn't work for you. Again, since I don't have SQL 2000, I can't verify what you did against what the CFMX docs say to do. Still, all this commentary can sit for others to consider, and perhaps someone may even offer an update some day if they read it and know the answer. Hope that helps.

If not, again, I recommend that your next step would be to contact one of the many CF support lists, to get more eyeballs on your problem.
# Posted By Charlie Arehart | 7/20/06 9:44 AM
Hello, fellow readers and commenters on my blog entry of July 8, "Solving error connecting to SQL Server 2005 from CFMX 6.1/7 on Localhost" . I have some news and a request of you. IT seems that the configuration of my blogging software had been preventing you who had chosen to subscribe from receiving emails when others added their comments.

As such, I think I've nailed the problem, but I wanted to make a little test here by picking out an entry with a few (but not too many) commenters, in the hopes that you would let me know if you got this email.

If you could just reply, I'd appreciate it. I'm on shared host, so can't just look at the spool to confirm that emails get out. I've done what testing I can. but now want to make double sure it's working. I've picked this entry as a sample test case. Thanks for your cooperation.
# Posted By Charlie Arehart | 8/3/06 2:57 PM
Hi Charlie and Alec,
I met the same problem as Alec with SQL Server 2000. I checked the TCP/IP and it is already enabled. The SQL Server is working fine with other applications including my web app written in ASP.NET. I haven't checked the ODBC connection, but will let you know soon abt it.
If you guys have any solutions, please let me know.

Thanks in advance,
Quang
# Posted By Quang | 9/19/06 10:45 AM
THe cfmx admin for datasources has 5 tabs and I do understand username and password an example of te other 3 would be great especially the database tab
# Posted By Geoffrey Barth | 10/6/06 8:18 AM
Geoffrey, I'm not sure why you're asking this here. Are you aware that there's online help on the Admin pages? Wherever you want more details, just click the "help" button in the upper right corner. That should answer your question. There is also a "configuration" doc at the livedocs.macromedia.com site.
# Posted By Charlie Arehart | 10/6/06 9:55 AM
Hey Charlie, we just had a customer call in with this same issue. I troubleshooted something similar for JRun back in 2004. However this customer configuration was different:

CFMX 7, SQL Server 2000 SP4 on Windows Server 2003

The problem is not CF. We had him verify that SQL Server was not actually listening on TCP 1433 -- despite the Network Config Utility saying TCP/IP is enabled. To do this run this command: netstat -anop TCP. This will show you all of the open TCP ports. Cross-reference the values in the PID column against the process id for the SQL Server (sqlservr.exe) in your favorite tool -- task manager, pslist, tasklist, process explorer, etc. If you do not see IP:1433 listed then nothing is bound to TCP 1433; however, if it is listed ensure check the PID bound. Check your SQL ERRORLOG, you should see something similar to the
2006-10-12 11:31:18.06 server SQL server listening on 10.7.241.212: 1433.
2006-10-12 11:31:19.60 server SQL server listening on TCP, Shared Memory, Named Pipes, Rpc.

I just blogged about it here: http://www.sargeway....
# Posted By sarge | 10/12/06 4:36 PM
Cool. Thanks for sharing, Sarge. Any additional data points and subtle nuances that affect folks differently will help everyone eventually. It's amazing: this blog entry has been the most popular (in terms of visitors) of all the ones I've ever done. Hope your info here or there will help many more.
# Posted By Charlie Arehart | 10/12/06 5:13 PM
It's amazing how we can take simple configurations and installations for granted, but they seem to be the most prevalent support items.

I just posted a checklist on my blog for connecting to SQL Server:
http://www.sargeway....

Hopefully this will point people in the right direction. If the SQL Server ERRORLOG doesn't say its listening to TCP, port 1433 and the correct IP, then thats the tell tale sign that SQL Server is not bound to that port. There's nothing we can do for you if SQL is not listening where you expecting.
# Posted By Sarge | 10/12/06 6:16 PM
Very good point, and as we both know from our years of supporting CF folks, it's really almost always the little things like that which hurt the most people. We may hear more noise from people with really challenging situations, but more often than not, the answer's really there. The trick is in knowing where to look. Thanks for pointing people in the right direction on this matter. Cheers.
# Posted By Charlie Arehart | 10/12/06 8:14 PM
Hai ,
I am also facing the same problem. I have Checked all the things you said but still i am facing the same problem.I am trying it from past three to four days on this single point. Please give me a better solution,so that it will helpful for me and the same for others.

Thanking You.
Yours Srinivasan.
# Posted By Srinivasan | 11/3/06 7:00 AM
Srinivasan, I've only offered what I've learned. If it doesn't work for you, I'm afraid I have to recommend you contact Adobe support or some other resource for additional help.
# Posted By Charlie Arehart | 11/6/06 10:43 AM
SQL Server 2000 connection errors are caused by uninstalled patches. Here's a link to update SQL Server 2000, http://www.microsoft...

Make sure the login you use in CF Administrator Data Sources has permissions to those tables.
# Posted By Enrique Carlos | 1/15/07 1:30 PM
I've been using SQL Server 2005 with CFMX7 for some time now, mostly migrating existing DB's. I've had a number of problems with schemas and logins and tables not being visible to the app user login I'm using, all of which has been down to my lack of understanding of the new Security architecture but it has been a right royal pain. We use mixed mode authentication and have a username and password for the application that we code into the connection when we create the DSN in CF. This means we have different DSN's for read and write as we use one far more than the other.

However we're also about to migrate our DB to a mirrored configuration on SQL Server. This means that we have two copies of our DB on two separate boxes, when one fails the other takes over and vice versa with lots of catch up and generally clever things going on to make user the data stays in sync or resyncs correctly when the primary comes back up. Damn clever stuff and all voodoo to a mere app programmer like me.

The problem is you need to use the new native SQL Drivers if you want to configure intelligent failover on the mirroring as you need to enter the mirror server details as well. The new SQL server drivers do this but the existing SQL Server drivers in Coldfusion obviously don't. So we configured an ODBC in the OS and then connected to that in Coldfusion. All verified fine until we tried to access any data at which point we got [SequeLink JDBC Driver]An internal error occurred.

Nice, very useful. Now this error seemed to cover a multitude of sins. Our way of fixing this was to turn off maintain connections in the Coldfusion DSN setup and also turn off "use regional settings for currency, date" in the ODBC setup.

All seems tickety boo now though I now have endless permission problems as I've not created my schema correctly.

I thought it might be useful to note the settings problem.
# Posted By Steve Powell | 1/16/07 2:47 AM
Ah finally someone who knows whats going on! Enabling it helped, and so glad you mentioned the server name should be 127.0.0.1 as using the actual name caused errors. Thanks so much!!!!!
# Posted By Josh | 5/28/07 5:08 PM
Fantastic...Most of the time we forget to check TCP/IP and Named Pipe Enable in SQL 2000/2005.
# Posted By Dash | 6/12/07 3:04 AM
Charlie, thank you so much for posting about this. Earlier today, I installed SQL Server 2005 on Windows XP, running CF 7. As you explained, my TCP/IP protocol was disabled by default in the SQL Server 2005 Network Configuration.

A special thank you to Sarge as well. My data sources still wouldn't connect after enabling TCP/IP (and restarting SQL Server). I followed Sarge's instructions to run "netstat -anop TCP" and determined that sqlserver.exe was running by default on port 2720.

I wish I could understand why the darned thing didn't just listen on the "default" port of 1433....nothing else is using 1433 either...but hey, beggars can't be choosers, so I changed the data source port to 2720 and was relieved to get "datasource updated successfully"

Thank you, thank you! Now I can go to bed!!!!!!
# Posted By Dan Parker | 7/23/07 10:19 PM
Glad to help, Dan. That is indeed a great feeling getting that DSN verification message when it's been busted! :-)
# Posted By Charlie Arehart | 7/23/07 11:25 PM
Regarding SQL Server 2000, Microsoft KB Article 306865 has some helpful information about editing the Windows registry. In my case, following the advice of the other commentators, I verified that SQL Server 2000 was not in fact listening on any port. Editing the registry and restarting SQL Server fixed my problem.
# Posted By John | 8/7/07 4:28 PM
Charlie - Sorry to post this comment to your (since it isn't specifically related to your article SQL 2005). However I had the same problem as Alec and similar to yours but with SQL 2000. After hours of troubleshooting I finally figured it out and wanted to post it hear if others have the same issue.

The error I was getting was "java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect" I fixed this by enabling TCP on the SQL server side (not just the client side). To do so go to <<SQL Install Directory>>\80\Tools\Binn and run the 'SVRNETCN.exe' utility. When doing so I found that TCP\IP was not enabled. I enabled it and then stopped and restarted SQL and my issue was resolved. I hope this is helpful to others.

Thank you Charlie and Sarge to getting me this far and I was able to keep poking around on the web to find other tips. Thanks!

Chad
# Posted By Chad | 10/3/07 5:32 PM
I fixed this error in MX 7.1 and SQL Server 2000 by installing SQL Server SP4 and ensuring I add the login name and password to the CF Admin datasource settings.
# Posted By Steve | 1/9/08 2:11 PM
I found this article and thought that this was the answer, but come to find out, after the software was installed and all patches and updates for 2003 by our network admin, some things got changed/edited/messed up. I found this article http://msdn2.microso... very helpful because my three IP's had different TCP ports and each had different Dynamic Port numbers... I deleted all the dynamic ports and all other TCP ports, restarted the service, and then found out which IPs needed 1433, and restarted the service until CF worked! :)
# Posted By Stick | 1/18/08 5:47 PM
I am getting the following error in my event viewer when i restart my coldfusion...please help...also please dont say update ur MDAC, or ur patches , or ur service pack...i have done all that...and i can not ignore this error..this error never came before..nd it is imperative i fix this error ASAP, I did change the tcp/ip in the server configuration, that part is perfect, something in the coldfusion admin, if that can help , it would be great...give me some hints on the coldfusion admin....


The description for Event ID ( 0 ) in Source ( ColdFusion MX 7 ODBC Server ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: ColdFusion MX 7 ODBC Server@LOCALHOST,ErrorCode=2310,ErrorMessage=TCP/IP, connection reset by peer.
# Posted By sachin jarral | 4/10/08 2:46 PM
Sachin, a couple of thoughts.

First, it doesn't appear that your problem is related to the blog entry above. You're asking about ODBC connections, and while I mentioned that they are suggested by some as a work-around to the problem above, you're asking about problems using them so it's really off-topic.

Still, in the guise of "teaching one to fish", I'll say that I did a little googling and I find a few things that seem may help. I'll assume you had searched for the specific message, "The description for Event ID ( 0 ) in Source ( ColdFusion MX 7 ODBC Server ) cannot be found.".

And the first result (of 2 there) is a lengthy discussion of this issue by an Adobe engineer in the Adobe forums. Are you saying you saw that and don't care for his suggestions (which do refer to some upgrades). Is that what you mean by you've "done all that"? Just seems worth confirming.

But beyond that, I had found many more possible solutions. How? When I search for such a message, I keep in mind that some parts of the message are too specific to a particular version of CF, as in the "ColdFusion MX 7 ODBC Server" above. Maybe the problem isn't unique to 7 and could apply to 6 or 8. So a search for

ColdFusion ODBC Server "description for Event ID"

finds quite a few more hits and more discussion. Check those out.

In particular, this one seems to have a good chance of answering your problem:

http://www.forta.com...

Check it out and let us know if it helped. If it didn't, do let us know, but you may want to go to the Adobe forums yourself to ask this, since it's more likely an Adobe engineer will see it there than here.

One last thing, if you don't want to do that but still want help, a blog entry just may not be the most expedient place to bring up your problem. If you're willing to pay for assistance, there are folks (including myself) who offer support for a modest hourly fee. For more on mine, see carehart.org/consulting/. Even so, I hope the info above (like all my blog entries) may help you and others, without charge.
# Posted By Charlie Arehart | 4/11/08 11:44 AM
About that blog entry of Ben's that I pointed you to, Sachin, I meant to add that the useful suggestions to consider are especially those in the comments, where other people mention getting the same error in CF6.
# Posted By Charlie Arehart | 4/11/08 11:47 AM
It did not help...please give me steps.. I would really appreciate that..maybe something in my coldfusion administrator not right !!!
# Posted By sachin jarral | 4/14/08 12:32 PM
Sachin, I must direct you to the steps I outlined in the previous two comments. When you say "it did not help", what is "it"? Are you saying you tried every suggestion in every comment of that blog entry? And you viewed every entry in the search I proposed? And that you asked your question on the Adobe forums? If you didn't do all these things, I'll say again that this blog entry is not the place to come asking for help, on a subject unrelated to it.

And since you're asking about something outside the focus of the entry, I'll just reiterate that you can contact me directly about consulting, at charlie at carehart.org. There's always an answer to a challenge one faces. It's just a matter of finding it. I've tried to help with free suggestions as much as I can.

Otherwise you can wait and see if anyone may reply. I'll say again, though, that asking that question here, when it's not related to the subject at hand, is something to reconsider in the future.
# Posted By Charlie Arehart | 4/14/08 1:25 PM
Charlie, I have done everythin I could find in the internet.made sure tcp/ip named pipes are enabled, the path in my *.ini file to coldfusion is set right, made sure all the components are started correctly, name of the server is there instead of IP address, reload , patch and update the drivers. There is nothing left to do, still after I restart my coldfusion thru services I get the above error.
# Posted By sachin jarral | 4/17/08 6:02 PM
Charlie..i m getting one more error on eventviewer..can you please help me in this ASAP..or some1... Thanx for all your help..les do this now..nd we can talk about the other error later on next month or something..

I have a SQL Server 2005 (SP2) on a freshly installed Windows Server 2003
(SP2) AD member server.
I receive next message in Event Log:
Source: Schannel
ID: 36882
The certificate received from the remote server was issued by an untrusted
certificate authority. Because of this, none of the data contained in the
certificate can be validated. The SSL connection request has failed. The
attached data contains the server certificate.
# Posted By sachin jarral | 4/22/08 11:05 AM
Sachin, I'm afraid that's one I have no experience with. Again, though, I have to ask that you please do not use this blog as a support forum. You've again taken it to an entirely different problem than the blog entry above.

Please take your issue instead to the Adobe support forums, http://www.adobe.com... (which are free, watched by hundreds and thousands of people who like to help). You'll notice there's a specific "database access" forum there.

Please do not continue to bring up these unrelated issues here, or I may be forced to delete your future comments. I'm sorry it's come to that (the first time for me), but I really must insist, for everyone's benefit.
# Posted By Charlie Arehart | 4/22/08 3:31 PM
Thanks for this - we had this problem with Quality Center and your comment about adding the port to the TCP IP list did the trick. Hopefully other Quality Center users will find this page when searching for the answer.

cheers!
# Posted By Dave Hartley | 5/28/08 8:07 AM
Thanks for this, it worked. The year is 2008 and still this blog is helping people around, though it was published in 2006. Please keep it up for others to be able to solve this nuisance.
# Posted By muki | 6/22/08 3:11 AM
Thank you! This post undoubtably saved me hours of anguish!
# Posted By Jonathon | 6/23/08 8:54 PM
Man !!! I have had this problem in local installations for years!!, and finally there is the solution.

I never had the problem on production environments, and the lost of performance for creating a ODBC was never too important in my local machine, so I gave up long ago.

But now it is fixed !

Thanks ! I owe you 10 beers.
# Posted By Bayron | 6/29/08 8:37 PM
To Bayron and others, thanks very much for the kind words. Really glad to see this entry still helping people 2 years later. That's the power of the web and blogs.

As for the 10 beers, well, my waistline will ask me to pass on that gracious offer, but I'll note that I do have an Amazon wishlist link on the right should anyone ever be so motivated. :-) But I contribute here and elsewhere in return for info I've gotten from others over the years. Pay it forward however you can. :-)
# Posted By Charlie Arehart | 6/30/08 11:13 AM
G.E.N.I.O.U.S.

Thank you! (VERY, VERY much)
# Posted By Phil | 7/17/08 6:15 PM
Thank, Phil. And if you got an error while submitting your comment, don't worry. It took. And in fact I'm posting this comment to make sure that the error is now corrected. Apologies to others subscribed to the entry still.
# Posted By Charlie Arehart | 7/17/08 6:58 PM
OK, hopefully the last test comment. (Sorry, yes, I should be testing the fix on other than the live site but it relies on some data in this entry and its comments that would be a challenge to recreate in test. Again, my apologies.)
# Posted By Charlie Arehart | 7/17/08 7:01 PM
Charlie,

Thanks for the excellent instruction. I have been to so many blogs before coming here, I was able to get to the root of the problem right away. Thanks once again. God bless
# Posted By Thomas Mathew | 8/2/08 5:44 PM
Folks, I just came across this free (simple) tool which may help some facing other challenges trying to resolve problems connecting to SQL Server.

http://www.whitepeak...

I've not used it, but just thought I'd mention it in case someone gets to this entry and through all the comments and still feels stumped.

BTW, @Thomas, thanks for your last comment of appreciation.
# Posted By Charlie Arehart | 9/1/08 10:48 AM
I found that SQL Server Browser windows service, after MSDE (SQL Serv Expr) is instaled, sometimes is disabled. I spend days looking for regular options to enable network loging, network services etc.etc.and everything was fine, but network loging simply did not worked.
When I enabled and started this service miracle happend...
# Posted By Mirza H. - Bosnia | 11/1/08 10:49 AM
@Mirza , thanks for sharing your experience. Every little bit helps when trying to track down annoyances like this. Cheers.
# Posted By Charlie Arehart | 11/1/08 12:23 PM
Thanks for the excellent resource. I was able to fix it on my CF8 SQL Server 2008 (both dev) configuration.
# Posted By Kamil | 5/15/09 11:00 AM
Thanks, this was great help!
# Posted By Nathanael | 5/19/09 1:27 PM
There are a number of issues that might not be immediately obvious when connecting ColdFusion 8 to SQL Server 2005 / 2008 for the first time. I've tried to create a catch-all step-by-step guide I pieced together from other sources and my own experimentation that should sort most problems. Let me know how it goes. Solution at: http://www.hardlight...
# Posted By Tim | 7/14/09 4:45 AM
Thanks, Tim, for sharing another resource for people facing these challenges.
# Posted By Charlie Arehart | 7/14/09 9:37 AM
Great post -- I can't believe that it was this hard to find a fix to a seemingly common problem. The last time I had to deal with named instances & CF was probably 7 years ago (yep, old time coder, lol). Thanks for posting this resource!
# Posted By jk | 9/10/09 3:12 PM
Dude,

It was your very last comment regarding IPALL that saved me. I kept going round and round trying to figure out what the heck was going on, including a failed attempt at the ODBC Plan B option, all to no avail.

I luckily found your thread and found the one thing I hadn't tried yet which is the IPALL thing.

Thanks for your post and updates! Lifesaver!

G.
# Posted By Gregory Matthews | 11/5/09 12:49 AM
Always great to hear. Thanks for the encouragement, Gregory.
# Posted By Charlie Arehart | 11/5/09 6:47 AM
Thanks! Lifesaver!!!
# Posted By Rammo | 5/10/10 10:08 PM
thanks for thought on adding port 1433 in sql server configuration manger under ip address tab..that was a real life saver for me..
# Posted By jaya | 9/27/10 11:57 PM
Thank you very much !!!
# Posted By Nwebsolution | 10/19/10 7:25 AM
Thanks a lot!!
# Posted By Arshad | 10/16/11 7:11 AM
I replaced may laptop with a new one and forgot what I needed to do to get the CF DSN to connect to my local mssql dev (Microsoft SQL Server Express Edition with Advanced Services) and your article helped perfectly. Like your dev environment, I went straight to the bottom option and entered my port number, restarted the server, and viola, back in business.

Why did I bother to leave a comment? I just wanted others to know that this works for Microsoft SQL Server Express Edition with Advanced Services (2008) and not just 2005.
# Posted By Tom | 2/24/12 8:30 PM
Thanks, Tom. Great to hear that it was so helpful. Thanks for sharing that. And good of you to add that info on 2k8 for folks who may have wondered.
# Posted By Charlie Arehart | 2/25/12 10:46 AM
Thanks Charlie, you saved me some headbanging with this post.
# Posted By Tom Bishop | 3/14/12 5:48 AM
Thank you very much. This post saved my marbles.....
# Posted By Kwabina Griffith | 11/29/12 5:15 PM
Charlie,

This post was very helpful...got past all the errors mentioned in the post. However, I am now getting another error, which Google is of little help. It seems it should be easy to fix, but can't figure out where. Here is the error message:

The root cause was that: java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]The SQL Server login requires an SSL connection.

I get this message when trying to set up a new datasource in CF 9. I can connect with studio fine and I no longer get the errors mentioned in the article (thanks).

Any ideas?
# Posted By Mark Fuqua | 12/12/12 10:49 AM
Mark, I gather you're using the feature SQL Server to enforce only encrypted connections, right?

First of all, I'm not sure if CF even supports that. Or it may matter what version of CF you're using, or what version of the JDBC driver. (I've never tried it myself.)

Second, are you really sure you need that? If the CF and SQL Server machines are in the same network, is the increased security really necessary? and worth the potential incurred overhead?

Finally, in the vein of "teaching a man to fish", when you say you googled about this, did you perhaps google the entire error message? In such a case, try googling part of it instead.

When I searched for just "The SQL Server login requires an SSL connection", I find discussions related to both CF and things other than CF (because I left off the parts of the error message relating to macromedia, etc.), which can often be quite helpful.

From that, I see discussions related to versions of drivers, versions of SQL server, versions of JSSE supported within the JVM, and more.

Here's another out of the box thought: there's an aspect of that SSL configuration that can be tweaked in the CF jvm.config (specifically related to addressing issues related to installing server certs with the java keytool), where one may enabled an option, -Dsun.security.ssl.allowUnsafeRenegotiation=true. It could be that that change would also affect the DB driver talking to a server via SSL. Keep that in mind if you may find someone else with all the other same version configuration as you, saying it should work, if it still does not for you.

Hope something there's helpful. Not really "the answer", but some thoughts for you to consider. :-)
# Posted By Charlie Arehart | 12/12/12 12:33 PM
Thanks Charlie,

You sent me down the correct path. I case anyone stumbles upon this in the future, it was happening because SQL Server was enforcing encrypted connections...not something I did on purpose, but somehow it was.

Here is a quick reference on how to enforce encrypted connections:

http://technet.micro...(v=sql.105).aspx

Obviously, I just did the opposite.

Thanks again,

Mark
# Posted By Mark Fuqua | 12/12/12 2:47 PM
Glad to hear it's resolved. And thanks for sharing the link. I hadn't thought to because I assumed (as I said in the opening) that you were intending to use that feature.

So it seems the first paragraph was all you needed. :-) But maybe the rest will help someone else.

Also, I do hope you saw my point about googling for part of the error, because that may also have clued you quickly into it being related to that SQL Server feature. Anyway, glad to have helped.
# Posted By Charlie Arehart | 12/12/12 3:37 PM
How to connect Java to jdbc and odbc @ same time
# Posted By sachin dev jarral | 8/1/14 12:51 AM
@Sachin, are you asking this with respect to ColdFusion? and by "at the same time", do you mean two datasources pointing at the same DB, one via ODBC and one via JDBC? You could, though one might wonder why you'd want to do it.
# Posted By charlie arehart | 8/1/14 8:40 AM
I'll add a comment here that someone pointed out to me a nice blog entry that Steve Brownlee had done, also back in 2006. He has some nice screenshots, so I want to mention it here now. The concepts still apply even in more recent editions of SQL Server:

http://www.fusioncub...
# Posted By charlie arehart | 9/29/14 9:18 PM
Charlie,

I have worked with CF for years and had this problem several times. I am working with a new server and CF 2016, using MS SQL Server 2012 SP3 and I am having the connection refused message. I took all the normal steps for turning on the TCP IP and enabling the IPs Anything new or special to check for CF 2016 and MS SQL server 2012?

Thanks!
# Posted By Greg B | 9/28/16 10:52 AM
Greg, I wouldn't expect there to be, no, but I can't say I've checked that specific combination (I do think i have). Are you having a problem, or have you not tested it yet?
# Posted By charlie arehart | 9/28/16 10:54 AM
Yes same Error..
Connection refused: connect the root cause was that java.sql.SQLnontransientconnectionexception: and so on.
# Posted By Greg B | 9/28/16 10:59 AM
Greg, I'm afraid I have no other suggestions than the dozens mentioned above. First and foremost, did you confirm that SQL Server is listening on port 1433 (if that's what you've told CF to use)?

If you go to an elevated (admin) command prompt ON THE SERVER with SQL Server, and do a netstat -o | find "1433", does it report some process listening to that port? the process ID is the last column. In Task Manager, is that the process ID of SQL Server?

And have you confirmed that the port on SQL Server can be reached from the server running CF (assuming they are different)? Perhaps a firewall is blocking it.

From the command line of the server running CF, do telnet domainorip 1433. Does the screen go blank? If not, then that port is not open, or not open to that server. (If you don't have Telnet, you can add it using "programs and features" in Windows.)

If you've done all that, and a telnet request is working, then I see no reason CF couldn't talk to the DB, and I'm afraid I would have no other ready suggestions (assuming you've read every word of the post above, with all its suggestions).
# Posted By charlie arehart | 9/28/16 11:10 AM
I found a post about adding a different port to the all IP address setting and this fixed it, I have never had to do that before. I set dynamic ports to 4718 and it fixed it.

The post was here:
http://www.gfisk.com...
# Posted By Greg B | 9/28/16 11:15 AM
Greg, I appreciate the suggested solution, and hope it may help others. But are you saying you also changed SQL Server to listen on that new port?

And as important, had you tried any of the recommendations from my last comment? It may help others to know what did/did not work for you, that led to your solution.
# Posted By charlie arehart | 9/28/16 11:35 AM
Charlie,

To sum it up, I tried the standard fixes for the connection refused with cold fusion server 2016 and MS SQL Server 2012, and did not have any luck.

I tried :
Enabling TCPIP
Turning on the IP address under the details of the TCPIP settings.
Setting the IP all port as described above.

Enabling Named pipes
I tried connecting to the server in CF admin with 127.0.0.1\SQLEXPRESS
I tried connecting to the server in CF admin with [ServerName]\SQLExpress
I also tried "localhost"

And none of this worked, except when I changed the IP ALL setting to use TCP Dynamic port 4718 then it connected.
I used server setting of "127.0.0.1" and the dynamic port 4718.
I specified the user name and password in the CF admin of the data source. So the only thing that fixed it for me was using the dynamic port setting in the port field of the CF admin create datasource. Hope this helps someone else!


turn on TCPIP,
# Posted By Greg B | 9/28/16 12:22 PM
Copyright ©2017 Charlie Arehart
Carehart Logo
BlogCFC was created by Raymond Camden. This blog is running version 5.005.
(Want to validate the html in this page?)

Managed Hosting Services provided by
Managed Dedicated Hosting