Connect to 'unconfigured' ODBC source by DSN name and parameters

[Anonymous]

I have an ODBC Datasource for MySQL on my machine. I have left all of the connection properties such as Server, Database, Username, and Password blank.

I am able to successfully connect to this unconfigured DSN and specify properties through PowerBI Desktop, as in the screenshot below. Data is successfully pulled and refreshed as expected.

(disregard the DSN name difference in screenshots. They are example names and not the source of the issue)

However, I have not found a way to successfully expose this through the PowerBI Gateway. The publish feature does not automatically configure the datasource (at least not successfully), and when manually setting up the datasource like in the screenshot below, I can see the request to MySQL being made on my machine is only using User/Password and the DSN. The additional properties (port, server, datasource) are defaulting to what is set in the DSN.

 

On the contrary, I can configure the exact same setup to successfully use a connection string with 'Driver' instead of 'DSN'. The additional connection settings (such as port) are honored as expected. For example:
Driver={My ODBC Driver for MySQL};server=myserver;port=3307;datasource=mydatasource;

Does anyone have more information about this?

[v-juanli-msft]

Hi @Anonymous

It seems you could successfully connect to Mysql from ODBC in Power BI Desktop, however, when you configure connection in Gateway, it doesn't work.

Please check the following to let me know more details:

Do you use On-premises gateway or personal gateway?

which error does it show when you configure in Gateway, could you show an screenshot?

please read through these similar threads to get some ideas:

add odbc data source under gateway

  > Check your dsn name(testodbc1 in my scenario) in ODBC administrator or in Power BI Desktop

  >Add data source under gateway by entering connection string , account and password,

     make sure you enter the same connection string as that you get in Power BI Desktop. 

connect an ODBC database to the gateway in Power BI service

  >Please create a System DSN to points to your data source in in ODBC administrator, then add the ODBC data source under gateway. 

  >Power BI Gateway only accepts 64-bit drivers for data sources,

    Please create a 64-bit DSN and use the 64-bit driver

Best Regards

Maggie

[Anonymous]

Hi @v-juanli-msft,

I appreciate your reply, but ultimately I believe you may have misunderstood my issue here. I'm able to successfully configure a connection through the gateway by using the "Driver name" method, or by fully configuring the DSN itself and not relying on the connection string.

To rephrase my issue: I noticed behavior where an "unconfigured" ODBC dsn is unable to be "dynamically" configured via Connection String Options in the PBI Gateway when DSN name is specified. By unconfigured, I mean that the DSN exists on the machine, but has been configured with the default (often blank) values. However, "dynamic" configuration works with both the DSN name method in PBI Desktop, and also with the Driver name method through PBI Gateway

More simply phrased: Connection String Properties are not honored through PBI Gateway when DSN is specified.

Here's the 2nd screenshot that looks like it is now missing from the original post.  In this screenshot, the error occurs because the gateway is requesting data from the default port 3306, rather than the specified port 3307. 

 

 Can anyone confirm that DSN can be used with additional connection properties like this?

[Anonymous]

Bump