Enterprise gateway on virtual machine trying to connect to ODBC

[mo789]

We have an enterprise gateway setup and it displays a setup of online and ready to be used. 

We have also setup an ODBC connection and through the windows ODBC client, the connection is succesful when I try to authenticate with my username and password. I can connect to the database (Oracle) through the virtual machine.

Moving to my local desktop, every time I try to add the same datasource, checked spelling of the connection string (dsn=XXXX) where XXXX is the name in ODBC connection, I get hit with the error below. I have tried so many different things but nothing seems to be helping getting this resolved.

Btw, I have tried to connect and pull a file on the virtual machine, a .CSV and it comes through. So I am able to connect to the virtual machine and pull data, on the virtual machine I can connect to ODBC, I can just not seem to connect to ODBC through the gateway on my Power BI Service. 

*****************************************

Error below

*****************************************

Unable to connect: We encountered an error while trying to connect to . Details: "We could not register this data source for any gateway instances within this cluster. Please find more details below about specific errors for each gateway instance."Hide details

Activity ID:	4a17e115-709c-4124-855a-19f5665ad74c
Request ID:	bb168566-293f-b5cd-33f8-06754a3a9a6b
Cluster URI:	https://wabi-canada-central-redirect.analysis.windows.net/
Status code:	400
Error Code:	DMTS_PublishDatasourceToClusterErrorCode
Time:	Fri Jun 12 2020 10:13:48 GMT-0400 (Eastern Daylight Time)
Service version:	13.0.13524.230
Client version:	2006.1.01422-train Virtual Machine Gateway: Unable to connect to the data source undefined. Underlying error code: -2147467259 Underlying error message: ODBC: ERROR [08004] [Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve the connect identifier specified ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259 Microsoft.Data.Mashup.ValueError.DataSourceKind: Odbc Microsoft.Data.Mashup.ValueError.DataSourcePath: dsn=N1V8 Microsoft.Data.Mashup.ValueError.OdbcErrors: #table({"SQLState", "NativeError", "Message"}, {}) Microsoft.Data.Mashup.ValueError.Reason: DataSource.Error Troubleshoot connection problems

[Anonymous]

When changing SQL Server connectivity settings, Azure does not automatically enable the TCP/IP protocol for SQL Server Developer and Express editions. The steps below explain how to manually enable TCP/IP so that you can connect remotely by IP address.

First, connect to the SQL Server virtual machine with remote desktop.

1. After the Azure virtual machine is created and running, click the Virtual Machines icon in the Azure portal to view your VMs.

2. Click the ellipsis, ..., for your new VM.

3. Click Connect.

4. Open the RDP file that your browser downloads for the VM.

5. The Remote Desktop Connection notifies you that the publisher of this remote connection cannot be identified. Click Connect to continue.

6. In the Windows Security dialog, click Use a different account. You might have to click More choices to see this. Specify the user name and password that you configured when you created the VM. You must add a backslash before the user name.

7. Click OK to connect.

   Next, enable the TCP/IP protocol with SQL Server Configuration Manager.

   1. While connected to the virtual machine with remote desktop, search for Configuration Manager:

   2. In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration.

   3. In the console pane, click Protocols for MSSQLSERVER (the default instance name.) In the details pane, right-click TCP and click Enable if it is not already enabled.

   4. In the console pane, click SQL Server Services. In the details pane, right-click SQL Server (instance name) (the default instance is SQL Server (MSSQLSERVER)), and then click Restart, to stop and restart the instance of SQL Server.
   5. Close SQL Server Configuration Manager.

   I hope this will be helpful.

   Best Regards

[lbendlin]

Verify that your TNSNAMES.ORA entry is accurate, and that your ODBC connection on the gateway uses it.

[mo789]

So I have the tnsnames.ora file on the virtual machine. Not sure where to put it. If my ODBC application on the virtual machine is saying it is able to connect to the database, doesn't that mean the tnsnames.ora file is in the right place? 

[lbendlin]

Potentially.  

I also just realized that you use an ODBC driver.  What made you decide against using the native Oracle Net driver?

[mo789 · Accepted solution]

We currently have a schema within our DB that is mainly for all analytics use, ODBC. I would like everyone to know that when I tried to use native connection to Oracle, it worked. The ODBC connection I could still not figure out.