frasierphilips
asked on
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)
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)
ASKER
Is Sage a MySql database? It comes with its own ODBC hosted driver
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.
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.
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.
Check it has been fully patched it should be Version 16.0.17.0158 - Help>About
Sage is NOT MySQL
Sage is NOT MySQL
ASKER
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.
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
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
you could try using the DSN with something else like Excel to see if it works.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No answers were forthcoming and I want to close the question.
DBD::mysql is the normal/standard module used when connecting to a mysql database.