16 character password limit; need work-around

[quasigenx]

Looks like CF7 has a documented 16 character limit on datasource passwords. Our
production server password is 18 characters.

I brought this up on the cftalk mailing list, and the suggestion was made to
try a third party JDBC driver. I tried the Microsoft JDBC driver, as well as
jDTS. Both exhibited the exact same behavior; my sa login (which is under 16
chars) worked, but the regular login did not. The exact error is:

Connection verification failed for data source: BULLHORN_MASTER
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]Login failed for user 'bullhorn1'.
The root cause was that: java.sql.SQLException: [Microsoft][SQLServer 2000
Driver for JDBC][SQLServer]Login failed for user 'bullhorn1'.

ColdFusion appears to be truncating the password at 16 characters before it
passes is to JDBC, regardless of the driver used. When I configure this new
driver to use the sa username/password, it works great. When I change just the
username and password to our regular login (bullhorn1), I get a "Login failed
error". It's not saying the password is too long, or that JDBC is rejecting the
password due to length, it's saying login failed. This leads to the assumption
on my part that CF is truncating or otherwise altering the password before it
hands it off to the driver.

Also interesting to note is that even when using the custom JDBC drivers and
specifying the "other" driver type, it warns you on that definition screen that
there is a 16 character limit.

I'm ready to call out the CF development team. True, it's documented right
there in the app, but this could very well throw a huge monkey wrench into our
CF7 plans, for a very stupid issue. Please, anyone else have an ideas?

Before you say "just change the password". That is not an option. We are
talking about 30 different SQL Servers, and the password is probablly hardcoded
in a bunch of places. Not our actual site code, but doubtless many little
utilities we have written over the years. Regardless, JDBC drivers can handle
passwords longer that 16 character just fine, this is strictly a ColdFusion
"feature".

[quasigenx]

Since this post, I have tried a couple of other options, to no avail.

1. I tried using the API admin tool to set a longer password. This created the
datasources ok, but exhibited the same behavior or truncatint the password
passes to JDBC.

2. I tried ODBC, and was flabbergaster to fing that it had the same
limitation. I even tried trusted connection ODBC, but that looks like it would
involve re-installing SQL server in mixed authentication mode. Forget that.

At this point, I have literally no fallback position besides:

A. Changing the password on all 30 SQL Server instances.
B. Reverting my shiny new code back to CF5.5 and never using CF7 again.

Any developers/support reps want to weight in on this?