Datasource connection fails suddenly

[fredp60157821]

I have a CF9 installation which has been stable for a long time, maybe years.  Two days ago, after a Windows update, my CF datasources no longer worked. The message I get on everyone one of my datasoruces is:

Connection verification failed for data source: HomeOnSqlServer
java.sql.SQLException: [Macromedia][SequeLink JDBC Driver]TCP/IP error, connection refused.

My SqlServer database is running fine; the ODBC32 and ODBC64 applications show the datasources are all valid.

So I thought that since CF9 is old I'd upgrade to CF2021.  I did the install but I get the same message with CF2021 as I did with CF9. 

So then I went back to Windows and tried to uninstall the update which seemed to cuase the problem, but that didn't fix the problem.

My guess, but it is only a guess, is that when Windows 10 did this last update it must have changed some setting or removed some service which the CF ODBC datasources require.

Does anyone have any idea how I go about tracking down "[SequeLink JDBC Driver]TCP/IP error, connection refused" errors?  Thanks.

[Dave Watts]

If this happened after a Windows update, it's probably a network problem and has nothing to do with CF. So, I'd start looking at SQL Server network configuration specifically. SQL Server can listen using a bunch of protocols, TCP/IP is just one of them. So successful ODBC connections don't necessarily mean that JDBC connections using TCP/IP will work. SQL Server includes a network configuration tool, you should take a look in there to see whether TCP/IP is still enabled, what ports it's using, etc.

Also, you should take a look at your Windows Defender local firewall settings, and make sure that connections are allowed from your web server to your database server via TCP/IP using whatever port SQL Server is using (by default, this is TCP/1433).

Dave Watts, Eidolon LLC

[fredp60157821]

Dave makes an excellent point that it is very unlikely a CF problem.  I have checked and SqlServer is still allowing TCP/IP connections and I have turned off the Windows Defender firewall. 

As I mentioned, when trying to access an existing datasource I get an "[SequeLink JDBC Driver]TCP/IP error, connection refused" error.  But then I noticed something interesting: if I try to create a new datasource I get a different error message, namely "The ColdFusion ODBC Server service is not running or has not been installed. " 

And certainly that is true as I went to Computer Services and found that "ColdFusion 9 ODBC Server" was not running.  Nor was "ColdFusion 9 ODBC Agent."  So it looked likeI was getting close, but when I went to start "ColdFusion 9 ODBC Server" I got a popup window saying "Windows could not start the ""ColdFusion 9 ODBC Server" on the lcoal computer Error 1053.  I looked at the error log and it showed:

The description for Event ID 0 from source SequeLink Service Starter cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer. If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event: Missing datamodel path

Does anyone have a suggestion as to what I should try next?

[BKBK]

What happens when you restart Windows, then check whether every ColdFusion-related service has restarted or can be restarted?

[fredp60157821]

BKBK, When I restart Windows the following ColdFusion related service are started:

ColdFusion 9 .NET Service, ColdFusion 9 Application Server, ColdFusion 9 Search Server and ColdFusion 9 Solr Service.

The following services are not started:ColdFusion 9 ODBC Agent and ColdFusion 9 ODBC Server.  If I try to start ColdFusion 9 ODBC Server manually I get the message "Windows could not start the ColdFusion 9 ODBC Server service on local computer.

[BKBK]

Yet another possibility: use Windows' System Restore to return the entire machine to a previous state.

[fredp60157821]

BKBK, I tried your suggestion of restoring Windows to a prrevious state.  But I am not sure if the latest restore I have is before when the problem started to appear.  In any case I did do a restore (which took a long time) but it did not solve the problem.  Now I should mention that one thing that concerned me was that during the restore I keep seeing emssgaes like, "Please wait installing updates."  That happened often.  I was hoping it would just do a restore without doing any updates. But, in any case, it didn't reset my system to a working state.

[BKBK]

From what you describe, it might no longer be possible to return the system to the previous state. Oh, well. At least, we can now rule that out as a possible solution.

Before you continue, make sure to restore Windows to the current update.

[Charlie Arehart · Accepted solution]

Fred, a couple of things will likely get this resolved quickly (no need to reinstall or restore anything, likely).

First, as for your inability to start the CF9  ODBC service, can you confirm something, since you mention having installed CF2021? Do you also see the CF2021 ODBC service running? If so, stop that. Now does the CF9 one start?  If so, the problem is likely that they both wanted to use the same port.

And here is my guess on what happened:  I suspect that when you installed CF2021, it SETUP the service but could not START it (for the same reason that your CF9 one was then running). Then when you restarted the box (for the Windows update), it may be that the CF2021 ODBC service started FIRST, rather than the CF9 one. That's why you got the failures in CF9, and why you could not start the CF9 ODBC service until you stopped the CF2021 one. (Usually CF DOES choose different ports for things like this. Is there any chance you had stopped CF9 when you were installing CF2021?)

But second, someting we should address also is why you are still using ODBC for SQL Server? You really don't need to, since CF6 in 2002 added built-in JDBC drivers. They left the ODBC support there for those who needed it, and perhaps for those who were just more familiar with it. But really, there should be no reason to still use it in 2021.

So separate from all the above, have you tried creating a new DSN in the CF Admin, selecting "Microsoft SQL Server" as the driver type instead, and then pointing to the same info that would have been in your ODBC datasource definition? That would be the SQL Server ip address, port, and database name (and username and password, if needed). Does that verify?

Note that you could give it a different CF DSN name, just while checking that out. If it WORKS, great. You could consider then testing your app against this new DSN. You could rename the original one (to yourdsn_odbc, for example, and momentarily your apps would fail) then quickly rename this new DSN to yourdsn, whatever that name is.

Make sense? Let us know how it may go.

[fredp60157821]

Charlie, thanks for taking the time to make such helpful suggestions:

(1) Suggestion:

First, as for your inability to start the CF9 ODBC service, can you confirm something, since you mention having installed CF2021? Do you also see the CF2021 ODBC service running? If so, stop that. Now does the CF9 one start? If so, the problem is likely that they both wanted to use the same port.

Answer: After installing CF2021 I had both CF9 and CF2021 running.  Both Administrator programs came up OK.  But CF2021 also could not connect to my datasources.  And both ColdFusion 9 ODBC Agent and ColdFusion 9 ODBC Server and their CF2021 equivalents were not running.  And neither could be started manually.

(2) Suggestion:

But second, someting we should address also is why you are still using ODBC for SQL Server? You really don't need to, since CF6 in 2002 added built-in JDBC drivers. They left the ODBC support there for those who needed it, and perhaps for those who were just more familiar with it. But really, there should be no reason to still use it in 2021.

Answer: This is a great suggestion!  I never even knew you could do that!  I just tried that with:

CF Data Source Name
Database
Server	Port
User name
Password	(16-character limit)

 and got the message "The requested instance is either invalid or not running."  Possibly I am specifying the Server incorrectly.  I am going to work along this new direction.  Thanks so much!

[fredp60157821]

I just realized my copy and paste did not work.  here are the values I have:

Data & Services > Datasources > Microsoft SQL Server
Microsoft SQL Server : TEST_SQLSERVER
CF Data Source Name : TEST_SQLSERVER
Database : UUFCC
Server : MAINPC\SQLEXPRESS Port:1433
User name : UUFCC_SU
Password : **********

As I said, maybe my formatting of the Server as "MAINPC\SQLEXPRESS" is incorrect.

[fredp60157821]

Charlie, What a great suggestion!  I changed my CF datasources to use the native SqlServer drivers and everything is back up and running marvelously!  The ODBC drivers don't work.  But, in a way, who cares?  It would be nice to get things back the way they were when everything worked, but right now I can contiue developing.  Thanks again.  A workaround I hand't thought of.

[Charlie Arehart]

Great to hear, and glad to help. 

You'd not be the first person to still be using odbc when jdbc would work. 🙂 Just easy to stick with what you know, and what has worked for years. 

As for the odbc issue, as you say it may not be worth digging into further. If it's somehow an ongoing issue for others, we could expect they would raise it in a new thread.

With things solved, it could help other readers if you'd mark my previous response as the answer (I realize it's the second suggestion rather than the first, but it would cover both).