question

Zippo706-1033 avatar image
0 Votes"
Zippo706-1033 asked NavtejSaini-MSFT commented

Azure SQL - Auditin

Adding auditing in easy in SQL Azure (non managed), however the field list is HUGE and most of it is useless from a security perspective. Is there a way i can audit the database, but only with certain fields, Ie dbname, upn, ip, client name, statement,duration, login ok, login fail. Other things like sid, hash, etc are not required.

Is there a way to do this?
Will this screw up LogAnalytics/Sentinel?

azure-sql-database
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@Zippo706-1033 Please let us know if you need any further help.

0 Votes 0 ·
AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered AlbertoMorillo edited

If you wish to audit logins successful/unsuccessful connections, once you enable audit you open View Audit Logs and can search for successful and unsuccessful login events. It should provide you useful information like hostname and IP address. Please click here for more details.

You can also audit successful/unsuccessful authentications and DML T-SQL statements for an specific database using PowerShell as shown below:

  Set-AzureRmSqlDatabaseAuditing -ResourceGroupName "resourceGroup" -ServerName "SQL Server Name" -DatabaseName "AdventureWorksLT" -StorageAccountName "storageAccount" -AuditActionGroup "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP", "FAILED_DATABASE_AUTHENTICATION_GROUP", "BATCH_COMPLETED_GROUP" -AuditAction "UPDATE ON database::[AdventureWorksLT] BY [public]"  -RetentionInDays 60

Another example, this time how to collect who dropped database objects on a specific database and schema:

 Set-AzureRmSqlDatabaseAuditing `
 -State Enabled `
 -ResourceGroupName "resourcegroupname" `
 -ServerName "ssqlinstancename" `  #ssqlinstancename.database.windows.net
 -StorageAccountName "strageaccountname" `
 -DatabaseName "dbname" `
 -AuditActionGroup 'SCHEMA_OBJECT_CHANGE_GROUP' `
 -RetentionInDays 8 `
 -AuditAction "DELETE ON schema::dbo BY [public]"


Azure SQL Auditing should provide you the application name, duration, login name and client IP address that executed the query if you look for the event type BATCH_COMPLETED once you have enable Auditing. Please see above examples.

The Auditing feature won't screw up Log Analytics, Azure Monitor or Intelligence Performance features.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Zippo706-1033 avatar image
0 Votes"
Zippo706-1033 answered AlbertoMorillo commented

Thank you for the response. The goal here is to audit the database, but
Will it act secondary to the server audit. IE. Full Server, I audit to blobs, but database DB1, I want to send to loganalytics (and sentinel) or splunk via event hub.

My use case is:
someone in headquarters or over vpn used the production web to sql account to log in to the database. I would know that as the IP address would be something like 55.55.55.55 or client hostname would not match webserver1, webserver2, etc. I don't want to audit a select or update statement from webserver1, webserver2, etc.
2nd use case.
Production support has the ability to update the db. They would use their domain\user account w/ MFA. However, i want to catch the statement that was made by them (ie: delete from user where username='userx' or update user where username='userx'. There is change control, but developers/support don't always adhere to it. Their IP would always be 55.55.55.55.

Fyi, logging everything to blob isn't very helpful as its in xel format and only use that as a full fallback. Querying the blobs via fn autit queries takes 15 min per query.

Again, I know i can capture this in full server logging, but our logs are 100 gig/day and have several (sometimes 30 or more) minutes of latency in both log analytics and splunk as they have to index not only every request, but logging fields it really doesn't need (SID, has, etc).

Would this be a better case for events?

Really just looking for soime direction here.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

If you examine the PowerShell examples I provided on my first reply, you will notice I am showing you how to collect only what you want. How to collect DELETE, UPDATE statements on specific databases (not the whole server). You can adapt those examples to add INSERT statements also. So you are no longer capturing everything (you will be avoiding for example SELECT statements, DDL language, etc), that will make the auditing records you are capturing on storage accounts of sizes easier to handle, sizes that log analyzers can process quicker. You will be capturing less information.

0 Votes 0 ·