Data Gateway Connection Issue

[Saugat_Mitra]

Hi,

I am trying to connect my SQL Server to Power BI using the on-premises data gateway. The gateway appears to be installed and running, as shown in the Manage Connections and Gateways section. The connection to the SQL Server is set up, and the data source is mapped to the correct SQL database.

However, I am encountering the following error:

Error:

"This report couldn’t access the data source because it doesn’t have permission."

The message suggests either choosing a different data gateway or contacting the gateway administrator.

The gateway shows as Running on FAPL, and I have confirmed that the user (Saugat) is set up with the correct permissions. Despite this, I continue to face the issue, and the "Test Connection" option is disabled because "Skip test connection" is enabled.

Error Details:

• The gateway is listed as Running on FAPL.

• The error message states permission issues while accessing the SQL Server through the data gateway.

What I've Tried:

• Ensured that the gateway is running.

• Configured the correct data source (SQL Server).

• Ensured the Global Administrator role is assigned to the user for access.

Can you please assist in resolving this issue?

 

 

[BhavinVyas3003 · Accepted solution]

Hi,

Have you tried this? Check if it works for you.

Go to Manage Gateways > SQL Server Data Source

Uncheck “Skip test connection” and Save

Re-enter valid SQL credentials and Test the connection

Thanks,

Bhavin

[v-dineshya · Accepted solution]

Hi @Saugat_Mitra ,

Thank you for reaching out to the Microsoft Community Forum.

Possible Root Causes:
1. Data Source Credentials Issue: Even though the connection is mapped to Saugat, the credentials stored in the gateway connection (Manage Connections and Gateways > Connection > Edit Credentials) might not have correct SQL Server permissions.
Power BI uses the credentials saved in the gateway connection, not your login session.

2. Mismatch in Connection Settings: If the connection string (e.g., server name fapl) used in Power BI Desktop when publishing the report doesn't exactly match the server name and database name configured in the Gateway, Power BI will see it as a mismatch.

3. SQL Server Authentication Type Issue: If your connection uses SQL Authentication but the gateway is set to use Windows Authentication (or vice versa), permissions will fail.

4. Firewall or Network Restrictions: If the machine where the gateway is installed doesn't have access to the SQL Server from a network/firewall perspective, it can throw a permission error too — although usually the error would be different.

5. Gateway User Access: In "Manage Gateways", the user (Saugat) must have been added under "Users" tab for the Lumax gateway.

Note: Not just that your email is set in the connection settings you also must be added explicitly under "Users" allowed to use the gateway.

please follow below steps:

1. Check and Re-enter Credentials: Go to Manage Connections and Gateways > Connections > Saugat > Edit. Edit the connection and re-enter the database credentials manually (don't rely on existing ones). Make sure they are SQL credentials or Windows credentials depending on what SQL Server expects.

2. Disable 'Skip Test Connection' Temporarily: Edit the connection again. Disable "Skip Test Connection". Then Test Connection to see if it succeeds or throws a more specific error.

3. Confirm Gateway User Access: Go to Manage Gateways > Lumax > Users tab. Make sure your account (saugat@futurealgos.com...) is listed there and allowed.

4. Confirm Exact Matching Server and Database Name: Open the Power BI Desktop file (.pbix) used to publish the report.
Check the data source settings: the server name must be exactly fapl and database lumax_yed_shifter_traceability matching how the gateway is configured.

5. Confirm Authentication Method: Double-check whether SQL Server expects Windows Auth or SQL Auth and ensure the Gateway Connection uses the same.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @Saugat_Mitra ,

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @Saugat_Mitra ,

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @Saugat_Mitra ,

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[BhavinVyas3003 · Accepted solution]

Hi,

Have you tried this? Check if it works for you.

Go to Manage Gateways > SQL Server Data Source

Uncheck “Skip test connection” and Save

Re-enter valid SQL credentials and Test the connection

Thanks,

Bhavin