We help IT Professionals succeed at work.

ODBC connection error after upgrading to Sage 2012

David Haycox
David Haycox asked
on
We have an ASP application that pulls data from Sage 50 Accounts for display on web page.  We recently upgraded from Sage v15 (2009) to v18 (2012).  After setting up the System DSN in ODBC with the new Sage driver, we now get the error as listed in the image.

Although it states "User ID or Password invalid" that is not the case - it's definitely correct.

Could the section "The driver doesn't support the version of ODBC behavior that the application requested" be more relevant?  Sage must have changed the way their ODBC driver works, but they don't support it.

Sage 2012 is patched with updates 1,2,3 and the ODBC driver update.

Any assistance or advice would be much appreciated.
Sage-ODBC-error.JPG
Comment
Watch Question

Bruce DenneySage 50 Consultant and Integrator

Commented:
You need to install the ODBC V 2012 hotfix,

www.sage.co.uk/accountsupdates

it is in the Misc section
John EastonDirector

Commented:
When you update Sage, make sure you have installed the ODBC drivers on the webserver.  They are not compatible with different versions.  We have similar issues with the new driver.

To overcome this we created a new System DSN, using the new Sage Driver.  From memory you have to enter the directory that the Sage data is stored in - this is the ACCDATA folder.

From my ASP app I then reference the following:

 
Set SageConn = Server.CreateObject("ADODB.Connection")
strCnn = "DSN=mySagev18; Uid=User; Pwd=Password; UseDataPath=No"

Open in new window


Obviously not my real username and password!  The solved the problem for me.
David HaycoxConsultant Engineer

Author

Commented:
Sorry, I did mention that we already installed that...
Bruce DenneySage 50 Consultant and Integrator

Commented:
You need to install the ODBC V 2012 hotfix, This is not pushed out on the Automatic updates, you have to do it manually.

www.sage.co.uk/accountsupdates

it is in the Misc section
Bruce DenneySage 50 Consultant and Integrator

Commented:
Can you check the version of C:\WINDOWS\system32\S18DBC32.dll

David HaycoxConsultant Engineer

Author

Commented:
It's 18.1.0.8

Currently looking into JEaston's answer, watch this space.
Bruce DenneySage 50 Consultant and Integrator

Commented:
Setting up a DSN is a good idea as when they change in future, you can change the DSN once and not all your apps spreadsheets, databases and so on that use the ODBC driver.

In this case though it looks like you are already using the V18.1 driver.

So you would have to have forgotten to code the data path and relied on the default data path the ODBC driver used.  It may well be pointing at the demo or practice data, try username MANAGER and no password and see what you connect to.

David HaycoxConsultant Engineer

Author

Commented:
No joy so far, we were using the same syntax apart from "UseDataPath=No", but adding that makes no difference.

I also tried pointing at the demodata; again I get the same error regardless of whether I use correct credentials or not.

Any other ideas?
John EastonDirector

Commented:
The other issue I came across when doing this was to do with user permissions.  However I believe this was an issue for both old and new versions.  However, if you data is located on another server we have issues with NT authentication.

To resolve this we had to add an ImpersonateUser object as detailed below.

 
Set objLogon = Server.CreateObject("LoginAdmin.ImpersonateUser")
objLogon.Logon "AdminUser", "AdminPassword", "Domain"
session.lcid=2057

Open in new window


If your data is on the same server, and hasn't moved, this is not likely to be the issue.  However, just in case I thought I would post it.
Consultant Engineer
Commented:
Just had an email back from Sage who pointed out that  .NET v1 is no longer supported by Sage 2012 - because:

"The SDO also uses Sage.Cryptography to validate the username and password in v18 and this renders desktop apps targeting 1.1 incompatible."

So far simply changing to .NET v2 appears to be the fix.  I still have to make this change on the live server (which is SBS2003 with the ASP app running on the default web site), but that's another story.

Thanks for all the tips so far.
Bruce DenneySage 50 Consultant and Integrator

Commented:
Hmmn, when you upgraded the installation on the server it should have upgraded .net so seems strange.
David HaycoxConsultant Engineer

Author

Commented:
Thanks for the tips.  Answered question myself in the end though.