Fix Sage 100 SOTAMAS90 ODBC Connection Errors
Sage 100 ODBC SOTAMAS90 connection error
Also searched as
- SOTAMAS90 DSN driver not found
- Sage 100 ODBC 32-bit vs 64-bit Excel Power BI
- Sage 100 ODBC invalid company code
- Crystal Reports Sage 100 ODBC cannot connect
Short answer
Most Sage 100 ODBC failures are one of four things: the DSN was created in the wrong 32-bit or 64-bit ODBC administrator for the tool you use, the DSN path or company code is wrong, the Windows account cannot read the MAS90 folder, or the Sage 100 user lacks external access. Recreate the DSN in the matching administrator and test with the driver's own Test button before blaming Excel or Power BI.
Applies to: Sage 100 Standard, Advanced and Premium with the Sage 100 ODBC driver (SOTAMAS90 is the conventional DSN name), Windows workstations and servers, Excel, Power BI, Crystal Reports and SSRS clients
Diagnose the ODBC connection
- 1Decide the bitness of the client tool. 32-bit Excel, older Crystal and Visual Studio 2010-era tools need a 32-bit DSN; 64-bit Excel and Power BI Desktop need a 64-bit DSN and a 64-bit driver.
- 2Open the correct ODBC Data Source Administrator: C:\Windows\SysWOW64\odbcad32.exe for 32-bit, C:\Windows\System32\odbcad32.exe for 64-bit. The Start menu shortcut named ODBC Data Sources often opens only the 64-bit one.
- 3Check the System DSN tab for SOTAMAS90 (or your name). If it is missing, add it with the Sage 100 ODBC driver listed in that administrator; if the driver is not listed, run the Sage 100 workstation or ODBC driver setup for that bitness.
- 4Open the DSN and confirm the Directory points at the MAS90\Home folder, using a UNC path for shared installs, and that the Company field uses a valid company code.
- 5Use the Test connection button. A failure here is independent of Excel or Power BI and narrows the cause.
- 6Confirm the Windows account running the tool can read the MAS90 folder and the data folder (MAS90\SOA and company data), and that it is not blocked by antivirus or by a mapped-drive-only path.
- 7In Sage 100 Role Maintenance, make sure the user has the external access permission, and that the password entered is current.
- 8If the DSN works for one company but not another, check that the second company has been through its latest update and is not locked by a pending conversion.
The 32-bit versus 64-bit trap
A DSN created in one administrator is invisible to a tool of the other bitness. This is why "it works in Crystal but not in Power BI" is so common. The fix is not to change the DSN settings but to create a second DSN, with the same name if you like, in the other administrator using the driver that administrator lists.
If the 64-bit administrator does not list the Sage 100 driver, the 64-bit driver is not installed. Many sites only ever installed the 32-bit component with the workstation setup. Install the 64-bit ODBC driver from the Sage 100 installer for your version, then rebuild the DSN. Do not try to point a 64-bit tool at a 32-bit driver; Windows refuses it with architecture mismatch messages.
Company code, path and security
The Sage 100 driver is company-specific: the company code in the DSN selects which company's tables you see. Typos, a lowercase code where the system expects uppercase, or a company that was archived all produce a generic login failure. Create one DSN per company or use a connection string that overrides the company.
Path failures usually mean the path works for you but not for the service account (SSRS, Power BI gateway, scheduled Crystal). Use a UNC path, and grant read access on the Home and company data folders to the service account. A drive letter mapped in your session is not present in a service session.
Security lives on the Sage side too. A user may log on to the desktop successfully and still be refused by ODBC if their role does not allow external access. A password change that was done in Sage but not in a saved connection string also surfaces as a plain login error.
-- Typical Excel/Power BI ODBC connection string DSN=SOTAMAS90;Company=ABC;UID=reportuser;PWD=******** -- Quick sanity query once connected SELECT CustomerNo, CustomerName, ARDivisionNo FROM AR_Customer
UDFs, slow queries and read-only limits
User-defined fields appear as columns prefixed with UDF_ on the base tables, so they are available to reports without extra setup. Dates are often stored as strings or in formats that need conversion in the query layer. Large joins across tables like SO_SalesOrderDetail, IM_ItemTransactionHistory and GL_DetailPosting can be slow because the driver reads ProvideX data files rather than a relational engine.
For heavy reporting, extract on a schedule to a reporting database instead of letting Power BI scan the live files, which also keeps the load off users in the middle of the day. ODBC is read-only for reporting use; writes should go through the Business Object Interface or Visual Integrator so validation runs.
Common pitfalls
- !Creating the DSN in the 64-bit administrator for a 32-bit Excel (or the reverse) and concluding that the driver is broken.
- !Using a drive letter in the DSN directory for a service or gateway account.
- !Pointing reports at the live company during the day with unbounded queries, which can slow data entry for users.
- !Forgetting that the company code is part of the DSN, so a new company needs its own connection.
- !Trying to write through ODBC, which skips Sage validation and is unsupported for most writes.
How an ERP-grounded AI assistant handles this
Because the driver exposes a read-only view of the tables, a grounded assistant (ERPray, or DataRay for a direct database or file source) can sit on the same ODBC or exported dataset. It maps a plain-language question to the correct table and UDF columns and shows the SQL it ran, so the user can verify it. It does not repair a broken DSN; the connection checks above still apply, and the assistant is useful once the connection is healthy.
Frequently asked questions
Why does my SOTAMAS90 DSN work in Crystal but not Excel?
Usually a bitness mismatch. Crystal may be 32-bit while your Excel is 64-bit, or the reverse. Each uses a different ODBC administrator and driver. Create the DSN in the administrator that matches the failing tool and test it there.
Where do I install the 64-bit Sage 100 ODBC driver?
It comes from the Sage 100 installer for your version, as a separate ODBC component for 64-bit. Check the release notes and installer options for your version. If the 64-bit administrator does not list the driver, it is not installed.
Can I use ODBC to import orders into Sage 100?
Not safely. ODBC is intended for reading data. Imports should go through Visual Integrator or the Business Object Interface, which apply Sage's validation, numbering and posting logic.
Do I need one DSN per company?
In practice yes, or a connection string that supplies the company code. The driver is tied to a company, so a DSN pointing at company ABC will not show tables from company XYZ.
Related
Sage 100 Business Object Interface (BOI): Working VBScript Example
The Sage 100 Business Object Interface exposes the same business objects the screens use through a COM object called ProvideX.Script. You initialise it with the MAS90\Home path, create an SY_Session object, log on, set the company, then create a business object such as AR_Customer_bus and read or write through it. Because it runs the real business logic, it is the supported way to write data in code, unlike direct file or ODBC writes.
How-toHow to Add User Defined Fields (UDFs) in Sage 100
In Sage 100 you add a user defined field through Custom Office: open the Customizer Selection for the module, create the field with a name, type and length, then place it on the relevant data entry panel using the Customizer. UDFs are stored in the module's tables with a UDF_ prefix, which is how they appear in ODBC, Crystal and imports once the field is defined.
How-toHow to Void a Check in Sage 100
Sage 100 voids checks through Accounts Payable > Main > Check and Payment Voiding. Select the bank code, choose the check number, and Sage reverses the Cash Disbursements Journal entry and reopens the invoice it paid. A check that already cleared in Bank Reconciliation or falls in a closed period needs an extra step before it can be voided cleanly.
Error fixSage X3 Syracuse Service Won't Start: Troubleshooting Guide
When Sage X3 users reach no login page, check the Syracuse Windows service and its dependencies in order: the MongoDB service that holds Syracuse configuration, the port and certificate Syracuse binds to, then the connection from Syracuse to the X3 runtime. Syracuse log files normally state which step failed, so read the log before restarting things repeatedly.
How-toHow to Create a Generic Inquiry in Acumatica
Generic Inquiries (GI) are Acumatica's no-code query builder for joining tables, adding filters, and exposing the result as an inquiry screen, dashboard data source, or API endpoint. Open System > Customization > Generic Inquiry, add your base table, define joins and conditions, then run and save. No SQL or customization project is required.
Error fixFix Odoo AccessError: You Are Not Allowed to Access Records
Odoo raises AccessError when a user's security groups do not grant a CRUD right on a model through ir.model.access.csv, or when an ir.rule record rule filters the specific record out for that user or company. Fix it by adding the user to the group named in the error, editing the model's access rights, or reviewing the record rule's domain under Settings > Technical > Security.
AI for ERPAI for Sage 100, Grounded in Your MAS90/200 Data
Put AI on Sage 100 and the Sage 100 Manufacturing (MRP) data set without sending data to a public model. Private LLMs and agents grounded in your MAS90/200 tables.
Stuck on Sage 100?
Talk to engineers who work inside Sage 100 every week, and who build private AI that answers these questions from your own ERP data.