ODBC Gateway Setup fail!

[Anonymous]

Hi, 

1) the base info : PBI - Pro, Gateway - On-premises

2) I learned about the community's thread on this issue https://community.powerbi.com/t5/Service/Can-t-connect-ODBC-datasource-to-gateway/m-p/797173

3) and do the everything follow up its instructions

4) The PBI Desktop connect the ODBC datasoure successfully!!

5) connection string I setup as "dsn=pbi_bpcsf" since the powerquery shows as the following,:

let
source = Odbc.DataSource("dsn=pbi_bpcsf", [HierarchicalNavigation=true]),
US400CN_Database = sourc...

... ...

and gateway schedule show is :

"ODBC('connectionstring:dsn=pbi_bpcsf')"

6) ODBC data source administrator - both(32-bit) and (64-bit) all have setup.

7) But it still did not work after I add "Add" button in Gateway setup main window,  the main window...

HOW CAN I DO?  NEED HELP!

Unable to connect: We encountered an error while trying to connect to . Details: "We could not regis...Hide details

Activity ID:	a398f15a-15c3-4bd8-b3b1-44cc9ee65a48
Request ID:	dda9fab1-2b91-b072-1253-88043abe5f5a
Cluster URI:	https://wabi-west-us-redirect.analysis.windows.net/
Status code:	400
Error Code:	DMTS_PublishDatasourceToClusterErrorCode
Time:	Sat Jul 18 2020 21:52:21 GMT+0800
Service version:	13.0.13860.51
Client version:	2007.1.01830-train SuZ-Gateway: Unable to connect to the data source undefined. Underlying error code: -2147467259 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=pbi_bpcsf Microsoft.Data.Mashup.ValueError.OdbcErrors: #table({"SQLState", "NativeError", "Message"}, {}) Microsoft.Data.Mashup.ValueError.Reason: DataSource.Error

[EV]

Hi is there solution to this problem?

[lbendlin]

Can you connect via ODBC on the gateway?

[Anonymous]

No , cannot , that's why I post this issue for help....!

And.. I can connect data /and SQL data through ODBC to PBI desktop , it's successfully...

JUST Gateway setup datasource for refresh fail...

[lbendlin · Accepted solution]

You need to successfully connect to your data source via ODBC on the gateway VM before you can proceed to create the connection on the gateway. The ODBC system dialog has a "Test" option. You can use that, or install a database client on the gateway VM for the test.

[rbeekman]

If it were only that easy. I added a MySQL ODBC dsn to my gateway and an MSSQL one. Both dsn's work fine from my laptop PBI desktop works fine with them. But in the cloud PBI keeps saying nog connection can be made tot the gateway. Being untrue, because teh MySQL works fine.
Also tried to setup the connection directly tot SQL server from PBI desktop... no success there either.

[Anonymous]

Hi, got it and tks!

And could you recommend a VM link or VM software name to me? which fit for WIN10 OS and Power BI Gateway...

tks!

[lbendlin]

Here are the recommendations

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-install

Hint: these are on the very low end of the scale.  Generally you want as much power and memory and fast disks as you can get.

[Anonymous]

I have that gateway...

And successfully used on connecting public folder, server database(sql server)...

My PBI have a lot of reports and daily scheduled refresh them by the gateway in a year...

Just failed creation ODBC gateway... BUT, the ODBC successful connection data to PBI desktop...

BTW, my source database is iBM AS400, fyi..

tks..

[lbendlin]

use the ODBC control panel on the gateway (not on your PC)  to validate that you can reach the data source from the gateway through ODBC.

Doesn't matter if it works on the PC. It needs to work on the gateway.

[Anonymous]

Dear,Fail1Fail2Fail detailODBC setup has follow System DSN

pls see the pics...

tks!

[Anonymous]

may be you mean setup on the gateway,,, pls see the pics,,, since my gateway installed in a server and ODBC not in it...

so , it looks i have to use personal gateway or install ODBC on it...

tks.gateway status okODBC not install in that server

[v-easonf-msft]

Hi , @Anonymous 

Do you have resolved it? If yes, you could accept the helpful answer as solution. You also could share your own solution here. For now, there is no content of description in the thread. If you still need help, please share more details to us.

BTW, it seemed that you didn't  sign in your account on your gateway.

Best Regards,
Community Support Team _ Eason

[Anonymous]

Dear, You can see the history , and the info was detail,  and all the advise you mentioned to me, I followed up but it not work.