As I wrote back in 2020, Microsoft provide a really easy way to allow you to connect Azure Services to Azure SQL Database that, in my opinion, is not sufficiently secure because it doesn’t allow you to lock out access from other subscriptions.
The below is a workaround for exactly the same issue when using a Linked Service for Azure SQL Database in Azure Data Factory.
The expected approach when creating a Linked Service for an Azure SQL Database is to use an Azure Integration Runtime and this is what is chosen if you allow the Data Factory to auto-resolve the Integration Runtime.
However when I test the connection in this case, I find that it fails with an error even when the Data Factory’s managed identity has been created in the Azure SQL Database.
To resolve this error, the suggestion from Microsoft is to tick “Allow Azure services and resources to access this server” in the Azure SQL Server’s Networking pane.
And if I do that, my test of the Linked Service will succeed.
But quite frankly, I’m not happy with that level of security. I can’t lock access down to a single IP address or range that I control and while I can restrict it to a region (e.g. Europe), that still includes everyone else’s Azure subscriptions in that region. So, in order to resolve this more securely, I’ve installed a Self Hosted Integration Runtime (SHIR) on an Azure VM and connected it to my Data Factory. This is exactly the same type of Integration Runtime I’d use to connect to an on-premises instance of SQL Server from Azure Data Factory.
And guess what? Even though I’m connecting it to an Azure SQL Database, rather than an on-premises SQL Server instance, it still works! When I choose the SHIR as the Integration Runtime for the Linked Service, it works just fine so long as the Azure SQL Server’s firewall allows access from the Azure VM hosting the SHIR.
And that’s it – a more secure way to connect Azure Data Factory to Azure SQL Database! However, it’s worth paying attention to the limitations of SHIRs because some activities (like Dataflow) don’t work with them at this time.
Be First to Comment