Azure Firewall settings for VSTO add-in & SQL

0
votes

I've written a pretty basic SQL Backed VSTO Outlook addin, which will eventually be used by about 20 people in several offices of the company I work for. All will also be working from home periodically.

The tutorials I've followed so far go through adding "your own" IP address to the server firewall rules: https://docs.microsoft.com/en-gb/azure/sql-database/sql-database-get-started-portal-firewall

As does a similar question I came across during my research for this question: SQL Azure Firewall Rules on New Portal

This wasn't a problem when it was just me using the app, I followed the tutorial once to add my office IP address, and once to add my home IP address. It would be very awkward though if I had to get everyone to collect their IP addresses and set each of them up on the firewall.

This would be my first application to use remote resources, so I'm unsure of the next steps with the firewall, and so struggling to find a guide. Should I be:

  1. Opening up the firewall to all, but creating a strong password stored within the app
  2. Creating an initialisation step within the app to grant new users access through the firewall
  3. Create some kind of proxy so that all of the SQL requests come from the same IP address.

In the case of (2) creating an initialisation step - azure sql server firewall settings appears to change a firewall rule - wouldn't that mean only one person can connect at once?

2
azureazure-sql-databasevsto
Is the add-in installed on people's computers? Wouldn't the database password be then available to anyone with access to that PC? It sounds a bit of a risky approach. – juunas
The add-in would be installed as part of their MS Outlook installation. I don't believe the source-code would be visible to them? Azure tutorials encode the database username and password into the source code. docs.microsoft.com/en-us/azure/sql-database/… – Martin KS
This article suggests a "middle tier" application - similar to my option (3) but doesn't go into any detail. Would I then have to write a user-authentication application as well, or is there a straightforward way to authenticate this against Active Directory or similar? social.msdn.microsoft.com/Forums/sqlserver/en-US/… – Martin KS
Some kind of middle tier API would probably be the best choice. I can't say what the recommended authentication method is though. – juunas

2 Answers

1
votes

The best option would be:

1a. Opening up the firewall to all, but requiring Azure Active Directory Authentication for the users.

1
votes

You can create a small application that can remotely update an Azure SQL firewall rule with a local PC IP address. For that you will need Microsoft Management Fluent library and Resource Group and Resource Management (Fluent) capabilities for Microsoft Azure. Here you will find a sample application.

You may also need to register the application as an Azure Active Directory application as explained here and assign a corresponding role for it. Then you will be able to create an authorization file for the application as explained here.

subscription=########-####-####-####-############
client=########-####-####-####-############
tenant=########-####-####-####-############
key=XXXXXXXXXXXXXXXX
managementURI=https\://management.core.windows.net/
baseURL=https\://management.azure.com/
authURL=https\://login.microsoftonline.com/
graphURL=https\://graph.windows.net/

You can also also provide all users with a PowerShell script that can get authenticated on its own to SQL Azure using an Azure Active Directory authentication token as explained here and then update a firewall rule or create a new one using the following script.

New-AzureRmSqlServerFirewallRule -ResourceGroupName "myResourceGroup" 
      -ServerName $servername -FirewallRuleName "AllowSome" -StartIpAddress "0.0.0.0" -EndIpAddress "0.0.0.0"