Capturing errors and auditing in Azure SQL DB

Logging is a necessity in the database world, well, the entire tech world. You need to know what is happening in and around your databases. It’s easy to setup in Azure SQL DB.

This post will guide you through setting up and querying diagnostic settings to access your database logs easily.

Setting up a diagnostic setting

It’s a good idea to have a dedicated log analytics workspace, rather than using the default one. You can use this to create one before setting up your diagnostic setting, or you can use an existing one.

It’s quite easy to set up capturing errors for Azure SQL DB. You need to set up a diagnostic setting at the database level, under Monitoring > Diagnostic Settings.

Click Add diagnostic setting.

Logging/metrics options

Let’s review our options for logging.

Additionally, you have some other options with metrics.

The logs and metrics capture different types of information.

For example:

• Metrics show you that there’s a problem (high CPU, long waits)
• Logs show you what caused it (specific queries, deadlocks, errors)

When choosing what to select, allLogs gives you all the logs (imagine that!), and audit gives you only Devops Operations Audit Logs and SQL Security Audit Event.

Configuring the diagnostic setting

I’ve decided to set up all logs except SQL Security Audit Event, which I will configure separately, along with all metrics, and then put it in my SQLLAW and click Save.

Now, my diagnostic setting is there.

To capture audits of what happens inside the database, you also need to enable auditing, and I’ll put it in the same Log Analytics workspace as my logs.

And once you save that auditing piece, you can revisit the diagnostic settings and see it there.

It would be nice if the SQL audit worked with the checkbox in the logging diagnostic setting I set up, but alas, it doesn’t, so I had to resort to using it with the official auditing function.

Now, we have all the bits and pieces for logging and auditing in the same log analytics workspace, and we can query it.

View the log and audit data

To do this, you need to access the Log Analytics workspace and run a Kusto query. I have a Kusto tutorial available here, but I’ll also provide some additional information below.

Before we even attempt to examine the logs, let’s make some things happen; otherwise, the results will be very uninteresting, namely, nothing returns. I logged into my database and attempted to create a table incorrectly, then created it correctly to ensure something was logged. If you are setting this up on a legit database that has things happening in it, you won’t have to do that.

That being said, I’m going to my log analytics workspace now and clicking on Logs.

A box of suggested queries pops up, but I never use it. I never found it particularly helpful for anything I wanted to do. I close that and use the query window, with KQL mode.

Querying log results

Then, I will run this query. This will display all the information in the log related to Azure SQL.

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL"

There are a lot more columns, too.

What I find helpful is to project this out into a table with only the columns I want to see, and I also like to include some of the other filters in that query.

Maybe you only want to see one category of logs. Here are the categories available to you with Azure SQL DB:

Additionally, you have options for handling TimeGenerated, including functions and time units ago.

Time Units for ago()

And what we wind up with his something like this:

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL"
  and TimeGenerated > ago(1d) 
  and Category == "Errors"
| project TimeGenerated, Message, ResourceGroup, DatabaseName_s

If you have errors, you will get results similar to the following screenshot. Note that I don’t usually put things in the default resource group, but I was too lazy to create a new one for this demo, even though it’s not a lot of work.

Querying audit results

To get the audit records for people querying inside the db, you can use the following query, but note that SQL Security Audit Events can take longer (up to 2 hours delay after setting up) to process and appear compared to the other diagnostic logs like performance metrics, errors, etc.

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL"
  and TimeGenerated > ago(4d) 
  and Category == "SQLSecurityAuditEvents"
| where database_name_s != 'master'
| project
event_time_t, 
        ResourceGroup,
        server_instance_name_s,
database_name_s, 
statement_s, 
server_principal_name_s, 
succeeded_s,
client_ip_s, 
application_name_s, 
additional_information_s, 
data_sensitivity_information_s

I specifically searched for the DROP TABLE statements I ran.

And if you haven’t used SQL audit before, you will be surprised at all the stuff that happens behind the scenes that also gets audited. You can configure the auditing to capture fewer actions by modifying the actions it captures. I cover that in this post.

I’m going to create a dashboard for these results, allowing anyone at my company to query this data in the future easily. That will be covered in a future post, but I will probably use Azure Workbooks.

The post Capturing errors and auditing in Azure SQL DB appeared first on sqlkitty.