Redshift through on premise gateway fails due to ODBC driver

[Anonymous]

We get the following error when a dataset contains both Redshift data as well as on premise SQL Server data. 

GatewayPipelineErrorCode=DM_GWPipeline_Gateway_MashupDataAccessError
GatewayVersion=
Reason= --->
[1]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException: Substituted: MashupValueException:<pi>Microsoft.Data.Mashup.MashupValueException (0x80004005): The 'Driver' property with value '{Simba Amazon Redshift ODBC Driver}' doesn't correspond to an installed ODBC driver.
at Microsoft.Data.Mashup.ProviderCommon.MashupResource.<>c__DisplayClass44_2`1.<StartEvaluationAndGetResultSource>b__0()
at Microsoft.Mashup.Security.Impersonation.RunAsProcessUser[T](Func`1 func)
at Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction)
at Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior)
at Microsoft.Data.Mashup.MashupCommand.ExecuteReader()
at Microsoft.Data.Mashup.DataSourceReference.TestConnection(String connectionString)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.GatewayDataAccessMashupUtils.DSRTestConnectionAsync(String dsrJson, MashupCredential credential, Nullable`1 activityId, Int32 testConnectionTimeout, String[] exemptions, String containerPool)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.MashupOleDbConnectionProvider.<PrepareMashupCredentialEncryptedConnectionState>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.MashupOleDbConnectionProvider.<OpenConnectionAsync>d__2.MoveNext()</pi>

We have the Amazon provided Redshift ODBC driver installed on the gateway server, but based on this error message it is looking for another driver that we do not have and are not planning on purchasing. 

[v-qiuyu-msft]

Hi @Anonymous ,

Which action do you perform then the error occurs? 

There should be RequestID and ClusterURI information displays with the error. Please share these information with us. 

Best Regards,
Qiuyun Yu 

[Anonymous]

@v-qiuyu-msft 

This occurs after publishing the report and trying to refresh it. We show the gateway is setup correctly and the accouts are authenticated correctly. 

Cluster URIWABI-WEST-US-redirect.analysis.windows.net

Activity ID132d9dc0-f6c1-4f15-9da6-83b1a85858e0

Request ID3b09f166-5b8d-43ce-bae2-084be11dfc29

Time2019-03-06 18:11:24Z

I've noticed that the "Simba Amazon Redshift ODBC Driver" is included in the ODBC folder for Power BI Desktop but not for the on premise gateway.

[Anonymous]

@v-qiuyu-msft 

I was able to replicate this issue on my personal machine using the on premise gateway with the same dataset. I got the same error when trying to refresh but when I copied the Simba Amazon Redshift ODBC Driver folder and ini file to the on premise ODBC folder and restared the gateway, it now refreshes. I believe this is the root cause and I will check with our DBAs to see if they are able to correct the issue on our gateway server. 

[Anonymous]

I verified this corrects the issue

[kevhav]

Incredible. I had this exact same issue, and this solved it. Thanks @Anonymous !

Another note for others who might read this:

• With my installation of Power BI Desktop, at first I couldn't find the "Simba Amazon Redshift ODBC Driver" folder and .ini file (nor even the "ODBC Drivers" folder that contains it). I think it was because I had installed Power BI Desktop via the Microsoft Store.
• When I looked at an installation of Power BI Desktop that had been installed via download from the Power BI website...then I could find the "ODBC Drivers" folder, containing the "Simba Amazon Redshift ODBC Driver" folder and .ini file.

[Anonymous]

+1 the above suggestion by @Anonymous 

Hopefully I'm not waking up a dead thread but... I ran into the exact same issue. Refreshing the dataflow via the on-prem data gateway worked fine on my laptop but not on the AWS RDP server needed to access the Redshift DB. I was getting the "Simba Amazon Redshift ODBC Driver" cannot be found on the local machine error referenced above. 

I tried everything!

Finally copy/pasted the drivers from my local laptop up to the server and it's working great.