Unable to setup Data gateway connection using Always ON secondary SQL data source

[Anonymous]

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: e3acb3f6-6cd4-ca6d-87ff-c6bdbb8c1c68 Request ID: 27ff2b59-d8dd-b8a3-db3f-e795421fe016 Cluster URI: https://wabi-us-north-central-redirect.analysis.windows.net Status code: 400 Error Code: DMTS_PublishDatasourceToClusterErrorCode Time: Thu Aug 16 2018 23:58:47 GMT+0530 (India Standard Time) Version: 13.0.6289.161 PowerBI Enterprise Gateway: Cannot connect to the mashup data source. See error details for more information. Underlying error code: -2147467259 Underlying error message: OLE DB: The target database ('EDW') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online. Invalid connection string attribute DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259 Microsoft.Data.Mashup.ValueError.DataSourceKind: OleDb Microsoft.Data.Mashup.ValueError.DataSourcePath: applicationintent=readonly;database=EDW;provider=SQLOLEDB;server=SINAZEDBS19;'trusted_connection=True Microsoft.Data.Mashup.ValueError.ErrorCode: -2147467259 Microsoft.Data.Mashup.ValueError.Message: The target database ('EDW') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online. Invalid connection string attribute Microsoft.Data.Mashup.ValueError.Reason: DataSource.Error

[TomMartens]

Hey,

wondering if you are aware of this video, it shows how an AG listener can be used to accomplish what you are using:

https://www.youtube.com/watch?reload=9&v=Q8Fimh4lcUg

and this document:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only...

Regards and have a great weekend,

Tom

[GilbertQ]

Can you please provide more details in terms of the version of the Gateway you are using?

How do you connect to the data source normally?

Any other details that will help us assist you. When there is just an error message it does not give us a lot to go on.

[Anonymous]

Gateway version version : 14.16.6768.3 (July 2008)

MS SQL Data source - However, the data source is on Always ON - Secondary server

we are unable to configure the SQL data source in the Gateway as a normal dataset. Normally we would need to specify the Additional parameter : ApplicationIntent=ReadOnly to take care of the databases in the Availability Groups. However, we do not have the option to add additional parameters. Kindly suggest or provide the syntax to update the Gateway configuration

[v-piga-msft]

Hi @Anonymous,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[GilbertQ]

Hi there Thanks for the additional information. From what I know currently that isn't available. You could possibly configure something via a linked server to create the connection?