win32::ODBC Sage Account Package issue only on Windows 7 64 bit

My client has just upgraded all their PCs to Windows 7 64 bit.  The file sharing machine is running a booking system, accessed via a web browser, using Xampp, MySQL and Activestate Perl.

Also on the file server, is  a folder containing the data files for their Sage Accounts 2010 accounting package.  These systems have run for many years under XP Pro 32 bit however when the booking system has been transferred to Windows 7 64 Bit, an anomaly has arisen (everything else works perfectly)

The booking software establishes read-only access to the Sage database via the Sage 32 Bit ODBC driver in order to download financial information for the purposes of calculating commissions.

I installed Sage on the file sharing machine and changed the ODBC data source (using syswow64/odbcad32.exe) to the correct folder- I've checked that the software can see the driver (it can) however when I try to invoke the connection I get an invalid username/password error (I know both un/pw are correct).

I have also tried to connect via DBD/DBI and get the same result, the exact error message being:-

User ID or Password invalid (SQL-28000) [state was 28000 now 01000] The driver returned invalid (or failed to return) SQL_DRIVER_ODBC_VER: 2.00 (SQL-01000) [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr). (SQL-01000)
frasierphilipsAsked:
Who is Participating?
 
frasierphilipsAuthor Commented:
I downloaded a 3rd party ODBC connection tool and it connected perfectly
0
 
FishMongerCommented:
Have you tried using DBD::mysql instead of DBD::ODBC?

DBD::mysql is the normal/standard module used when connecting to a mysql database.
0
 
frasierphilipsAuthor Commented:
Is Sage a MySql database?  It comes with its own ODBC hosted driver
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Bruce DenneySage 50 Consultant and IntegratorCommented:
I suspect that the DSN is not correct.

The drivers when used without creating a DSN use the default path and on this PC that could be the Demo data or something else, if you make a specific DSN it should be fine.  

The default username and password is Manager and no password if you try that with your existing setup, I expect it will return data form the demo or practice data.
0
 
Bruce DenneySage 50 Consultant and IntegratorCommented:
P.S. V2010 is loosing support in a a couple of months as part of the Sage life-cycle policy. So upgrading to a version that is officially supported on more recent versions of windows might be a good idea.
0
 
Bruce DenneySage 50 Consultant and IntegratorCommented:
Check it has been fully patched it should be Version 16.0.17.0158 - Help>About

Sage is NOT MySQL
0
 
frasierphilipsAuthor Commented:
Nope, i'm completely certain that the settings are correct - I'm beginning to wonder if it's something to do with the fact that the version of Perl in my copy of Xampp is in some way incompatible with the Windows 7 infrastructure, particularly in the way that it's passing the username and password to the system driver.
0
 
FishMongerCommented:
It's very likely that the problem is with the password hash string.

Older versions of mysql used a 16 byte hash string and newer versions use a 41 byte string.  You can enable backwards compatibility in the newer versions if you want to stay with the 16 byte password hash, but that's not the best thing to do.  You should reset the passwords so that they are using the 41 byte hashing string.  You may first need to alter the size of the password field to accommodate the longer string.

Password Hashing in MySQL
0
 
Bruce DenneySage 50 Consultant and IntegratorCommented:
you could try using the DSN with something else like Excel to see if it works.
0
 
frasierphilipsAuthor Commented:
No answers were forthcoming and I want to close the question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.