Azure elastic jobs not working when using a private endpoint

Question

I'm trying to set up elastic jobs to run a procedure on another database.

I set up code to run as below: EXEC jobs.sp_add_target_group 'DatabaseGroup' GO

EXEC jobs.sp_add_target_group_member
'DatabaseGroup',
@target_type =  N'SqlDatabase',
@server_name='test-db01.database.windows.net',
@database_name =N'TEST'
GO

EXEC jobs.sp_add_job @job_name='Hourly Update', @description='Runs Hourly Update',
@schedule_interval_type='Hours',@schedule_interval_count=1

EXEC jobs.sp_add_jobstep @job_name='Hourly Update',
@command=N'exec [TT].[HOURLY_UPDATE]',
@credential_name='JobRun',
@target_group_name='DatabaseGroup'

EXEC jobs.sp_start_job 'Hourly Update'

So when i submit my job to run

I got the following error: Failed to connect to the target database: Reason: An instance-specific error occurred while establishing a connection to SQL Server. Connection was denied since Deny Public Network Access is set to Yes
To connect to this server, use the Private Endpoint from inside your virtual network
(Msg 47073, Level 14, State 1, Line 65536)

So my private end point name is called db01.test.****.com <- confidential name used

So I recreate the group_member & resubmit job again

EXEC jobs.sp_add_target_group_member
'DatabaseGroup',
@target_type =  N'SqlDatabase',
@server_name='db01.test.****.com',
@database_name =N'TEST'
GO

The new error below is: The server 'db01.test.****.com' cannot be used as a job target because it is not an Azure SQL Database server in the same Azure cloud as this job agent. The server's fully qualified host name must end with '.database.windows.net'.

So any ideas how i can get around this scenario & get my job to submit

Marc Slater Marc Slater · Accepted Answer · 2020-10-01T05:36:28

Your private endpoint should still be registered in Azure DNS using the 'database.windows.net' domain suffix. If you've created your own internal A records on your own managed DNS servers this would be the expected behavior.

A quick workaround on this is to put modify your HOSTS file on your source system using the Private Endpoint IP and the FQDN and it should work. Managing HOSTS files is not something you want to do for any period of time.

If you're running this all from within Azure, you should be able to leverage Azure DNS. - - Create a zone called 'privatelink.database.windows.net'

Create an A record with your Db01 & Private IP Address in this zone

On your VNET - check to see if your using Azure DNS or Custom DNS. If Custom DNS - you will need to setup your DNS servers to do DNS forwarding and send requests for 'privatelink.windows.net' to Azure DNS. Only IPs that are registered with Azure will be authorized to query Azure DNS. If you're using Azure DNS it will start working.

If it doesn't resolve for you right away then you will have to check to see if the Private DNS Zone has a virtual network link that connects your VNETs.

Azure Private DNS https://docs.microsoft.com/en-us/azure/private-link/private-endpoint-dns

Private DNS Documentation: https://docs.microsoft.com/en-us/azure/dns/private-dns-privatednszone

DNS Links Documentation: https://docs.microsoft.com/en-us/azure/dns/private-dns-virtual-network-links#resolution-virtual-network

Azure elastic jobs not working when using a private endpoint

1 Answers