Sage 100 Web Services fields return blank or null results on some fields or tables
Summary
Sage 100 Web Services fields return blank or null results on some fields or tables
Cause
Insufficient ODBC permissions via Sage 100 ODBC security
Backup Warning
Use caution when working with the below product functionality. Always create a backup of your data before proceeding with advanced solutions. If necessary, seek the assistance of a qualified Sage business partner, network administrator, or Sage customer support analyst.
Resolution
Use the ODBC Security tab in Role Maintenance to assign permission levels to data tables and fields. This option allows you to restrict the data that can be retrieved by a user using the ODBC driver. If the user does not have the permission rights that match or the user’s permission rights are below what is assigned to the table or field, the ODBC driver will return null values. Because the use of this utility requires detailed knowledge of the data files, this option should be secured by assigning a role to the user(s) to prevent access by unauthorized personnel.
In Sage 100 ERP 2013 and higher do the following:
- Go to Library Master, Setup, System Configuration, Security Tab
- Check the ODBC Security option “Enable ODBC Security within Role Maintenance” and press Accept
- Open Library Master, Main, Role Maintenance
- Create a new role and for test purposes, select everything on all tabs including the ODBC Security Tab. Click Accept
- Open Library Master, Main, User Maintenance
- Create a user name and password (DO NOT LEAVE THE PASSWORD FIELD EMPTY). Give the user access to the Company Code with the role you create
- Click Accept
- Open Library Master, Main, Company Maintenance
- Click on Preferences tab and confirm that Allow External Access is checked
- Click Accept