microsoft / azure-sql-db-auditing-oms-integration Goto Github PK

A sync application that utilizes OMS public APIs to push SQL audit logs into OMS for advanced log consumption and analysis capabilities.

Batchfile 3.83% C# 96.17%

azure-sql-db-auditing-oms-integration's Introduction

#Azure SQL DB Auditing log integration into OMS

You can now directly set OMS as your Azure SQL DB auditing logs target, eliminating the need for this sync application.

OMS and Event Hubs have been added as built-in auditing targets alongside Blob storage. This new capability can reduce your auditing costs and is much more reliable than using the sync application. For further information and instructions please visit https://docs.microsoft.com/azure/sql-database/sql-database-auditing#subheading-2

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.

Prerequisites

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:

azure-sql-db-auditing-oms-integration's People

Contributors

Stargazers

Watchers

azure-sql-db-auditing-oms-integration's Issues

This repo is missing a LICENSE file

This repository is currently missing a LICENSE file.

A license helps users understand how to use your project in a compliant manner. You can find the standard MIT license Microsoft uses at: https://github.com/microsoft/repo-templates/blob/main/shared/LICENSE.

If you would like to learn more about open source licenses, please visit the document at https://aka.ms/license (Microsoft-internal guidance).

Custom fields are not created when deploying the application

Hello All,
It's a great solution that we are seeing here.
I'm encountering something wired.
After importing the Designer view file. An error is thrown when loading the views : Unknown type 'SQLAuditLog_CL'
I have checked the custom fields, and i did not find any CF with this name.
Looks like the deployment operation is not creating the CFs.

This repo is missing important files

There are important files that Microsoft projects should all have that are not present in this repository. A pull request has been opened to add the missing file(s). When the pr is merged this issue will be closed automatically.

Microsoft teams can learn more about this effort and share feedback within the open source guidance available internally.

Merge this pull request

Licensing Information not provided in this repo

Is the source code in this repo covered by the MIT License?

Microsoft.WindowsAzure.Storage.StorageException: The operation has timed out.

I have setup this webjob to read SQL Audit logs from BLOB.
It works for 5 of our databases fine. But when it attempts to import the production db logs, it fails with the following exception:

[03/22/2018 10:43:46 > c6d3e7: INFO] OMS Error: 0 : Dumpping headers: Failed processing: https://production.blob.core.windows.net/sqldbauditlogs/<servername>/<DB_Name>/SqlDbAuditing_ServerAudit/2018-03-13/07_34_58_818_7898.xel. Reason: Microsoft.WindowsAzure.Storage.StorageException: The operation has timed out. ---> System.TimeoutException: The operation has timed out. [03/22/2018 10:43:46 > c6d3e7: INFO] at Microsoft.WindowsAzure.Storage.Core.Executor.Executor.EndGetResponse[T](IAsyncResult getResponseResult) in c:\Program Files (x86)\Jenkins\workspace\release_dotnet_master\Lib\ClassLibraryCommon\Core\Executor\Executor.cs:line 282 [03/22/2018 10:43:46 > c6d3e7: INFO] --- End of inner exception stack trace --- [03/22/2018 10:43:46 > c6d3e7: INFO] Request Information [03/22/2018 10:43:46 > c6d3e7: INFO] RequestID: [03/22/2018 10:43:46 > c6d3e7: INFO] RequestDate: [03/22/2018 10:43:46 > c6d3e7: INFO] StatusMessage: [03/22/2018 10:43:46 > c6d3e7: INFO] [03/22/2018 10:43:46 > c6d3e7: INFO] HTTP Request: [03/22/2018 10:43:46 > c6d3e7: INFO] User-Agent: Azure-Storage/7.2.1 (.NET CLR 4.0.30319.42000; Win32NT 6.2.9200.0) [03/22/2018 10:43:46 > c6d3e7: INFO] x-ms-version: 2015-12-11 [03/22/2018 10:43:46 > c6d3e7: INFO] x-ms-client-request-id: 5a5be323-5113-41f9-80c7-6ecb994e5162 [03/22/2018 10:43:46 > c6d3e7: INFO] x-ms-date: Thu, 22 Mar 2018 10:38:46 GMT [03/22/2018 10:43:46 > c6d3e7: INFO] Host: production.blob.core.windows.net

I have tried upscaling the App Service Plan and checked the storage metrics, all are fine.
Since this is our PROD db, the Audit log files are quite large, 50mb each, and maybe 10-20 files per hour are stored.

OMS View does not import

When attempting to import the OMS view that is included, the following error is received after uploading into the view designer:
"Cannot convert the view. Check the view queries."

To reproduce this issue:

Open the Azure portal
Navigate to the OMS workspace
On the OMS Workspace blade, select View Designer
Select Import at the top
Browse to a local downloaded copy of the OMS view included in this repo

Missing field "data_sensitivity_information"

The new data_sensitivity_information field is missing from the JSON object created and uploaded to OMS.

Job failing at ReadStringIntoBuffer

Until today it was working fine, and suddenly the Job fails

[07/20/2018 08:07:00 > 801899: SYS INFO] Status changed to Initializing
[07/20/2018 08:07:00 > 801899: SYS INFO] Run script 'SQLAuditLogsToOMSJob.exe' with script host - 'WindowsScriptHost'
[07/20/2018 08:07:00 > 801899: SYS INFO] Status changed to Running
[07/20/2018 08:07:02 > 801899: ERR ]
[07/20/2018 08:07:02 > 801899: ERR ] Unhandled Exception: System.TypeInitializationException: The type initializer for 'XEL2OMS.Program' threw an exception. ---> Newtonsoft.Json.JsonReaderException: Unterminated string. Expected delimiter: ". Path 'modified.TPRA.SqlDbAuditing_ServerAudit.Date', line 1, position 24576.
[07/20/2018 08:07:02 > 801899: ERR ] at Newtonsoft.Json.JsonTextReader.ReadStringIntoBuffer(Char quote)
[07/20/2018 08:07:02 > 801899: ERR ] at Newtonsoft.Json.JsonTextReader.ParseProperty()
[07/20/2018 08:07:02 > 801899: ERR ] at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.PopulateObject(Object newObject, JsonReader reader, JsonObjectContract contract, JsonProperty member, String id)
[07/20/2018 08:07:02 > 801899: ERR ] at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
[07/20/2018 08:07:02 > 801899: ERR ] at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.PopulateDictionary(IDictionary dictionary, JsonReader reader, JsonDictionaryContract contract, JsonProperty containerProperty, String id)
[07/20/2018 08:07:02 > 801899: ERR ] at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
[07/20/2018 08:07:02 > 801899: ERR ] at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.PopulateDictionary(IDictionary dictionary, JsonReader reader, JsonDictionaryContract contract, JsonProperty containerProperty, String id)
[07/20/2018 08:07:02 > 801899: ERR ] at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
[07/20/2018 08:07:02 > 801899: ERR ] at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.PopulateDictionary(IDictionary dictionary, JsonReader reader, JsonDictionaryContract contract, JsonProperty containerProperty, String id)
[07/20/2018 08:07:02 > 801899: ERR ] at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateObject(JsonReader reader, Type objectType, JsonContract contract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerMember, Object existingValue)
[07/20/2018 08:07:02 > 801899: ERR ] at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.Deserialize(JsonReader reader, Type objectType, Boolean checkAdditionalContent)
[07/20/2018 08:07:02 > 801899: ERR ] at Newtonsoft.Json.JsonSerializer.DeserializeInternal(JsonReader reader, Type objectType)
[07/20/2018 08:07:02 > 801899: ERR ] at XEL2OMS.Program.GetStates(String fileName) in D:\home\site\repository\src\XEL2OMS\Program.cs:line 287
[07/20/2018 08:07:02 > 801899: ERR ] at XEL2OMS.Program..cctor() in D:\home\site\repository\src\XEL2OMS\Program.cs:line 29
[07/20/2018 08:07:02 > 801899: ERR ] --- End of inner exception stack trace ---
[07/20/2018 08:07:02 > 801899: ERR ] at XEL2OMS.Program.Main() in D:\home\site\repository\src\XEL2OMS\Program.cs:line 340
[07/20/2018 08:07:02 > 801899: SYS INFO] Status changed to Failed
[07/20/2018 08:07:02 > 801899: SYS ERR ] Job failed due to exit code -532462766

Deploy App Service without public facing endpoint

I would like to use this application to ingest SQL Data Warehouse logs into OMS but, due to security concerns, do not want a public url to be created from this. What mechanisms can I use to accomplish this? Is it as simple as putting it in a VNet or are there additional steps to take?

Webjob failed to load audit logs to OMS due to insufficient space

Hi,

It's been more than a week now since I rebuilt the web app to push our SQL audit logs to OMS. After a couple of hours, web job has failed to continue and getting the error below. I already raised a ticket with Microsoft but they directed me here to raise the issue. They stated that the path in the error is not in any of their OMS servers.

OMS Information: 0 : Was not able to delete file: D:\home\site\wwwroot\u400yj5k.5s2.xel. Reason: There is not enough space on the disk.

Specify App Service Plan?

Is there a way to specify the App Service Plan to create the website under? I would prefer not to create a new B1 ASP, when I already have an S1 ASP instance with slots available.

Thanks

microsoft / azure-sql-db-auditing-oms-integration Goto Github PK

azure-sql-db-auditing-oms-integration's Introduction

You can now directly set OMS as your Azure SQL DB auditing logs target, eliminating the need for this sync application.

Prerequisites

azure-sql-db-auditing-oms-integration's People

Contributors

Stargazers

Watchers

Forkers

azure-sql-db-auditing-oms-integration's Issues

Recommend Projects

Recommend Topics

Recommend Org