Error Connecting to SQL Server High Availability Cluster using Azure On-Premise Gateway

Question

We are attempting to configure an On-Premise Data Gateway for use by Azure by Power BI/Power Apps. We wish to connect to a SQL Server High Availability Cluster on a "ReadOnly" instance of a database for Live/On-Demand queries. Below would be an example of a connection string that would accomplish this:

Server=MyServer;Database=MyDb;Trusted_Connection=True;applicationintent=readonly

We downloaded the latest version of the gateway, installed it, but cannot get it to connect to a HA SQL database. I have searched the Internet and have yet to find an answer. We tried the "SQL Server" and "OleDb" data source in Azure Power BI without success. We can connect to a non HA instance of a SQL Server database with no issue. Can this be done, and if so, how?

https://powerbi.microsoft.com/en-us/gateway/

Below is the error I see in the app.powerbi.com web portal when attempting to add the SQL connection:

Unable to connect: We encountered an error while trying to connect to MyServer.

ID: 90c2a52c-ba1f-4506-84ed-d6da5a44e684 Request

ID: 75e6338b-6806-9d42-8af3-7f6896485df8 Cluster

URI: https://wabi-west-us-redirect.analysis.windows.net

Status code: 400 Error Code: DMTS_PublishDatasourceToClusterErrorCode

Time: Fri Dec 01 2017 07:53:37 GMT-0700 (Mountain Standard Time)

Version: 13.0.3154.215

BI Gateway: Received error payload from service with ID 413243: SqlException encountered while accessing the target data source.

Below is an exception logged in the client running the gateway service:

GatewayPipelineErrorCode=DM_GWPipeline_UnknownError

InnerType=SqlException

InnerMessage=The target database ('MyDatabase') 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.

InnerToString=System.Data.SqlClient.SqlException (0x80131904): The target database ('MyDatabase') 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.

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.<>c__DisplayClass31_0.b__0(Task`1 _)

at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke() at System.Threading.Tasks.Task.Execute()

I'm doing it, so it can be done. Do you have an error message? docs.microsoft.com/en-us/power-bi/service-gateway-onprem-tshoot — influent
@influent I went ahead and added error messages seen on the web portal and on the client where the gateway service is running. — Shayne Ephraim
I've never actually used the Gateway with Availability Groups, sorry. It looks like the problem is related to that but I'm not sure what the fix is. — influent

Shayne Ephraim Shayne Ephraim · Accepted Answer · 2018-08-27T19:34:37

I have since learned more about the Azure On-Premise Gateway and this is still an issue. A possible work around is to create a linked server on the HA read-only secondary SQL server using "@provstr=N'ApplicationIntent=ReadOnly'".

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'HaReadOnlyLink', @srvproduct=N'SQL', @provider=N'SQLNCLI11', @datasrc=N'SqlHaListnerHost', @provstr=N'ApplicationIntent=ReadOnly', @catalog=N'MyHaDb'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HaReadOnlyLink',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

You can then use this linked server from another database on the HA secondary database to connect the the read-only HA database like so:

SELECT * FROM HaReadOnlyLink.MyHaDb.dbo.MyTable

Microsoft SQL Linked Server Documenation

Error Connecting to SQL Server High Availability Cluster using Azure On-Premise Gateway

1 Answers