[Looking for Charlie's main web site?]

Solving error connecting to SQL Server from ColdFusion on Localhost

Note: This blog post is from 2006. Some content may be outdated--though not necessarily. Same with links and subsequent comments from myself or others. Corrections are welcome, in the comments. And I may revise the content as necessary.
[Updated a few times since 2006, to correct some minor changes in the tools involved.]

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 choose "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. For the official MS docs on this, see it discussed in this related topic.

(In later versions, where you don't readily find that Configuration Manager, see my discussion below under "The solution" for other ways to get to that feature.)

And if it is enabled, right-click on that TCP/IP option, choose "properties", then its "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 (which may be 127.0.0.1 or ::1, if using "localhost" for the "server" you're telling CF to connect to), and ensure both that it's "enabled" and that its "tcp port" option is set to 1433. Then restart SQL Server and verify the DSN again.

Note that if it's still "not working" after you make these changes, do make sure you don't see 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. More on that below.

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.

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

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.

Here's what the error looked like in 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

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

(Again, this was written originally for SQL Server 2005, and much has not changed, but I offer updated info where needed.)

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 open this other ways. One is to go to Control Panel>Computer Management, then find it under "Services and Applications". Or you can run it from the Start>Run command (see this doc for more on that.)

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][SQLServer 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.

If the problem changes to now just being unable to login

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. There are a few possibilities:

  • Maybe it's just a matter of defining the right user and giving it the right permissions within SQL Server.
  • And if using "trusted authentication" (where you define no username and password in the CF DSN), then it's the user running CF which must be given permissions to the database you want to use. If you've not changed that user, it defaults (on Windows) to LocalSystem, or in SQL Server as the login "NT Authority\LocalSystem". Or as Kris noted in a 2020 comment below, some may find they need to enable permissions instead for the "NT Authority\Network Service" user.
  • Maybe it's that you're using the old "sa" account, and that may be disabled by default. You can check/change that in SSMS on the main "security" section for your server, then right-click the sa account and choose properties then "status" and see if it's "disabled".
  • Or maybe it's a need to enable sql logins (in SQL Server), instead of the default of Windows authentication only. To do that, use SSMS, right-click on the server, choose "Security" and select "SQL Server and Windows Authentication mode".
  • Finally, have you recently restored the DB that you're trying to connect to? If so, consider that you may need to run sp_change_users_login in SQL Server to fix orphaned users.

Hope something above helps. (Even though this post is from 2006, it's still by far my most-visited and getting comments of thanks now over 15 years later.)

For more content like this from Charlie Arehart: Need more help with problems?
  • If you may prefer direct help, rather than digging around here/elsewhere or via comments, he can help via his online consulting services
  • See that page for more on how he can help a) over the web, safely and securely, b) usually very quickly, c) teaching you along the way, and d) with satisfaction guaranteed
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.
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.
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.
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.
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.
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.
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.
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.
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!!!!!!
Glad to help, Dan. That is indeed a great feeling getting that DSN verification message when it's been busted! :-)
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.
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.
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.
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.
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.
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!
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. :-)
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.
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.)
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.
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.
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!
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.
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.
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.
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?
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. :-)
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
(tinyurl: https://tinyurl.com/..., added by Charlie later, as the link above was not appearing right in the blog comments here)

Obviously, I just did the opposite.

Thanks again,

Mark
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.
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.
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...
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?
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).
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.
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
I want to add a comment here, in case it may help others finding this (old, but still relevant) post.

I came across SQL Server docs which indicate that whether TCPIP is enabled by default for a new installation is *dependent on the license/edition of SQL Server*. I had never heard about that before.

So it turns out that basically it's *enabled* by default for the paid editions (Enterprise, Standard, Web), while it is *disabled* for the Dev and Express editions. It's also *enabled* for the Evaluation edition. All very interesting.

Here is the info for SQL Server 2016:

https://docs.microso...


And you can see from this info on SQL Server 2008 that it was about the same (though back then it was NOT enabled for the Eval edition):

https://bit.ly/2pPa4... (for some reason, my blog software borked the "real URL, if I added it here as a comment, which was technet.microsoft.com/en-us/library/ms190198(v=sql.105).aspx?f=255&MSPPError=-2147217396)

All this makes me wonder if that was a key factor in which the problem I addressed in my blog post here was something that perhaps only some percent of SQL users actually hit. Very interesting.
I've tried to get both CF2016 and 2018 to connect to MS SQL 14 for more the 40 hours and I've tried everything posted everywhere. I'm very disappointed in Adobe for not actually fixing this issue with any of their CF Updates. (even though some claimed to fix this issue) Not only is connecting to MS SQL broken, but CF doesn't connect to the new versions of MySQL, or to any versions of MS Access (Unable to update the NT registry. Variable DRIVERPATH is undefined). Not only that, in Windows 10, connections made in the stock ODBC don't show up in the CF ODBC select list. So the only fix that finally got me to my MS SQL Data was to upgrade Windows 10 ODBC to: mysql-connector-odbc-8.0.16-winx64 so that the CF ODBC Select List was finally populated with the connections I pre-made in Windows ODBC. (and by the way, I connected with Named Pipes and not TCP) Another issue that belongs to Microsoft is that when you attempt to add Port 1433 to the TCP/IP Protocol with the Configuration Manager, in the IPALL section it frequently doesn't let you. Happy Frustrations Fellow CFers !
Paul, to be clear, if CF couldn't connect to MS SQL or MySQL, and there was an issue of Adobe's responsibility to fix something about that, it would be HUGE news that would be widely known. As it is, there is no problem of that sort that I know of.

This blog post is about fixing a problem in SQL Server, that makes it ABLE to be talked to from CF. I see you saying in your last sentence that you've experienced issues of SQL Server "not letting you" make the needed change for that ipall setting. As you say, that would "belong to MS".

I just don't see how any of this (my blog post, or indeed your reported issue) "belong to Adobe".

I hear you saying you've spent "more than 40 hours" trying to resolve it, so I gather you're done trying. Perhaps you will want to move on to Node or PHP. Or perhaps you may want to install one of those on the same box and CF and see if they can connect (before you may make such a move). If the problem still happened from them, that would prove that it's not "about CF".

I don't say this to anger or trigger you, though I fear it may. You seem quite upset. I'm just saying that you can't drop a bomb like that (your comment) casting aspersions as if they are fact that apply to others, in asserting that Adobe is "not fixing this issue". I simply don't see what issue it is you think they should fix, with regard to this issue about connecting to SQL Server (when it's not configured to allow it, out of the box).

As for MySQL, CF can connect to that. But as for any issue you have with it, this is not the place to discuss that. You should open a forum post or discussion at the CF portal, coldfusion.adobe.com. Folks there will respond (maybe Adobe, but certainly others, usually including myself, though I can't promise to interact with every one.)

Finally as for ODBC, well, that ship has sailed. It's very old and should no longer be used. It's worked less and less well with later and later versions of CF, especially 64-bit ones. (And CF2018 no longer supports 32-bit at all.) It's VERY rare that someone has a situation where they MUST use it. I hear you saying you are relying on it for now, as a stop gap. (I do find it odd that you say "the only fix that finally got me to my MS SQL Data was to upgrade Windows 10 ODBC to: mysql-connector-odbc-8.0.16-winx64". I don't see how a mysql connector update helps with an MS SQL database. Maybe that was just a typo.)

And perhaps you will want to leave it all at that, if things are "working" for you. I just wanted to say that I don't find what you share to have been as valuable as you seem to think it is. If you want to elaborate, some may appreciate it. (Then again, because all comments go to all past commenters on the post, perhaps others may NOT care to hear elaboration. I'll ask you to think twice if you may want to just "bite back". People really don't need to hear that. In that case, drop me a line directly at charlie at carehart.org.)
and as always, Charlie has the answers. Even though we are on CF2016, development on Windows 10, it STILL comes down to that no-else-mentioned properties IP tab, all IP.
# Posted By Susan Pitcher | 1/28/20 1:56 PM
Thanks, Susan. And yes, this is by far the most viewed post of mine. Granted, it's from 2006, but comments in nearly each year since shows that it's still benefiting folks, which is great.

Thanks for the kind regards, and glad I could help.
Charlie,
You wrote that article in 2006 and in July of 2020, it is still applicable. I have been trying for hours to get this working and came upon your solution of using the configuration manager and enabling TCP/IP. In my case, I also needed to remove 1433 from the port and it worked. Thank you. Please do not take this or any of your other articles down.
# Posted By Bud Hines | 7/3/20 5:31 PM
Thanks, Bud, and glad to help. And nope, no plans to take it or the others down. Even if I ever change to a new blog format, I would keep the old content. Old stuff is often just as valuable as new, though I know it's not the way most view things. :-)
It's been a long time since I've done this, but I also had to make a change in SQL Server Management Studio. Followed the instructions on this post https://blog.sqlauth... and then everything worked perfectly
# Posted By Kris Korsmo | 8/21/20 8:12 PM
Thanks, Kris. Sorry for the terrible delay in responding. I have updated the post to mention your comment and that some may need to enable permissions for that Network Service account (while some may need to enable instead the LocalSystem account, and I added that as well).

In fact, I tweaked the final few paragraphs to be in their own new section, to account for these and other things that one may find they need to do, once they solve the original TCP/IP socket issue which is of course the main focus of the post. But I wanted these other points to stand out better, including yours. Again, thanks.
Copyright ©2024 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