Title: The DBMS must use system clocks to generate timestamps for audit records.
Vulnerability ID: V-32391
IA Controls: None
Description: Determining the correct time a particular event occurred on a system is critical when conducting forensic analysis and investigating system events.
Timestamps generated by the information system shall include both date and time. The time may be expressed in Coordinated Universal Time (UTC), a modern continuation of Greenwich Mean Time (GMT), or local time with an offset from UTC.
If time sources other than the system time are used for audit records the timeline of events can get skewed. This makes forensic analysis of the logs much more unreliable.
Check Text: Review DBMS settings to determine if audit logs are being recorded with time pulled from the underlying system. If timestamps on audit logs are based on something other than the system clock or a database clock synchronized with the system clock, this is a finding.
Fix Text: Modify DBMS settings to stamp audit records only with timestamps based on the underlying system clock or on a database clock synchronized with the system clock.[divider]
The TIMESTAMP data type in SQL Server is not what you are looking to use here. The TIMESTAMP data type in SQL Server is a unique internal counter used by SQL and really has no relevance in making sense of anything recorded with it. In SQL Server, the GETDATE() function will return the current server /system timestamp as a DATETIME value with no time zone offset. There are optional SYSDATETIME(), SYSUTCDATETIME(), and SYSDATETIMEOFFSET functions that can be used for more precision.
SYSDATETIME() produces more fractional seconds than GETDATE().
SYSUTCDATETIME() produces the same fractional seconds as SYSDATETIME() but returns the UTC time rather than local time.
SYSDATETIMEOFFSET() will return the same fractional seconds as SYSDATETIME and SYSUTCDATETIME, but adds the time zone offset as well.
You can see the different results from each of these functions on your server by running the following script:
SELECT GETDATE() AS GET_DATE ,
SYSDATETIME() AS SYS_DATETIME ,
SYSUTCDATETIME() AS SYS_UTC_DATETIME ,
SYSDATETIMEOFFSET() AS SYS_DATETIME_OFFSET