Secure Azure Infra – 6:Securing Azure SQL

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.

6.1: Enable SQL Database Firewall

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.

SQL Firewall

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.

6.2: Enable SQL Database Auditing and Threat Detection

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.

About engsoon

Eng Soon is a 4-time Microsoft MVP and has nearly 5 years of experience building enterprise system in the cloud.He is also a Certified Microsoft Azure.Eng Soon also have strong technical skills and analytic skill. As a developer, Besides the development task, he also involved in Project Management, Consulting, and Marketing. He has a passion for technology and sharing what he learns with others to help enable them to learn faster and be more productive. He also took part as speaker in many nationwide technical events, such as Conference, Meetup and Workshop. Currently, looking for opportunity in Cyber Security which include Cloud Security and Application Security.

View all posts by engsoon →

Leave a Reply

Your email address will not be published. Required fields are marked *