Inspiring

Answered

CF 2021 won't add ODBC Socket connections after updates

Forum|Forum|2 years ago
December 7, 2023
3 replies
1225 views

Hi,

Earlier this week we migrated from 2016 to 2021. The Microsoft SQL Server DSN no longer worked after migration. But since time was of the essence, I set that issue aside and tried ODBC Sockets. This worked just fine. I should note that I did this before running all the patches and updates.

Today I migrated another server, same basic method but I did the patches before adding ODBC socket connections. Now if I select ODBC socket as driver type and click add, the form to enter connection info does not appear at all.

Oddly, when I go back to the first server migrated, the same thing now happens if I try and add a new odbc socket or edit the existing ones. But the existing DSNs still work...

Any thoughts? TIA!

This topic has been closed for replies.

Correct answer chrisc42802438

While I never got the ODBC connection working, I did get the JDBC connections working, which was the preferred outcome anyway.

From the point where all packages were installed and updated and core was on update 12, I uninstalled all packages and rolled back the updates. From there I installed only the SQLserver package, at which point the connections worked (after the AuthenticationMethod=ntlm fix). Core was updated through update 5.

Tried to then update to update 12, but it would just hang up. At that point I tried the solution I used a couple days ago which was to update all the packages, which forces the core update.

At this point SQL Server connections worked and all updates were applied. Phew.

No idea why it worked like this - probably something to do with the updates, or at least with trying to install them all at once? Also not sure why Core updates weren't working unless forced by package updates. ¯\_(ツ)_/¯

Thanks for your help Charlie!

chrisc42802438AuthorCorrect answer

Inspiring

While I never got the ODBC connection working, I did get the JDBC connections working, which was the preferred outcome anyway.

From the point where all packages were installed and updated and core was on update 12, I uninstalled all packages and rolled back the updates. From there I installed only the SQLserver package, at which point the connections worked (after the AuthenticationMethod=ntlm fix). Core was updated through update 5.

Tried to then update to update 12, but it would just hang up. At that point I tried the solution I used a couple days ago which was to update all the packages, which forces the core update.

At this point SQL Server connections worked and all updates were applied. Phew.

No idea why it worked like this - probably something to do with the updates, or at least with trying to install them all at once? Also not sure why Core updates weren't working unless forced by package updates. ¯\_(ツ)_/¯

Thanks for your help Charlie!

Charlie Arehart

Community Expert

All very odd, yes. But good job sharing the details. Perhaps Adobe or others may benefit from them. Glad all's well, finally.

/Charlie (troubleshooter, carehart. org)

chrisc42802438Author

Inspiring

Thanks. Love CF for development but migrating versions always seems to have some hidden surprises/mysteries! 🙂

chrisc42802438Author

Inspiring

I'll add that the 'AuthenticationMethod=ntlm' fix for the jdbc connections outlined here (https://community.adobe.com/t5/coldfusion-discussions/cf21-sql-server-dsn-login-error-with-windows-auth-worked-in-cf16-and-cf18/m-p/12751072) doesn't seem to work for me either...

Charlie Arehart

Community Expert

Doesn't work for the odbc or the jdbc driver? And does this mean your error in sql sever was about tls? Please do tell us more.

Also, were you in fact using the Adobe provided sql server driver? Or did you obtain the Microsoft sql server jdbc driver, which you'd implement in cf as an "other" driver type? That affects what connection string is needed for tls issues.

/Charlie (troubleshooter, carehart. org)

chrisc42802438Author

Inspiring

Sorry, doesn't work for JDBC.

From 2016:

Driver MSSQLServer
JDBC URL jdbc:macromedia:sqlserver://[server]:[port];databaseName=ccssedata;SelectMethod=dir
ect;sendStringParametersAsUnicode=false;querytimeout=0;MaxPooledStatements=100;jdbcbehavior=0

From 2021:

Driver MSSQLServer
JDBC URL jdbc:macromedia:sqlserver://[server]:[port];databaseName=CCSSEDATA;SelectMethod=dir
ect;sendStringParametersAsUnicode=false;querytimeout=0;MaxPooledStatements=100;jdbcbehavior=0

Charlie Arehart

Community Expert

While your focus is on odbc, and the oddity you're seeing (which I'll note I've never heard of), you mention using sql server--which you'd tried first and had trouble with. Really, resolving THAT should be your focus. Odbc is 20 yr old tech not suited to most apps. The built-in cf datasource drivers use jdbc instead.

Unless you would insist on trying only to plow through on getting odbc working (in which case I'll leave that to others to help with), tell us more about what didn't work with the sql server driver. Was it in adding the dsn? Verifying it? Running code using it?

Also, is this move to cf2021 on the same server that ran cf2016 or another?

/Charlie (troubleshooter, carehart. org)

chrisc42802438Author

Inspiring

Thanks, the issue is in adding the DSN - adds but doesn't connect. I'm getting a time out error, though it's happening pretty much instantaneously:

Connection verification failed for data source: ccssedata_ole_import
java.sql.SQLException: Timed out trying to establish connection

Charlie Arehart

Community Expert

That timeout would suggest this cf server can't reach that sql server....which is why I asked if this was a new box running cf2021.

Try reaching the sql server ip or domain via a command line ping from the cf server. Does that work?

/Charlie (troubleshooter, carehart. org)

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded