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 · Accepted solution]

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. 

[v-tsaipranay · Accepted solution]

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. 

[msr15105k · Accepted solution]

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.

[msr15105k]

Thanks so much for your help so far. Our IT person will check through your message and troubleshoot.  Meanwhile, here is a message from the error logs:

From the On-Premise Data Gateway error logs on 1510-

GatewayPipelineErrorCode=DM_GWPipeline_UnknownError
GatewayVersion=
InnerType=SqlException
InnerMessage=<ccon>A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)</ccon>
InnerToString=<ccon>Microsoft.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException: Substituted: Win32Exception:<ccon>The certificate chain was issued by an authority that is not trusted</ccon>

[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]

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).

[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?