On Premise Data Gateway - Single database, different username

[vranjan22]

Hi,

I have a on premise data gateway installed on a server which is being used for PowerApps application. I am now creating a PowerBI report from that database(SQL Server Express Edition), however the user account is different from what is being registerd for On Premise data gateway.

How can I use it to connect to Power BI reports?

Please advise.

Thanks,

Vivek

[Seth_C_Bauer]

@vranjan22 In Power BI the user account that created the gateway will have access to the gateway there. You can add other admins if necessary, but you will create a datasource on the gateway using the credentials specific to the SQL DB. Then you can apply users to the datasource who have access to use it when publishing reports. 

[vranjan22]

Thank you @Seth_C_Bauer for your response.

I am quite new to Power BI & Power Apps, hence I may not understand all the suggestions shared by you.

The datatable and the on-premise gateway are configured on a server. The datatable is getting updated using PowerApps which is connected through the on-premise gateway. The ID used for PowerApps & gateway is same.

Now, I am creating a PowerBI report for the datatable. I have used PowerBI desktop for connecting to SQL datatable & designing the report. While publishing, I am using a different account. At present, in order to refresh the PowerBI report, I doing following steps:

Open report in PBI desktop > Refresh data table > Save & Publish the report

I am looking for skipping the steps above. The PowerBI reports should automatically get refresh at regular intervals.

Please advise.

Thanks,

Vivek

[Seth_C_Bauer]

@vranjan22 The gateway is the same for Apps and Power BI. An admin of the gateway can see the gateway you stood up in both applications. In Apps, you created a connection, in Power BI you will most likely need to create a datasource for the same SQL DB. This can be done in Power BI. The admin, would then assign you as a user of the data source so that when you publish the report, you would see that a gateway is available on the dataset for you to schedule a refresh of the report.

This article walks through the data source creation, just ignore the download of the gateway portion because you already have one.

https://docs.microsoft.com/en-us/power-bi/service-gateway-enterprise-manage-sql

[vranjan22]

Hi @Seth_C_Bauer

I followed the steps mentioned in the link shared by you along with I assigned the Admin access to the id using which I am creating PowerBI report:

While adding the data source to the gateway, I am getting the error:

The user I have created on SQL server and I am using that credential to connect, but unable to do so.

Please advise.

Thanks,

Vivek

[Anonymous]

@vranjan22,

Do you use the same user account and credential to connect to SQL in Power BI Desktop and gateway? If so, are you able to connect to SQL Server from SQL Server Management Studio(SSMS) with this user? Please makke sure SSMS is installed in the same machine with gateway.

Regards,
Lydia