Friday, October 7, 2016

How to check success and failure logins attepmts in Azure SQL Database

Who tried to access my Azure SQL Database? Were they succeeded? Can I get more details on attempts?

If you are maintaining a Azure SQL Database, you should be asking these questions yourself. Or, practically, you need to have these questions in order to protect your database. Generally, with on-premise instance, we get these information using either Audit or Logon Triggers. Can we get the same from Azure SQL Database?

Azure SQL Database has Auditing that can be easily enabled via the portal. It does not support Logon Triggers and notifications cannot be sent via emails as it does not support Database Mail. However Audit captures everything you need and there are few ways of seeing them.

In order to see required information for my databases, I enabled Audit and then use Azure Storage Explorer to see all details because it is the easiest way. If you need the same, here is the way of doing it;

1. Open the Azure Web Portal and open SQL Server blade.
2. Click on the server you need to audit and select Auditing and Threat detection.
3. Enable Auditing. Select Table as Auditing type because it is easy to read.
4. Create a new storage for storing audit records.
5. Set the Retention (Days) as you need and set the Table name as you like.

6. Select Audited Events and check both Success and Failure check-boxes.
7. Select all Threat Detection Types.

8. Click on Save to save all settings.

As I mentioned above, there are multiple ways of getting audit records to our environment. Let's use Azure Storage Explorer and see how we can see them. Let's see how we can use other channels like Power BI with another post later.

1. Open Azure Storage Explorer and configure the connection for the storage you use. Read more on this at:
2. You should be able to see all required details related to login attempts.

No comments: