With June fast approaching I’m in the middle of “Security Intelligence in Azure PaaS” tour with 4 Sql Saturdays and 2 user groups still to go. So far it’s been very positive experience and I was able to capture a fair bit of feedback. Along the way I got interesting questions from both attendees and fellow presenters. Today I’m going to answer one of the most common which is “How to securely access Sql Database/Sql Datawarehouse/Cosmos DB from Azure when ‘Allow access to Azure services’ option is disabled?”.

At first I strongly recommend disabling ‘Allow access to Azure services’ option. Enabled state opens resource to ANYONE inside Azure starting from VMs across all regions to Cloud Shell sessions running in any browser/VSCode instance. Configuring firewall rules and maintaining list of allowed IP addresses is almost pointless then - waste of time. Sql Database and Datawarehouse have ‘Allow access to Azure services’ enabled by default, where in Cosmos DB it’s disabled out of the box. Regarding Cosmos DB - after recent announcements at MS Build 2018 (general availability of VNET service endpoints) network controls to secure Cosmos DB are almost identical with Azure Sql PaaS. “Almost” because at the moment only Sql and Mongo APIs support service endpoints, firewall rules can’t be modified using direct connection to Cosmos DB (no equivalent to sp_set_database_firewall_rule/sp_set_firewall_rule) and they can only exist on the scope of Cosmos DB account. Depending on client’s type and location there are different ‘best practices’ to allow secure access. In order of applicability these are:

1. Configure VNET Service Endpoints
2. Programmatically add rule for IP (static or dynamic/ephemeral)
3. Just-in-time enable ‘Allow access to Azure services’

VNET Service Endpoints come with long list of limitations attached - they are region specific, logical server/Cosmos DB account scoped (subnet gets access to all databases), not transparent for traffic from peered networks and few more, but it’s the best option to provide secure access to PaaS resources that should be used in the first place if possible. Perfect for IaaS and resources that natively support VNET deployments (with exceptions like subnet that hosts Azrue Sql Managed Instance). Configuration is straight forward with lots of examples available online and I’m going to focus on other recommendations that are not that popular. What to do if it’s not possible to use VNET Service Endpoints? First we need trusted connection that can be used to apply required security configuration changes and optionally orchestrate operations and synchronise access. Common denominator is Azure ARM rest API call and examples below use PowerShell. Both Azure Sql PaaS and Cosmos DB support programmatic methods of setting firewall rules. For Cosmos DB:

The same operation for Sql Database and Sql Datawarehouse:

Interestingly there’s no New-AzureRmSql*Database*FirewallRule to configure firewall on database scope which is recommended for security, high availability and performance reasons (database scoped firewall rules are checked BEFORE logical server/master db rules). In addition to API calls it’s possible to configure rules directly in tSQL with trusted connection already established:

With trusted connection and snippets listed above it’s possible to programmatically add firewall rule to whitelist public IP of service/resource that requires access. Finding IP might not be trivial task. Azure Sql DB/DW captures failed login events in Audit log by default. Cosmos DB on the other hand has more configuration options that are also well documented (e.g. list of public IPs for Data Explorer available in Azure Portal). Worst case scenario - public IP is unknown/unreliable/difficult to get. There’s a method to temporarily enable ‘Allow access to Azure services’ which is firewall rule represented by IP ‘0.0.0.0’ and remove it immediately after task completion (analogy with AzureAD Conditional Access/Privileged Identity Management etc. - in general adaptive security with just-in-time and just-enough permissions). Example of Automation runbook that gets just-in-time access to Azure Sql Database using AzureRunAsConnection automation connection:

Effective time with ‘Allow access to Azure services’ option enabled should be as short as possible.