Error when trying to connect to an Athena Data source via on-premises data gateway

[Anonymous]

Hi,

I am trying to configure a connection through to AWS Athena via an On-Premises Data Gateway.

When the connection tries to do its test, I get the following error message:

We cannot convert the value null to type Record

I am using OAuth2 as the Gateway Authentication.

Within the ODBC Connection, I am using IAM Credentials.

The ODBC connection that I setup works fine from the device through to AWS Athena.  I can also connect via Power BI Desktop and return data.

Does anyone have any clue?

Thanks

[Anonymous · Accepted solution]

I have resolved the issue.

For reference, here is what I needed to do to make it work:

1. Make sure that on the Gateway server where you setup the DSN, ensure that it is setup as a "System DSN". 
   I believe that this was the main issue that was causing the error.
   
2. Depending on how you have setup the Gateway, you may need to change which users you allow to authenticate in the ODBC Settings
   1. Click on Authentication Options => Password Options
      1. If the gateway service account is the same account that is currently configuring the ODBC settings, leave "Encrypt Password For: Current User Only":
         
         For this to work, though, the account that you are using to configure the ODBC connection must be the same account that is the service account within the On-Premises Data Gateway.
      2. If you want the ODBC Authentication available for a different account, you will need to select 'All Users of this machine'
      3. Alternatively, you could use the .aws configuration file and select the "default" authentication option, but I didn't test this.

Once I had these settings sorted, the connection worked.

[v-yetao1-msft]

Hi @Anonymous

Maybe you can try the method provided by sgunnam01

1. Install the gateway and Athena ODBC connector on a ODBC machine. We DO NOT need to put this in Customer connectors. 

2. Give the role based permissions (based on how you want your authentication) to the machine where above two components are installed. Make sure you are able to connect from ODBC to datasource by doing "Test Connection" in ODBC configuration dialog. There we used Authentication mechanism as "Default". 

3. When creating "Datasource" in Power BI service using the "gateway" name that was setup in step1, selection the authentication as anonymous.

Solved: Error while connecting to AmazonAthena gateway. We... - Microsoft Power BI Community

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

Thanks for the tip.  While it hasn't 100% solved the issue, it has made me look at how it was setup again and I have now been able to figure it out.

I will post my fix, for reference in another reply