AZURE SQL DB AND LOG ANALYTICS BETTER TOGETHER – PART #1

Microsoft

Aug 08, 2019

As a DBA you may want to query SQL Audit and SQL Diagnostics information. The easiest way to do this is sending to Log analytics that is part of Azure Monitor

You can also send this data to Event Hubs and storage accounts. On this post I will focus on Log Analytics

Check also other parts

AZURE SQL DB AND LOG ANALYTICS BETTER TOGETHER – PART #2 – ALERTS
AZURE SQL DB AND LOG ANALYTICS BETTER TOGETHER – PART #3 - Query AUDIT data or Who dropped my TABLE?

1 – FIRST CREATE AN LOG ANALYTICS WORKSPACE

Click on Create a Resource and search for Log Analytics

2 – ENABLE AUDIT TO LOG ANALYTICS

At Server level or at Database level, enable auditing and send log to Log Analytics and select the workspace you just created

3 – ENABLE DIAGNOSTICS TO LOG ANALYTICS

*This configuration is done PER DATABASE

Click on Diagnostics Settings and then Turn On Diagnostics

Select to Send to Log Analytics and select the Log Analytics workspace. For this sample I will selected only Errors

4 – QUERING LOG ANALYTICS

Just go to the Log Analytics workspace and query the database using a language called Kusto. More information and sample on sintax can be found on this link

A Kusto query is a read-only request to process data and return results. The request is stated in plain text, using a data-flow model designed to make the syntax easy to read, author, and automate. The query uses schema entities that are organized in a hierarchy similar to SQL’s: databases, tables, and columns.

The data we will work in this sample can be found on table AzureDiagnostics

Just write a query and click Run

4.1 FAILED LOGINS SAMPLE

For this sample use query below to find failed logins

//Failed Logins
AzureDiagnostics
| where TimeGenerated >= ago(5d) //Events on last 5 days
| where action_name_s == "DATABASE AUTHENTICATION FAILED"
| extend additional_information_xml=parse_xml(additional_information_s)
| extend Error_Code=additional_information_xml.login_information.error_code
| extend Error_State=additional_information_xml.login_information.error_state
| project
    TimeGenerated, event_time_t,
    ResourceGroup, LogicalServerName_s, database_name_s,
    session_id_d, client_ip_s, application_name_s, session_server_principal_name_s,
    Error_Code, Error_State
| order by TimeGenerated desc

Expanding this event we can see all information from this event

Date/Time
Server
DB
Application Name
User
IP
Error Code and Error State
etc

You can then use this Error Code and State to find what is the error description

In this case:

– Error 18456: Login failed for user ‘%.ls’.%.ls
– State 8: The password is incorrect

REF: https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error

4.2 ERRORS GENERIC

Use the query below to find all errors or specific errors

//Errors Query
AzureDiagnostics
| where TimeGenerated >= ago(30d) // Last 30 days
| where Category =~ "Errors"
| where error_number_d == 8134 //Divide by zero error encountered.
//| where Message contains "divide" //Or search by text
| project
    TimeGenerated,
    ResourceGroup,
    LogicalServerName_s,
    DatabaseName_s,
    Message,
    error_number_d,
    Severity,
    state_d
| order by TimeGenerated desc

You can find some information related to the errors. Can be useful to consolidate information, histogram, alerts, etc

*Notice that there is no information on who caused the error. Because this information came from Diagnostics information, not the audit

4.3 ERRORS DETAILED

With this other sample below you can get the queries that failed with full information on who did it, what query, etc.

//Detailed errors
AzureDiagnostics
| where TimeGenerated >= ago(15d)  //Last 15 days
| where Category =~ "SQLSecurityAuditEvents"
| where succeeded_s == "false"
| where additional_information_s contains "8134" //Looking for specift error. Ex Div by zero
| extend additional_information_xml=parse_xml(additional_information_s)
| extend failure_reason=additional_information_xml.batch_information.failure_reason
| project    
    ResourceGroup,
    LogicalServerName_s,
    database_name_s,
    session_id_d,
    action_name_s,
    client_ip_s,
    application_name_s,
    failure_reason,
    statement_s,
    additional_information_s