Add second data source to a gateway

[msr15105k]

I have a report running in the online service that initially had one connection to an SQL database, using a gateway to connect to it. I attempted to add a second database from the same SQL server to the gateway, using basic credentials (username and password for the database). However, when trying to add this connection to the gateway, the following error appears, and the new connection is not created. Note that the credentials work properly when logging into the database directly from SQL Server Management Studio, so the credentials appear to be correct.

My understanding is that a single gateway is allowed to have more than connection.

Any ideas or solutions would be greatly appreciated.

Thanks.

[v-tsaipranay]

Hi @msr15105k ,

Thank you for reaching out to the Microsoft Fabric community forum. Also thank you @GilbertQ  for your inputs

The error you're facing while adding a second SQL database connection to the On-premises Data Gateway is likely due to an authentication mismatch or gateway configuration. Please ensure that the authentication type (Basic or Windows Authentication) used in the gateway matches the one used in SSMS.

If your SQL Server uses Windows Authentication, the gateway service account must have the necessary access permissions. Also, verify that both data sources have the same privacy level under Manage Gateways settings in Power BI.

Additionally, check if there are network restrictions or firewall blocks on TCP port 1433 between the gateway machine and SQL Server. Restarting the gateway service and enabling diagnostic logs can also help identify the issue.

For more information, please refer to Microsoft's official documentations:

Manage an on-premises data gateway | Microsoft Learn

Add or remove a gateway data source - Power BI | Microsoft Learn

I hope my suggestions give you good ideas, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thank you. 

[GilbertQ]

Hi @msr15105k 

I have seen this happen in the past and what it could be is that there are logon triggers that get executed when you log on to the SQL Server database. This is not elegantly handled in the power BI gateway. Your only option to get around this is to connect using an ODBC connection to SQL Server, which will work just as well. It's just it doesn't introduce issues with the logon triggers you. can you also contact your database administrator to confirm if there are log on triggers?

[msr15105k]

The first database connects fine using the gateway, only the second one produces the error. Would you recommend ODBC connections for both databases/data sources?