Come abilitare l'Audit di SQL Server e rivedere il Log di Audit

L'audit di Microsoft SQL Server è fondamentale per identificare problemi di sicurezza e violazioni. Inoltre, l'audit di SQL Server è un requisito per la conformità a normative come PCI DSS e HIPAA.

Il primo passo è definire cosa sottoporre a revisione. Ad esempio, potresti revisionare gli accessi degli utenti, la configurazione del server, le modifiche allo schema e le modifiche ai dati di revisione. Successivamente, devi scegliere quali funzionalità di revisione della sicurezza utilizzare. Le funzionalità utili includono le seguenti:

• C2 Auditing
• Criteri Comuni di Conformità
• Audit degli accessi
• Audit di SQL Servering
• SQL Trace
• Extended Events
• Cattura dei cambiamenti dei dati
• Trigger DML, DDL e di accesso

Questo articolo è per gli amministratori di database (DBA) che stanno valutando l'uso dell'auditing C2, dei Common Compliance Criteria e dell'Auditing di SQL Server. Non prenderemo in considerazione strumenti di auditing di terze parti, sebbene possano essere di grande aiuto, specialmente per ambienti più grandi e in settori regolamentati.

Abilitazione dell'auditing C2 e della conformità ai Criteri Comuni

Se attualmente non stai eseguendo l'audit del tuo SQL Server, il punto di partenza più semplice è abilitare l'audit C2. L'audit C2 è uno standard accettato a livello internazionale che può essere attivato in SQL Server. Esso registra eventi come accessi degli utenti, procedure memorizzate e la creazione e rimozione di oggetti. Tuttavia, è tutto o niente — non puoi scegliere cosa viene auditato e può generare una grande quantità di dati. Inoltre, l'audit C2 è in modalità di manutenzione, quindi è probabile che verrà rimosso in una versione futura di SQL Server.

La conformità ai Common Criteria è uno standard più recente che sostituisce l'audit C2. È stato sviluppato dall'Unione Europea e può essere attivato nelle edizioni Enterprise e Datacenter di SQL Server 2008 R2 e successive. Tuttavia, può causare problemi di prestazioni se il tuo server non è adeguatamente configurato per gestire il sovraccarico aggiuntivo.

Ecco come abilitare l'auditing C2 in SQL Server 2017:

1. Apri SQL Server Management Studio.

2. Connettiti al motore del database per il quale desideri abilitare l'auditing C2. Nella finestra di dialogo Connetti al Server, assicurati che Server type sia impostato su Database Engine e poi clicca su Connect.

3. Nel pannello Esplora oggetti sulla sinistra, fare clic con il pulsante destro del mouse sull'istanza di SQL Server in alto e selezionare Properties dal menu.

4. Nella finestra Proprietà del server, fare clic su Security sotto Seleziona una pagina.

5. Nella pagina Sicurezza, è possibile configurare il monitoraggio degli accessi. Per impostazione predefinita, vengono registrati solo gli accessi non riusciti. In alternativa, è possibile verificare solo gli accessi riusciti, o sia gli accessi non riusciti che quelli riusciti.

Figura 1. Configurazione dell'auditing degli accessi

6. Seleziona Abilita tracciamento audit C2 in Options.

7. Se vuoi abilitare l'audit di conformità ai Criteri Comuni C2, seleziona Enable Common Criteria compliance.

La conformità ai Common Criteria (CC) è uno standard flessibile che può essere implementato con diversi Livelli di Garanzia di Valutazione (EAL), da 1 a 7. Livelli EAL più alti richiedono un processo di verifica più esigente. Quando si attiva l'opzione Enable Common Criteria compliance in SQL Server, si sta abilitando la conformità CC EAL1. È possibile configurare manualmente SQL Server per EAL4+.

L'abilitazione della conformità CC modifica il comportamento di SQL Server. Ad esempio, i permessi di DENY a livello di tabella avranno la precedenza sui GRANT a livello di colonna, e verranno auditati sia i login riusciti che quelli falliti. Inoltre, viene abilitata la Protezione delle Informazioni Residue (RIP), che sovrascrive le allocazioni di memoria con un pattern di bit prima che siano utilizzate da una nuova risorsa.

8. Fare clic su OK.

9. In base alle opzioni selezionate, potresti ricevere un prompt per riavviare SQL Server. Se ricevi questo messaggio, fai clic su OK nella finestra di dialogo di avviso. Se hai abilitato la conformità ai criteri comuni C2, riavvia il server. Altrimenti, fai clic con il tasto destro sull'istanza di SQL Server in Esplora oggetti di nuovo e seleziona Restart dal menu. Nella finestra di dialogo di avviso, fai clic su Yes per confermare che vuoi riavviare SQL Server.

Abilitazione di SQL Server Audit

L'audit di SQL Server può essere abilitato al posto dell'audit C2; è possibile anche scegliere di abilitare entrambi. Gli oggetti Audit di SQL Server possono essere configurati per raccogliere eventi a livello di server o a livello di database di SQL Server.

Crea oggetto di controllo del server

Creiamo un oggetto di audit a livello di server per SQL Server:

1. Nel pannello Esplora oggetti a sinistra, espandi Security.

2. Fare clic con il pulsante destro del mouse su Audits e selezionare Nuovo Audit… dal menu. Questo creerà un nuovo oggetto SQL Server Audit per l'auditing a livello di server.

3. Nella finestra Crea Audit, assegna un nome alle impostazioni di audit nella sezione Audit name

4. Specificare cosa dovrebbe accadere in caso di fallimento dell'auditing di SQL Server utilizzando l'opzione On Audit Log Failure. È possibile scegliere Continue o decidere di spegnere il server o interrompere le operazioni del database che sono sottoposte ad auditing. Se si seleziona Fail operation, le operazioni del database che non sono sottoposte ad auditing continueranno a funzionare.

Figura 2. Creazione di un oggetto di audit a livello di server SQL Server

5. Nel menu a discesa Audit destination, puoi scegliere di scrivere il tracciato di audit SQL in un file o di registrare gli eventi di audit nel registro di sicurezza di Windows o nel registro eventi dell'applicazione. Se scegli un file, devi specificare un percorso per il file.

Si noti che se si desidera scrivere nel registro eventi di sicurezza di Windows, sarà necessario concedere il permesso a SQL Server. Per semplicità, selezionare il registro eventi Applicazione. Inoltre, è possibile includere un filtro come parte dell'oggetto di audit per fornire un insieme ristretto di risultati; i filtri devono essere scritti in Transact-SQL (T-SQL).

6. Fare clic su OK.

7. Ora troverai la nuova configurazione di audit in Esplora oggetti sotto Audits. Fai clic con il tasto destro sulla nuova configurazione di audit e seleziona Enable Audit dal menu.

8. Fare clic su Close nella finestra di dialogo Abilita Audit.

Crea oggetto di audit del database

Per creare un oggetto di audit SQL Server per l'audit a livello di database, il processo è leggermente diverso e devi creare prima almeno un oggetto di audit a livello di server.

1. Espandi Databases in Esplora oggetti ed espandi il database su cui desideri configurare l'auditing.

2. Espandi la cartella Security, fai clic con il tasto destro su Database Audit Specifications e seleziona New Database Audit Specification… dal menu.

Figura 3. Creazione di una specifica di controllo del server per l'audit a livello di database

3. Nella finestra Proprietà sotto Azioni, utilizza i menu a discesa per configurare uno o più tipi di azione di audit, selezionando le dichiarazioni che desideri sottoporre a audit (come DELETE o INSERT), la classe di oggetto su cui viene eseguita l'azione, e così via.

4. Quando hai finito, clicca su OK e poi attiva l'oggetto di controllo facendo clic con il tasto destro e selezionando Enable Database Audit Specification.

Visualizzazione dei log di audit di SQL Server

I log di audit C2 Audit SQL Server sono memorizzati nella directory dati predefinita dell'istanza di SQL Server. Ogni file di log può essere di massimo 200 megabyte. Un nuovo file viene creato automaticamente quando si raggiunge il limite.

Una soluzione nativa che si consiglia per visualizzare i log di controllo di SQL Server si chiama Log File Viewer. Per utilizzarla, seguire i seguenti passaggi:

1. In SQL Server Management Studio, nel pannello Object Explorer, espandi Security e

2. Fare clic con il pulsante destro del mouse sull'oggetto di controllo che si desidera visualizzare e selezionare View Audit Logs dal menu.

3. Nel Log File Viewer, i log verranno visualizzati sul lato destro. Indipendentemente dal fatto che i log siano scritti su un file o nel Registro eventi di Windows, Log File Viewer mostrerà i log.

4. In cima al Log File Viewer, puoi cliccare su Filter per personalizzare quali voci di registro vengono visualizzate. I file di registro di SQL Server sono salvati in formato .sqlaudit e non sono leggibili, quindi Log File Explorer ti permette di cliccare su Export per salvare i log in un formato di file .log delimitato da virgole.

Figura 4. Revisione del logging di audit di SQL Server nel Log File Viewer

FAQ

Come verificare se l'audit di SQL Server è abilitato?

Per verificare se l'audit di SQL Server è abilitato, interrogare la vista di gestione dinamica sys.dm_server_audit_status o controllare la cartella Sicurezza di SQL Server Management Studio (SSMS). In SSMS, espandere Sicurezza > Audit per visualizzare tutti gli audit configurati e il loro stato attuale – gli audit abilitati mostreranno un'icona verde, mentre quelli disabilitati appariranno con un'icona rossa. È possibile anche eseguire questa query per controllare in modo programmatico lo stato dell'audit:

      SELECT name, is_state_enabled FROM sys.server_audits
      

Ricorda che sia l'audit del server che la specifica di audit del database devono essere abilitati per una copertura completa dell'audit. Data Security che inizia con l'identità richiede una visibilità completa su chi accede a quali dati, e l'audit di SQL Server fornisce quella base quando configurato e verificato correttamente.

Perché il mio file di audit di SQL Server sta diventando così grande?

La crescita eccessiva dei file di audit si verifica tipicamente quando si monitorano troppi eventi o non si sono configurate correttamente le impostazioni di gestione dei file. I colpevoli più comuni sono l'abilitazione di TUTTI i gruppi di azioni di audit, l'audit delle istruzioni SELECT su tabelle ad alto traffico o l'impostazione di una crescita illimitata dei file senza rotazione. Per controllare la crescita, concentrati sull'audit solo degli eventi di cui hai effettivamente bisogno per la conformità – tipicamente LOGIN_CHANGE_PASSWORD_GROUP, DATABASE_PERMISSION_CHANGE_GROUP, e operazioni DML specifiche su tabelle sensibili. Configura limiti massimi di dimensione dei file e abilita il rollover dei file con le opzioni MAXSIZE e MAX_ROLLOVER_FILES. Per ambienti ad alto volume, considera l'uso del target APPLICATION_LOG invece del target FILE, o implementa il filtraggio dell'audit con clausole WHERE per ridurre la cattura di eventi non necessari. Un audit intelligente significa tracciare ciò che conta senza annegare nel rumore dei dati.

Come risolvere il problema quando l'audit di SQL Server non si avvia?

Quando l'audit di SQL Server non riesce a partire, il problema è solitamente legato ai permessi dei file, all'accessibilità del percorso o a conflitti di configurazione. Prima di tutto, verificare che l'account del servizio SQL Server abbia i permessi di scrittura sulla directory del file di audit – questa è la causa più comune dei fallimenti all'avvio. Controllare il log degli errori di SQL Server per messaggi di errore specifici, che di solito forniscono indicazioni chiare sul problema. Assicurarsi che la directory di destinazione esista ed sia accessibile dall'istanza di SQL Server, specialmente in ambienti clusterizzati dove i percorsi di storage condivisi devono essere validi su tutti i nodi. Se si utilizza il Log delle Applicazioni di Windows come destinazione, verificare che l'account del servizio abbia i permessi appropriati di scrittura sul log degli eventi. Errori di configurazione come nomi di audit duplicati o percorsi di file non validi impediranno anche l'avvio. La chiave è la risoluzione dei problemi metodica: controllare prima i permessi, poi i percorsi, poi la sintassi della configurazione. Netwrix semplifica questa complessità fornendo una gestione centralizzata dell'audit che elimina queste insidie comuni.

Qual è l'impatto sulla prestazione dell'auditing di SQL Server?

L'audit di SQL Server ha un impatto minimo sulle prestazioni se configurato correttamente, aggiungendo tipicamente un sovraccarico del 2-5% nella maggior parte degli ambienti. L'impatto effettivo dipende da tre fattori chiave: quali eventi si auditano, con quale frequenza si verificano e le prestazioni del sottosistema di memorizzazione. Audire operazioni ad alta frequenza come le istruzioni SELECT su sistemi OLTP intensivi creerà più sovraccarico rispetto al concentrarsi su eventi rilevanti per la sicurezza come accessi, cambi di permessi e operazioni DML su tabelle sensibili. I target di audit asincroni (l'impostazione predefinita) offrono prestazioni migliori rispetto alle opzioni sincrone ma con una registrazione degli eventi leggermente ritardata. Per minimizzare l'impatto, utilizzare il filtraggio dell'audit con clausole WHERE, evitare di audire operazioni di sistema non necessarie e assicurarsi che l'archiviazione dei file di audit abbia una capacità I/O adeguata. Gli Extended Events generalmente hanno un sovraccarico inferiore rispetto a SQL Server Audit per scenari ad alto volume, ma SQL Server Audit offre funzionalità di conformità superiori e una gestione più semplice. Un design di audit intelligente si concentra sul valore della sicurezza più che sulla registrazione completa – si desidera una visibilità che protegga senza paralizzare le prestazioni.

Audit di SQL Server vs SQL Trace: quale dovrei usare?

SQL Server Audit è la scelta moderna per nuove implementazioni, mentre SQL Trace è deprecato e dovrebbe essere evitato per nuovi progetti. SQL Server Audit offre una migliore sicurezza, prestazioni e capacità di gestione rispetto alla funzionalità legacy di SQL Trace. A differenza di SQL Trace, gli eventi di SQL Server Audit non possono essere modificati o eliminati dagli utenti (inclusi gli amministratori di sistema), garantendo l'integrità dell'audit per i requisiti di conformità. Il framework di audit offre elaborazione asincrona per prestazioni migliori, capacità di filtraggio integrate e integrazione con il Registro eventi di sicurezza di Windows. SQL Trace richiede una codifica manuale con stored procedure ed è stato segnato per la rimozione nelle future versioni di SQL Server. Extended Events è la sostituzione raccomandata per le capacità diagnostiche di SQL Trace, mentre SQL Server Audit si occupa del monitoraggio della sicurezza e della conformità. Se attualmente utilizzi SQL Trace per l'audit di sicurezza, migra immediatamente a SQL Server Audit – offre la traccia di audit a prova di manomissione che la vera sicurezza dei dati richiede. Le soluzioni Netwrix si basano su queste capacità di audit native per fornire visibilità centralizzata su tutto l'ambiente dei dati.