Sage 100

Problem connecting Microsoft Power BI to Sage 100 data via standard SOTAMAS odbc connection. It keeps popping up the Sage User login dialog multiple times. Sometimes only 2, but it has prompted me over 10 times to access Sage 100 data tables via ODBC. I've seen it happen on multiple computers and with other users. Does anyone know what the problem is and how to correct it other than setting up a silent login on every single client workstation?

Comment from one of our developers...Can't connect to the standard (User) ODBC connection. Must setup a SYSTEM ODBC DSN Connection. It allows you to setup a default login and password for the connection.

Thanks for the quick response Jeff. Boy, I'd sure like to know **why** the standard ODBC login works with Microsoft Query, but not with Power Query/Power BI. Plan B is to modify the connection string in the Power BI connection with login info. That would eliminate the need to create a silent DSN on every single PC that might use the connection.

@DanBurleson might have a thought!!

Yes, this is a drag. It makes me think they must have had a plan to work around the prompts. If you don't mind storing directly into the registry, one can add 5 lines of VBA code (attached) to an Excel Auto_Open event (or other appropriate place) to set company, user and password automatically. On an ad hoc basis, one can manually set the company and credentials in the control panel for SOTAMAS90 and they will stay until the next MAS login (that didn't work back in the day).

@ClarkWalliser Of course the above use of an Excel macro will encounter security warnings (can be individually be disabled) that may be as undesirable as setting up a silent DSN at each user's workstation or domain login. - catch 22.

That's why I like hitting my hand with a hammer, because it feels so good when I stop... Wow, has anyone run into this type of problem on other odbc data connections, or is this unique to Sage 100?

Ok, I think I've found the solution! In Power Query and Power BI, go to Data Source Settings. Edit the Permissions/Credentials settings. Use the database login type. At user ID, enter the user ID plus the pipe symble | then the company code ID. Then enter the user password. This modifies the connection string for the SOTAMAS90 DSN used in this application globally. Note that there is also an option to modify file specific Data Source Settings also. Attached is a word doc with instructions and pictures.