SQL SERVER – SQL Audit Date Time Does Not Match Machine Date Time – Solution

Even though my consulting is mostly around performance tuning, I can see many customers who want to start using the new capabilities of SQL Server for their existing application as they plan to do an upgrade of their infrastructure. In one of engagement with a bank, they wanted to explore more about SQL Auditing capability. Since they were new I got engaged with them in implementing compliance, which they must follow. In the recent past, I have written below blogs on SQL Audit.

SQL SERVER – Error 33222, Level 16 – Audit ‘MyAudit’ Failed to Start

SQL SERVER – Script to Audit Login and Role Member Change

Solarwinds

While I was on site, one of their DBA readers sent me an email stating that the time in the log is incorrect. I asked him to share the screenshot. I also reproduce the same behavior in my own server.

As we can see below, the time I clock is around 5:30 AM and in audit log it was shown as around midnight.

SQL SERVER - SQL Audit Date Time Does Not Match Machine Date Time - Solution aud-utc-01

WORKAROUND/SOLUTION

I looked at the time difference reported and found that there is a difference on 5 hr. 30 min. which is the exact time difference between IST and UTC!! After some internet searching, it was clear that the SQL audit will record the time in UTC time into the logs rather than local time. So, we need to use the following query in our script

SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), CURRENT_TIMESTAMP), event_time) AS event_time_afterconvert
	,getdate() 'Current_system_time'
	,*
FROM fn_get_audit_file('G:\DATA\*', DEFAULT, DEFAULT)

Once we understood the concept, we implemented SQL Audit and they were able to get compliance reports.

What tool do you use to audit SQL Server activities? Do you use 3rd party solutions? Please reply by the comments and let me know.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – Event ID 1045 – No Matching Network Interface Found for Resource ‘Cluster IP Address’ IP address ‘10.10.10.10’ (Return Code was ‘5035’)
Next Post
SQL SERVER – Edition Upgrade – Invoke or BeginInvoke Cannot be Called on a Control Until the Window Handle has Been Created

Related Posts

4 Comments. Leave new

Leave a Reply

Menu