Error when connecting to report that uses DirectQuery to SQL Server

[HannahC]

Hi,

Newbie here 🙂

I published a report that uses DirectQuery to SQL Server. I have the personal mode gateway installed. The report looks fine on my desktop, but online I'm getting the error 'This data source can't be accessed by a data gateway' and then in the report holder I get 'Can't display the visual' with the below details

'Couldn't load the data for this visual

Your data gateway (Power BI – personal) is offline, but a data gateway is available.
Please try again later or contact support. If you contact support, please provide these details.

Error CodeDMTS_NoGatewayWithAllDatasourcesToBindError

Server'MyServer'

Correlation IDbdb5b245-dcf2-1936-d3a1-12e0b08a095a

Database 'MyDatabase'

ConnectionType{FieldValue}

Activity IDa61ee590-4f7c-446d-ba9c-6eacdbdc6037

Request ID5fddce3e-5c1e-f6b4-6d1d-4ee335f060e7

TimeWed Jun 14 2017 09:45:07 GMT+0100 (GMT Daylight Time)

Version13.0.1700.2465

Cluster URIhttps://wabi-north-europe-redirect.analysis.windows.net'

Please can you help? Is it possible this is a permissions issue?

Thanks in advance!

Hannah

[pbiuser67]

Underlying Error: PowerBI service client received error HTTP response. HttpStatus: 400. PowerBIErrorCode: DM_GWPipeline_Gateway_BadUsernameFormat
Activity ID: e8bbd214-627a-40fe-a38a-f840409f349f
Correlation ID: f512dd3d-5b2c-1ae5-461d-b633a824505b
Request ID: 930fcca8-6a6e-eb1a-2dfd-65fb97a894b9
Time: Tue May 09 2023 21:45:24 GMT+0530 (India Standard Time)
Service version: 13.0.20626.62
Client version: 2305.1.13619-train
Cluster URI: https://wabi-uk-south-b-primary-redirect.analysis.windows.net/

[HannahC]

Hi!

Newbie here so let me know if I'm posting in the wrong place.

I published a DirectQuery to SQL Server report and when I try to look at the report online (in my workspace) it throws an error at the top saying 'This data source can't be access by a data gateway' and within the report holder it says 'Can't display the visual', details given are below:

'Couldn't load the data for this visual

Your data gateway (Power BI – personal) is offline, but a data gateway is available.
Please try again later or contact support. If you contact support, please provide these details.

Error CodeDMTS_NoGatewayWithAllDatasourcesToBindError

Server 'MyServer'

Correlation IDbee088a6-a2b4-a5a4-e5c1-0c16c0f759d5

Database 'MyDatabase'

ConnectionType{FieldValue}

Activity ID19abd514-afe3-42cb-a1eb-dc9ada011bec

Request IDf283fd75-75cb-1c4d-3602-79621201617f

TimeTue Jun 13 2017 17:08:46 GMT+0100 (GMT Daylight Time)

Version13.0.1700.2465

Cluster URIhttps://wabi-north-europe-redirect.analysis.windows.net'

Please help as I would have assumed SQL Server could connect via the gateway? Is this a permissions issue?

Thanks,

Hannah

[v-haibl-msft · Accepted solution]

@HannahC

Please use on-premises data gateway instead of personal mode of on-premises data gateway. For data sources other than Azure SQL Database or Azure SQL Data Warehouse that use DirectQuery, and Enterprise Gateway must be installed and the data source must be registered to establish a data connection.

If you are using import mode to SQL Server, you can use personal mode of on-premises data gateway.

Please refer to following Matrix. Reference: https://powerbi.microsoft.com/en-us/gateway/.

For how to use on-premises gateway, you can refer to https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise-manage-sql/.

Best Regards,
Herbert

[HannahC]

Hi Herbert,

Thanks very much for this.

I was getting errors trying to download the non-personal mode, but I can ask IT to try again.

Thanks,

Hannah