locked
Connection error between Data Sync agent and metadata database through VPN Gateway and a private endpoint RRS feed

  • Question

  • I have a hybrid architecture between Azure and on-premises servers that are connected to each other through a VPN Gateway.

    I want to deploy the SQL Data Sync service, for this I have the following architecture: a Single Azure SQL Database that works as a metadata database, a Single Azure SQL Database that works as a Hub, and an on-premises SQL Server that it works as a member and another on-premises machine where the sync agent client is installed.

    Each Azure SQL Database has a private endpoint on a subnet within the vpn in Azure that connects to the on-premises VPN.

    The problem occurs when registering the sync agent key found to the SQL Azure Data Sync Agent. A connection error is obtained when entering the agent key and the configuration of this metadata database.

    This error is obtained despite being able to successfully telnet against the private ip of the metadata database endpoint to port 1433 from the machine where the sync agent client is installed.


    Wednesday, May 13, 2020 8:42 AM

All replies

  • Hi Maria,

    If I refer to the documentation, I believe you are at this point in the configuration process: To add an on-premises SQL Server database

    The instructions detail that the SQL Data Sync Agent should be installed on the SQL Server host. There are also two firewall requirements:

    Important

    You have to open outbound TCP port 1433 in the firewall to let the client agent communicate with the server.

    This means that in the VPN connection to Azure, your firewall must allow outbound traffic over port 1433. 

    The second firewall requirement is that the hub database (Azure) must have an allow rule in the firewall for the on-premise SQL Database to connect.

    If you get a firewall error, create a firewall rule on Azure to allow incoming traffic from the SQL Server computer. You can create the rule manually in the portal or in SQL Server Management Studio (SSMS). In SSMS, connect to the hub database on Azure by entering its name as <hub_database_name>.database.windows.net.

    Because you have the sync agent running on a separate VM, please make sure this host IP Address has been added to the hub database firewall allow list. This is to cover the nuance of running the agent on a separate host than the SQL Server.

    I think that between these two firewall requirements, you should be able to move forward. If you are still stuck, then install the sync agent on the SQL Server host and see if you ae able to complete the set-up. If this is the case, you will need to unregister the first sync agent you have installed before removing it. It doesn't appear to be registered at this point (this is the issue attempting to be resolved).  

    Please let me know if you are able to move forward or not.

    Regards,

    Mike


    Friday, May 15, 2020 12:46 AM
  • Hi Mike,

    I have already solved the connectivity issue, I had to allow outbound traffic on my machine through the azure sql gateway (associated ips, to the west europe region).

    But I think all the traffic is going through Internet instead of the private endpoints. Do you know if it is possible that traffic in Azure SQL Data Sync goes through private endpoints instead of the internet?

    Regards,

    Tuesday, May 19, 2020 7:58 AM
  • Thank you for the additional information, Maria. From the host where the sync agent is installed, can you perform a traceroute to the hub database and see how the traffic is routed from your on-premise LAN to Azure. Authentication is likely going over the internet but after the authentication handshake, the connection policy is directing traffic over the VPN link.

    If you look at the connection policy modes, you will see redirect is the default and requires access to the gateway for authentication but the query activity is direct to the SQL node. Please let me know what you find.

    Regards,

    Mike

    Wednesday, May 20, 2020 2:41 AM
  • Hi Maria,

    Did you need additional assistance or wish to continue the discussion regarding this topic? Please let me know if you are still needing a resolution to this issue.

    Regards,

    Mike

    Friday, May 29, 2020 5:19 PM