Is it possible to allow Azure elastic jobs access to Azure SQL Databases without the need to allow all Azure resources to the server?

1
votes

We are trying to use Azure elastic jobs to run database jobs on a set of Azure SQL databases. The access to these Azure SQL databases is restricted to only certain ip-addresses which we have configured in the firewall rules. When testing the execution of the elastic jobs the elastic job agent cannot access our databases. We do not want to enable access for all Azure Services. I found a post that it is possible to limit the access by manually adding the ip addresses of the Azure resources of the location we use. (https://github.com/MicrosoftDocs/azure-docs/issues/35138) But this means it is still possible for all Azure resources in that location to try and access our servers.

Is there a way to only provide access to our SQL databases for our Elastic Job SQL Agent resource?

1
azureazure-sql-database
I agree that having to allow ALL azure resources to access the database is a bit much to be able to just allow elastic jobs to access the database – PaulVrugt
Hi @Robert Wielink. If my answer is helpful for you, you can accept it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.). This can be beneficial to other community members. Very thanks! – Joseph Xu

1 Answers

0
votes

Yes, it is no need to allow all Azure resources to the server, but after that we need to list ip whitelist. (https://github.com/MicrosoftDocs/azure-docs/issues/35138) as the post also said: Please go ahead to white list the IP Ranges for the specific data center you are deployed to instead of allowing all IPs from all Azure services.

I also created a test to prove it.
I turned off this setting of my Azure SQL database.
enter image description here

Then I executed EXEC jobs.sp_start_job 'CreateTableTest' to start a job, I only got an error as follows: enter image description here

So I need to add the client ip to the firewall rules of my Azure SQL database. After that, it works well. enter image description here

When we allow Azure services and resources to access this server, it will automatically and implicitly add all the ip range of azure services and resources.

Update:
I've removed other IP list and the elastic jobs can still run successfully. enter image description here That's amazing! enter image description here