#Azure SQL DB Auditing log integration into OMS
This is a sync application that runs in Azure and utilizes OMS public APIs to push SQL audit logs into OMS.
It allows using OMS Log Analytics to explore and analyze your database activity, and gain insight into discrepancies and anomalies that could indicate potential business concerns or suspected security violations.
Azure SQL Database Auditing tracks database events and writes them to an audit log in your Azure Storage account. Azure SQL Database Auditing can help you maintain regulatory compliance, understand database activity, and gain insight into discrepancies and anomalies that could indicate business concerns or suspected security violations.
Operations Management Suite (OMS) Log Analytics helps you correlate, search, and act on log and performance data generated by operating systems, applications and databases. It gives you real-time operational insights using integrated search and custom dashboards to readily analyze millions of records across all of your workloads and servers. For additional useful information about OMS Log Analytics search language and commands, see Log Analytics search reference.
- Applies only to Azure SQL Databases with Blob Auditing enabled
- Azure Subscription with resource creation permissions
- OMS workspace with Administrator or Contributor permissions
#Estimated Cost of Deployed Resources
| Resource | Cost/Month | Cost/Hr |
|---|---|---|
| B1 App Service Plan | $55.80 | $0.075 |
| Storage Plan | ~$0 | $0.0036 / transaction |
#Setup Guide
###Retrieve SQL DB Auditing - Storage Connection String
-
Launch the Azure Portal at https://portal.azure.com.
-
Navigate to the Access keys blade of the storage account. Then click on the Context Menu ("...") to the right of key1, and click on View connection string. Copy & Save the Connection String for use in following steps.
###Retrieve OMS Workspace ID and Access key
-
Launch the Microsoft Operations Management Suite (OMS) at https://mms.microsoft.com.
-
Choose the relevant workspace.
-
In the top menu bar, click on the Settings icon.
-
Click on Connected Sources, then click on Windows Servers. Copy & Save the Workspace ID and Primary Key for use in following steps.
###Deploy sync application to Azure
-
Click on the Deploy to Azure button below to initiate deployment process.
During deployment, use the Storage Connection String, Workspace ID, and Primary Key that you saved in the previous steps.
-
When deployment is completed, you can close the web application browser window.
You will not be required to access this application, it will be running in the background, periodically pushing the audit logs to OMS.
Important
Once the sync application is deployed in Azure, it can take up to 5-10 minutes for initial data to start appearing in your OMS workspace.
###Import Azure SQL DB audit sync dashboard to OMS
-
Download SQLDatabaseAudit.omsview to your PC.
-
From the OMS dashboard, click on the View Designer tile.
-
At the top of the View Designer page, click on Import. An Upload from computer will appear at the bottom of the page - choose the SQLDatabaseAudit.omsview file that you downloaded to your PC in step 1. Then click Save at the top.
-
A SQL Database Audit tile will now appear on your dashboard.
-
Click on the SQL Database Audit tile to view the database activity report.
# Troubleshooting
Once the sync application is deployed in Azure, it can take up to 5-10 minutes for initial data to start appearing in your OMS workspace.
If you've completed the setup process but don't see audit data in your OMS workspace, you'll be able to review the logs for the import operation job in the Azure portal to try and identify the problem:
-
Go to the App Service that you created during the deployment of the sync application.
-
Click on "WebJobs" on the left menu and then on "Logs" in the top menu.
-
In the page that opens, you'll be able to view the logs for a specific run by clicking on the relevant job run link:
