Hi,
How do I get data from on-premises SQL Server which connected to my Azure with VPN Site-To-Site? I have tried 'direct' (set the IP on-premises SQL Server) connection in linked service, but failed to connect. The error message: Cannot connect to SQL Database: x.x.x.x\instance_name, Database: db1, User: user01. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access. 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), SqlErrorNumber=-1,Class=20,State=0, Activity ID: 7c20c940-0415-4b19-9c19-d30692aeeb16.
I already tested the same configuration with DBeaver (SQL client tool) in one of my VM in my Azure, and successfully connected. I have checked, firewall on-premises do not blocking any IP from Azure.
So what is the best practice to get data from SQL Server on-premises? Is using Self Hosted Integration Runtime the only way to achieve this goal?
Thanks in advance.
