Issue with Scheduled Refresh of SQL Server Connection-gatway

[Giu]

Hi All,

I have a gateway installed on an AWS server.

I have multiple connections to MySQL databases, and they work correctly.

I recently created a new connection to a SQL Server database using the following address:
xx000.webserver.xx\MSSQLSERVER2019

When I manually refresh the semantic model on demand, everything works fine.

However, when I schedule the refresh using the semantic model settings, generally the refresh fails.

Sometimes, the scheduled refresh does go through, but it's rare.

The error message is:

Data source error: {"error":{"code":"DM_GWPipeline_Gateway_DataSourceAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_DataSourceAccessError","parameters":{},"details":[],"exceptionCulprit":1}}} Table: Table_aaa

Any help or suggestions would be appreciated.

Best regards,
Giuseppe

[v-nmadadi-msft]

Hi @Giu 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[Kedar_Pande · Accepted solution]

@Giu 

The issue is likely with your SQL Server connection string. When using named instances (\MSSQLSERVER2019), scheduled refreshes can fail even though manual refresh works.

Try using the full TCP connection details instead:

Server: xx000.webserver.xx,1433 (replace with actual port)

Or use the server's IP address with port

Update the data source in your gateway configuration with these TCP details.

[tayloramy]

Hi @Giu, 

This error usually means the gateway could not reliably reach your SQL Server data source at refresh time. Because you are using a named instance (xx000.webserver.xx\MSSQLSERVER2019) on a gateway host in AWS, the most common root causes are:

• SQL Browser (UDP 1434) or the instance TCP port is not consistently reachable from the gateway
• The named instance is on a dynamic port that changes after restarts
• Gateway egress (to Azure Service Bus) or DNS intermittently fails
• TLS/cert or connector setting differences between Desktop/on-demand vs scheduled runs
• The gateway cannot access the SQL Server

Some things to try:

1. Assign a fixed TCP port to the SQL instance and connect with server,port instead of server\instance.
   How: set a static port in SQL Server Configuration Manager, restart the SQL service, then update the data source to xx000.webserver.xx,PORT (no instance name).
   Docs: Configure a server to listen on a specific TCP port and Static vs dynamic ports.
2. If you must keep a named instance, ensure both UDP 1434 (SQL Browser) and the instance TCP port are open from the gateway server to SQL.
   Docs: Firewall rules for SQL Server and SQL Browser. Community example: named instance connectivity.
3. Keep the gateway open to Azure Service Bus (outbound 443 at minimum; some networks also need 5671/5672 and 9350–9354).
   Docs: Gateway communication requirements.
4. Update to the latest gateway build and re-enter credentials on the mapped data source.
   Docs: Gateway troubleshooting.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[rohit1991]

Hi @Giu 

This issue happens because the gateway can’t consistently access your SQL Server during scheduled refresh even though manual refresh works. Here are the key checks/fixes that usually solve it:

1. Gateway mapping: In Manage Gateways, make sure the SQL Server name and database match exactly, and your dataset is linked to this gateway.

2. Credentials: Re-enter SQL Server login under Dataset >> Data Source Credentials using the correct authentication method (Windows or SQL).

3. Port / Instance: If using a named instance, set a static port 1433 and connect as xx000.webserver.xx,1433; ensure the SQL Browser service is running.

4. Network: Verify that port 1433 is open and the gateway service account has both network and database access.

5. Logs: Check C:\Program Files\On-premises data gateway\ for DM_GWPipeline_Gateway_DataSourceAccessError to identify connection or login issues.