Forum Discussion
Add second data source to a gateway
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.
We finally solved it. See details below, but in summary, we needed to add our SQL server to the gateway configuration file and then restarted the gateway. Marking your post above as a solution since it mentions re-starting the gateway.
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?
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?
Thanks. We tried the ODBC method, worked fine in the Desktop Power BI, but then when published, it appeared to look for an SQL connection and would not work. I can try to find more detailed error messages on that (my admin person did that test).