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?
- Signup to get his blog posts by email:
- Follow his blog RSS feed
- View the rest of his blog posts
- View his blog posts on the Adobe CF portal
- 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
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. =)
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?
"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.
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.
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.
Thanks!
Josh
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!
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!!!
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
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.
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.
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
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....
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.
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.
Make sure the login you use in CF Administrator Data Sources has permissions to those tables.
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.
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!!!!!!
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
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.
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.
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.
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.
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.
cheers!
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.
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. :-)
Thank you! (VERY, VERY much)
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
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.
When I enabled and started this service miracle happend...
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.
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.
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?
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. :-)
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
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.
http://www.fusioncub...
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!
Connection refused: connect the root cause was that java.sql.SQLnontransientconnectionexception: and so on.
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).
The post was here:
http://www.gfisk.com...
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.
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,
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.
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.)
Thanks for the kind regards, and glad I could help.
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.
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.