Azure Automation hybrid worker connectvity with Azure SQL db via private endpoint

Question

i have a powershell script to run on Azure automation hybrid worker (Azure VM ) that query SQL server which connectvity only via private endpoint and allows only AAD authentication . I'm using using service account for connection but ps script fails with error :Exception calling "Open" with "0" argument(s): "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: TCP Provider, error: 0 - No such host is known.)"

Note : TCP test connection port 1433 is successful and resolves private DNS as well.

Answer 1

@Krishna Yadav , thank you for the question. Did you test the TCP test connection from the runbook script to this sql server?

The error points to the fact that the runbook script execution host was not able to find the SQL Server, therefore performing the tcp test from the runbook script itself would be important.

Also, if you have multiple VMs added as Hybrid worker group, please ensure that all these VMs can access the SQL server remotely over the VNET. When runbook execution happens by the Automation Account on Hybrid worker, it could be scheduled on any of the worker in the worker group. For more details see Runbook execution environment.

Hope this helps.

If the answer did not help, please add more context/follow-up question for it, and we will help you out. Else, if the answer helped, please click Accept answer so that it can help others in the community looking for help on similar topics.