Friday, July 1, 2022

Sage 300 ERP - ODBC error - Invalid Database Specification

We utilize Sage 300 ERP from a Windows RDSH environment.  After upgrading to a 2019 environment and reinstalling Sage I had manually created the ODBC DSN.  Unfortunately this caused errors for end users "Invalid Database Specification".  
You'll find a lot of info that this is of course due to ODBC connection issues, and I kept finding that it referenced that the end users didn't have permission to access the SYSTEM DSN.
Fixes include making registry changes, using User DSN, ensuring you're using ODBCAD32, firewall issues, etc.

None of these worked for me until I found a quick mention on a Sage user forum thread stating to "Run As Administrator" Sage 300 so it could create the ODBC connection itself.

After I cleaned up the changes I had tried and deleted the ODBC I had manually created I did this and low and behold, that created the DSN and it works for end users.  (see very last post)

I despise Sage 300, I think it's poorly programmed from a Systems Administrator point of view.  Maybe it's an "Accounting Software" thing as I greatly dislike Quickbooks desktop / enterprise as well, and a few other accounting software's I've worked with.  
Oh well, it's installed and working now... until I deploy 2022...