Typically the SQL permissions for Sage databases are set up properly if normal functions work.
For 3rd party databases (like you are referencing) it depends on HOW the database is referenced. Most likely the user in question needs db_datareader added to the database (to be queried through ODBC).
The Excel templates can include SI data objects (which should use MAS_Reports for authentication) and direct data queries... which rely on user permissions.
------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
------------------------------
Original Message:
Sent: 09-09-2022 16:51
From: Shannon Stanley
Subject: Sage Intelligence SQL Error when running a report
@Kevin Moyes would the same directions listed in the article above work for the following error if the user is getting this when trying to run SI reports in premium version? This is a new user we are trying to setup for SI reporting.
Description : It was not possible to connect to the database.
The error returned was :
Cannot open database "EpicData" requested by the login. The login failed.
SQLState = 42000
Please check with the administrator that the connection information configured for this data container is valid.
Number : -1147181502
Source :
Driver Error : Unknown
SQL State : Unknown
------------------------------
Shannon Stanley
Sage 100 Consultant
Ardent Consulting LLC
------------------------------
Original Message:
Sent: 10-08-2019 09:53
From: Kevin Moyes
Subject: Sage Intelligence SQL Error when running a report
The SQL access I mention was for the user, not the Sage logins. Can the user access SQL data through a non-SI Excel query? Any SOTAMAS90 type queries have to be done differently with Premium / SQL.
------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
Original Message:
Sent: 10-07-2019 20:22
From: Michele Herzog
Subject: Sage Intelligence SQL Error when running a report
I have done all of these steps to no avail. I'm checking what boxes should be checked as per the attached.
------------------------------
[Michele] [Herzog] [CPA,CITP, CGMA]
[Overland Park] [KS]
[816-520-1365]
Original Message:
Sent: 10-07-2019 09:39
From: Kevin Moyes
Subject: Sage Intelligence SQL Error when running a report
Does the user have read access to the SQL databases? (Any extra queries in the templates)?
Issues with SI have come up frequently for one of our Premium customers, and here are a couple things we commonly have to check (along with the add-in architecture):
- Ensure user has full control over the following directories in C:\Program Files (x86)\Common Files\
- BIComponents
- BIComponentsDotNet
- BIExcelFunctions
- BIGenerator
- First time launching Sage Intelligence, run Sage 100 as Administrator and run a Sage Intelligence report – Excel should be set up automatically.
------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON