Can't Connect Sage 100 to PowerBI Gateway

[Beej-NJ20]

I have successfully connected our Sage 100 2019 database to PowerBI Desktop and also successfully published a small test report to PowerBI Service. Now trying to establish a Gateway that would allow for data refresh and continually receive the following errors (FYI - the name for the DSN is correct and it exists identically as a User and System DSN):

Underlying error message:	ODBC: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259
Microsoft.Data.Mashup.ValueError.DataSourceKind:	Odbc
Microsoft.Data.Mashup.ValueError.DataSourcePath:	dsn=XXXXXX
Microsoft.Data.Mashup.ValueError.OdbcErrors:	#table({"SQLState", "NativeError", "Message"}, {})
Microsoft.Data.Mashup.ValueError.Reason:	DataSource.Error

Not sure why there is still some SQL value (this is just ODBC!) and really just need this to work. Please share any insights and guidance!

[davehus]

I've had a few issues with Line 50 this regard. Few tips...

Gateway needs to be set as a local system account under services.

ODBC driver needs to be installed on the gateway server with the correct connection string. There is usually a generic filepath that isn't where your datafile might be stored.

These might help..

[Beej-NJ20]

If I set the gateway to be a local system account, can other users access it or is it limited to only my user account? There is another developer that also uses this gateway configuration. Just to clarify the driver recommendation: the drivers are on the server where the OnPremise Gateway is installed and 64-bit DSNs are created. PowerBI Desktop can reach the source files using the DSN connections. Thanks in advance for your help!

[davehus]

No it shouldn't affect how users interact with the gateway. You could try it and revert back if you need to but I've no experiences of this happening.

[lbendlin]

Gateway cluster members behave like regular PCs. They broker the refresh requests from the Azure service and query the data in the on-premise realm. To be able to do that they need to know how to connect to the on premise data sources.  The SQl server driver is built-in but others (like Oracle, Hive, MySQL etc) may require installation and configuration of the native drivers or ODBC drivers depending on what you plan to use.

[Beej-NJ20]

Sorry for the delayed reply. Where in the OnPremise Gateway screen do you install drivers? The only options available are Status, Service Settings, Diagnostics, Network, Connectors and Recovery Keys?

[Beej-NJ20]

I wasn't aware that the ODBC drivers needed to be installed on the gateweay cluster members. I didn't find any instructions about that when troubleshooting. Can you please share some additional details?

[lbendlin]

Sounds like you didn't install the Sage ODBC driver on the gateway cluster members?