Gateway connection error to NetSuite - cannot connect to any gateway instances of the cluster

[Anonymous]

Hi

We have a gateway setup on another machine between Power BI & NetSuite that had been working fine, until last weekend.

I think something has changed in NetSuite, the saved report URLs had all changed. In the .pbix file I needed to change the Source line code in the Advanced editor to point to the new URLs to refresh the file as I was also getting errors there.

On the published version in the web I’m also getting the below error:

I have also tried creating a new gateway and tried all the other combinations of Authentication Method and Privacy level but get the same error?

[Anonymous]

Hi,

Have you tried to configure the gateway in Power bi service thourhg ODBC path (Data source)? I am also trying to configure a Gateway in that way with no success 😞 I believe it is because I am not being able to figure out which is the appropriate connection string...

What I did in Power bi Desktop is to connect Power Bi to Netsuite through ODBC connection, DSN= Netsuite and a SQL statement and it worked fine, but after publishing to Power bi service I am unable to configure the Gateway properly

Hope this helps!

Kindest regards!

Marina

[Paul_Ben]

Hi @Anonymous 

Yes that is what I did in the end, rather than using URLs to saved reports that had the "enable web query" option ticked that had been working. I changed to an ODBC connection and that worked fine after that. 

If I understand it, you are having trouble with your Gateway once you publish it?

When you set up the Gateway, for the Connection string just ensure it is exactly as you have spelt it when you set it up in the ODBC, so the string is "dsn = " and then the name of the one you set up e.g. this is was what I had

The ODBC used in the published Gateway was set up on a Virtual Machine that was never turned off with the exact same name as the one I had on my machine for testing so that the scheduled refresh would not fail when my machine was turned off over night. Then just ensure you use this Gateway connection under the option: Settings/Datasets/Gateway connection for the required option

Hope this helps

[Anonymous]

Many thanks for your answer Paul!

I finally was able to connect my Power BI to Netsuite through ODBC the same way you mention in your answer, but now I am getting this error when trying to refresh in Power bi service and in power bi desktop.. 😞

ODBC: ERROR [HY000] [NetSuite][ODBC 64bit driver][64bit Client]The specified procedure could not be found. ERROR [HY000] [NetSuite][ODBC 64bit driver][64bit Client]An error occurred in the shared library manager. ERROR [HY000] [NetSuite][ODBC 64bit driver][64bit Client]The specified procedure could not be found. ERROR [HY000] [NetSuite][ODBC 64bit driver][64bit Client]An error occurred in the shared library manager.

This is horrible, when you get to resolve one issue and then another different happens... 😞

Many thanks again!

Marina 

[Paul_Ben]

Hi @Anonymous 

I'm not familiar with that error sorry but it may be to do with your code in the Query Editor. 

So it should start with: Source = Odbc.Query("dsn=NetSuite-PowerBI", "select ??? from ??? ")

Replacing the ??? with what you want from the tables with the necessary joins required like TRANSACTIONS.TRANDATE for the invoice date etc and  join on the TRANSACTIONS & TRANSACTION_LINES tables on the transaction_id columns

You may have already worked out what you want but I found this link useful when working out what tables and fields I needed:

https://www.netsuite.com/help/helpcenter/en_US/srbrowser/Browser2017_2/odbc/record/transaction_lines.html

[v-xicai]

Hi @Anonymous ,

You may check if the gateway service is in Run status.

1. Ctrl + Alt + Del to open the Task Manager
2. Click the "Services" tab
3. Find "On-premise data gateway service" in the list (sort by name or description, mine showed up with that name in the "Description" field (name value was "PBIEgwService".
4. Right click and click "Start" (mine was not running)

In addition, for the dataset that contains on premise and cloud data source, you also need to select Allow user's cloud data sources to refresh through this gateway cluster option Under Gateway Cluster Settings. See: Merge or append on-premises and cloud data sources.

You may resolve the issue via troubleshooting links:

https://docs.microsoft.com/en-us/power-bi/refresh-troubleshooting-refresh-scenarios .

https://community.powerbi.com/t5/Service/REFRESH-ERROR/td-p/840775

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

 Hi @v-xicai , thanks for your post

The Gateway is running and IT was able to see there has been spikes of usgae on the machine it is located on for some usgae of that Gateway.

Yes the option to allow cloud data source was already ticked from when it was running fine.

I just noticed if I just click on the Autenication Method and cycle it to another option and then put it straight back to Anonymous and then click Apply I get a different error messgae:

For the error code about credentials error, I haven't ever had to enter any as in the Gateway it was set to Anonymous and under the Datasets setting for Data source credentials it has the message "admin has granted access, credentials are not required"

I tried setting it to both Basic & Windows Authentication method and using my credentials I get the same above error.

I also tried to add a new Gateway with the same URL and just get the same errors.

Any ideas?