In addition to IaaS components, Contoso make use of an Azure SQL Database. Azure SQL is a PaaS service, where Microsoft assume responsibility for the underlying infrastructure and offer SQL ‘as a service’. However, there are still some security considerations that Contoso would like your assistance with.
In this lab, we’ll lock the SQL database down to allow only certain IP addresses, as well as enable additional auditing and logging.
1) In the Azure portal, navigate to the Contoso-PaaS resource group. Within this resource, navigate to the SQL server resource named ‘contososql’.
2) From the menu, select Firewall / Virtual Networks’.
3) You’ll see that no firewall rules are currently configured, however you’ll also see a suggested ‘client IP’ address based on your IP, as shown in Figure 17.
Figure 17: Enabling SQL Database Firewall
4) Click ‘Add Client IP’ at the top of the page and then save.
5) If you have SQL Server Management installed on your PC, you may test access to the SQL database. To do this, obtain the full server name by returning the ‘overview’ page and copying the server name from here. Use this server name to connect to your SQL database server from SQL management studio. You should be able to connect as the firewall is configured with your IP address.
In this exercise, we’ll enable auditing and threat detection for the Contoso SQL database. Auditing tracks database events and writes them to an audit log in Azure storage (similar to the storage logs you configured earlier). Threat Detection provides security alerts for suspicious activities relating to the SQL database.
1) In the Azure portal, navigate to the Contoso-PaaS resource group and then select the SQL database server resource named ‘contososql’.
2) Select ‘Auditing’ from the menu.
3) Change auditing to ‘On’ and select the storage account you used earlier (contosoiaas). Change the retention to 2 days.
4) Select ‘Advanced Threat Protection’ from the menu.
5) Click ‘Enable Advanced Threat Protection on the Server’.
Note: It is possible to enable auditing at both the server and SQL database level, however it is recommended to enable server level auditing only as this will also apply to all databases. More guidelines are available at https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auditing.
6) Navigate to the SQL database (‘ContosoDB’). Under the ‘Auditing’ menu item, click on ‘View Audit Logs’.
7) If you are able to log on to the database (i.e. if you have SQL Server Management Studio installed), you can do so (try a few failed attempts as well). After some time, you should see the audit log populated.