x Want to participate? Join the group to interact
Options
  • State Not Answered
  • Replies 5 replies
  • Subscribers 108 subscribers
  • Views 2838 views
  • Users 0 members are here
Browse Forums
Announcements
293
topics
370
replies
Business Object Interface
1k
topics
7k
replies
Core Financial Modules
2k
topics
8k
replies
Customization and Productivity
3k
topics
12k
replies
Distribution, Manufacturing, and Applications
2k
topics
7k
replies
General Discussion
1k
topics
3k
replies
Partner Cloud
45
topics
92
replies
Payroll, Time Tracking, and Job Cost
777
topics
1k
replies
Reporting and Analytics
963
topics
3k
replies
Sage CRM for Sage 100
164
topics
357
replies
Technical and Installation
2k
topics
9k
replies
Year-end Forum
89
topics
237
replies
MarkB70

Adding ODBC connection in Power BI service fails; works locally

Posted By

Greetings, 

I'm running into a roadblock attempting to add an ODBC data source via Power BI Service via +New / Data sources page from the following URL. https://app.powerbi.com/groups/me/gateways 

Here are a few facts:

  1. The ODBC driver (for Sage 100) is 64-bit and on a workstation that contains Power BI Gateway (July 2022).  On the debug tab of the ODBC Manager, the test is successful: Connection succeeded. Datasource includes xxxx tables.
  2. The Gateway passes the network test. Its status is online.
  3. From Power BI Service (New), if I purposely mistype the DSN name (system DNS, not user DSN) I receive the following message (expected result). ODBC: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.   Note: This helps illustrate that Power BI Service is able to communicate with the driver:.
  4. If I provide the correct DSN, I received the following error: ODBC: ERROR [S0000] [ProvideX][ODBC Driver][PVKIO]Logon failed
    ERROR [S0000] [ProvideX][ODBC Driver][PVKIO]Logon failed.
  5. I've tried several variations with/without credentials embedded in the DSN string; always with the same result.
  6. From the workstation that contains the Gateway, I can successfully use Power Query in Excel locally to use the same DSN I am attempting to use in Power BI Service and it works fine.
  7. The network firewall was temporarily disabled; same results. 

Does anyone have any suggested troubleshooting steps?  

Parents
  • 0

    Here it is:

    DSN=Sage100HOT64; UID=XXX; PWD=XXX; Directory=\\SERVER\Data\Sage2019\MAS90; Prefix=\\SERVER\Data\Sage2019\MAS90\SY\, \\SERVER\Data\Sage2019\MAS90\==\; ViewDLL=\\SERVER\Data\Sage2019\MAS90\HOME; Company=XXX; LogFile=\PVXODBC.LOG; CacheSize=4; DirtyReads=1; BurstMode=1; StripTrailingSpaces=1; SILENT=1; SERVER=NotTheServer

  • 0 in reply to MarkB70

    Thanks for posting the DSN being used. This is most likely a case that the gateway service account does not have permissions to the UNC being used for the DSN. You should try switching the account to a user that does have access to the UNC. Am attaching a link to Microsoft's site that details this.

Reply
  • 0 in reply to MarkB70

    Thanks for posting the DSN being used. This is most likely a case that the gateway service account does not have permissions to the UNC being used for the DSN. You should try switching the account to a user that does have access to the UNC. Am attaching a link to Microsoft's site that details this.

Children

*Community Hub is the new name for Sage City