SQL Server Audit til GeoEnviron databaser

  Vejledning til oprettelse af SQL Server Audit for GeoEnviron databaser.

Geokon har lavet to SQL scripts, SQLAuditServer.sql og SQLAuditDatabase.sql, der kan benyttes til oprettelse af såkaldte SQL Server Audits. 

SQL Server Audit er baseret på aktioner og aktionsgrupper. (se mere her: https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-2017).

En SQL Server Audit kan indeholde auditspecifikationer på serverniveau og databaseniveau:

• Server-niveau audit består af audit handlinger på serverniveau, som omfatter serveroperationer, såsom sikkerhedsoperationer, der involverer logins, roller og tilladelser, logon og logoff-operationer, database backup og gendannelse, manipulation af diverse database-, server- og skemaobjekter.
• Database-niveau audit konfigureres individuelt på hver database. Denne funktion er - før SQL Server 2016 SP1 - ikke tilgængelig i alle udgaver af SQL Server, kun i Enterprise-udgaver.
• Audit på databaseniveau gør brug af aktionsgrupper på databaseniveau og audit-handlinger på databaseniveau. Aktionsgrupperne på databaseniveau dækker til en vis grad samme områder som auditgrupperne på serverniveau men på databaseniveau.
• Audit på ​​databaseniveau giver også mulighed for audit af individuelle handlinger som f.eks. SELECT, INSERT, UPDATE, DELETE, EXECUTE, RECEIVE og REFERENCES. Disse handlinger kan begrænses til en bestemt database, et objekt (f.eks. Tabel, visning, stored procedure) eller et skema.

Fra og med SQL Server 2012 er SQL Server Audit delvist tilgængelig i alle udgaver. Indtil SQL Server 2012 var denne audit-funktion (introduceret i SQL Server 2008) kun tilgængelig i Enterprise og Datacenter-udgaver. Fra og med SQL Server 2012 blev audit-delen på ​​serverniveau af SQL Server gjort tilgængelig i alle udgaver, idet audit på databaseniveau indtil SQL Server 2016 SP1 kun er tilgængelig  Enterprise-udgaven.

Her er et resumé af SQL Server Audit support i de forskellige SQL Server versioner (Geokon supporterer ikke SQL Server 2008, og denne version er derfor ikke vist):

SQL Server Database Audit

Det ene script, SQLAuditDatabase.sql, kan anvendes til at oprette SQL Server Database Audit til GeoEnviron databasen - dvs. logning af GeoEnviron databaseforespørgsler og -ændringer - undtagen select statement (dog bliver eksekvering (execute) af stored procedures logget).

Scriptet opretter hhv. SQL Server Server Audit og SQL Server Database Audit Specification for GeoEnviron database(r). 

Scriptet forudsætter initielt følgende parametre, som dog kan tilpasses:

• At der på SQL serveren er oprettet filstrukturen: c:\SQLAudit\DatabaseAudit\ 
• At hver log-fil max. fylder 200 MB 
• At der max. oprettes 1000 log-filer
• At GeoEnviron databasenavn starter med "geo_"
• At SQL bruger til script-kørsel er sysadmin

SQL Server Server Audit

Det andet script, SQLAuditServer.sql, kan anvendes til oprettelse af SQL Server Server Audit til GeoEnviron databasen - dvs. logning af SQL Server ændringer samt hvilke brugere, der foretager login (successful / failed). Det andet script opretter hhv. SQL Server Server Audit og Server Audit Specification for SQL Server, hvor GeoEnviron databasen er installeret. 

Scriptet forudsætter initielt følgende parametre - som dog kan tilpasses efter behov:

• At der på SQL serveren er oprettet filstrukturen: c:\SQLAudit\ServerAudit\ 
• At hver log-fil max. fylder 200 MB 
• At der max. oprettes 1000 log-filer
• At GeoEnviron databasenavn starter med "geo_"
• At SQL bruger til script-kørsel er sysadmin

Læsning af SQL Audit filer

Der er en system funktion i SQL Server, sys.fn_get_audit_file, som returnerer information fra en  audit fil oprettet af en server eller database audit. Denne funktion kræver CONTROL SERVER rettighed på SQL Serveren.

Her er en simpel T-SQL query til at læse alle audit filer fra en specific filplacering (for example ‘E:\SQLAudit\DatabaseAudit\’):

SELECT * FROM sys.fn_get_audit_file
('E:\SQLAudit\DatabaseAudit\*', null, null)
GO

For at læse alle audit filer med et specifikt navn (for eksempel fra en audit called 'GeoEnviron' fra en specifik filplacering 'E:\SQLAudit\DatabaseAudit\'):

SELECT * FROM sys.fn_get_audit_file
('E:\SQLAudit\DatabaseAudit\GeoEnviron_*.sqlaudit', null, null)
GO

For at læse al information fra en specifik audit file (for eksempel ‘E:\SQLAudit\DatabaseAudit\GeoEnviron_file_name.sqlaudit’):

SELECT * FROM sys.fn_get_audit_file
('E:\SQLAudit\DatabaseAudit\GeoEnviron_file_name.sqlaudit', null, null)
GO

Man kan også lave en forespørgsel, som kun returnerer den information man har behov for fra audit filerne. For eksempel:

SELECT
server_principal_name as bruger, [succeeded] as status,
CONVERT(datetime,
SWITCHOFFSET(CONVERT(datetimeoffset, event_time),
DATENAME(TzOffset, SYSDATETIMEOFFSET())))
AS
event_time from (SELECT
server_principal_name, [database_name],[event_time],[statement], [succeeded]
FROM sys.fn_get_audit_file
('E:\SQLAudit\ServerAudit\*', null, null)) x
order by event_time desc

GO