{"id":2205,"date":"2016-04-18T00:00:00","date_gmt":"2016-04-18T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-server-security-audit-basics\/"},"modified":"2021-08-24T13:39:32","modified_gmt":"2021-08-24T13:39:32","slug":"sql-server-security-audit-basics","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/sql-server-security-audit-basics\/","title":{"rendered":"SQL Server Security Audit Basics"},"content":{"rendered":"
\n

The hardest part of Security Auditing is to define what should be audited and how. The security breaches around a SQL Server instances may occur in several areas: from SSIS packages saved on disk, to system users and administrators who can read and even modify data. There a lot of different attack vectors to monitor. A good place to start is to determine the legislative requirements for your installation, which will depend on the nature of the data. Generally, there is a requirement to track and log events that occur on the Database Engine in sufficient detail to aid a forensic analysis of any data-breach that occurs.<\/p>\n

The next challenge, after defining what should be audited, is to find a way to take a snapshot of the system’s security context at the time of the start of the auditing. The idea is that we must know what the settings are at the beginning of the auditing process, so we can monitor deviations and send alerts based on them.<\/p>\n

Finally, another challenge is to choose a way to deal with the large volume of data generated by the security auditing. The data can be stored in logs (SQL Server instance logs, Operating system logs), it can also be stored in log files on disk. Ideally, the data can be stored in ring buffers and consumed by external components and so on, but all this needs to be planned around the audit retention interval that is required.<\/p>\n

The work of setting up an audit log has been made much easier by Microsoft’s introduction of SQL Audit functionality. It has been available in SQL Server since SQL2008 and it was slightly improved in SQL2012. In this article first show how to set up the current security context, and then we’ll show you how to set up SQL Audit and apply it to all the databases in an instance. <\/p>\n

How to take a snapshot of the current security context<\/h2>\n

First, we will create a database called SQLAudit <\/strong>and a table in it called AuditDBLogin<\/strong>. <\/p>\n

IF OBJECT_ID('dbo.AuditDBLogin', 'U') IS NULL\n\nCREATE TABLE [dbo].[AuditDBLogin](\n\t[ServerName] [NVARCHAR](128) NULL,\n\t[LoginName] [sysname] NOT NULL,\n\t[LoginType] [VARCHAR](13) NOT NULL,\n\t[DatabaseName] [NVARCHAR](128) NULL,\n\t[SelectAccess] [INT] NULL,\n\t[InsertAccess] [INT] NULL,\n\t[UpdateAccess] [INT] NULL,\n\t[DeleteAccess] [INT] NULL,\n\t[DBOAccess] [INT] NULL,\n\t[SysadminAccess] [INT] NULL,\n\t[AuditDate] [DATETIME] NOT NULL DEFAULT ( GETDATE() )\n) \n<\/pre>\n
Then we will run the following script to get the database logins and all permissions for the databases.  It will need to run every database you need to audit on the instance that we are interested in auditing:<\/p>\n
INSERT INTO SQLAudit.dbo.AuditDBLogin\n ( [ServerName] ,\n  [LoginName] ,\n  [LoginType] ,\n  [DatabaseName] ,\n  [SelectAccess] ,\n  [InsertAccess] ,\n  [UpdateAccess] ,\n  [DeleteAccess] ,\n  [DBOAccess] ,\n  [SysadminAccess]\n )\n SELECT ServerName = @@SERVERNAME ,\n  LoginName = AccessSummary.LoginName ,\n  LoginType = CASE WHEN syslogins.isntuser = 1\n     THEN 'WINDOWS_LOGIN'\n     WHEN syslogins.isntgroup = 1\n     THEN 'WINDOWS_GROUP'\n     ELSE 'SQL_USER'\n     END ,\n  DatabaseName = DB_NAME() ,\n  SelectAccess = MAX(AccessSummary.SelectAccess) ,\n  InsertAccess = MAX(AccessSummary.InsertAccess) ,\n  UpdateAccess = MAX(AccessSummary.UpdateAccess) ,\n  DeleteAccess = MAX(AccessSummary.DeleteAccess) ,\n  DBOAccess = MAX(AccessSummary.DBOAccess) ,\n  SysadminAccess = MAX(AccessSummary.SysadminAccess)\n FROM (\n  \/* Get logins with permissions *\/ \n    SELECT LoginName = sysDatabasePrincipal.name ,\n        SelectAccess = CASE\n        WHEN permission_name = 'SELECT'\n        THEN 1\n        ELSE 0\n        END ,\n        InsertAccess = CASE\n        WHEN permission_name = 'INSERT'\n        THEN 1\n        ELSE 0\n        END ,\n        UpdateAccess = CASE\n        WHEN permission_name = 'UPDATE'\n        THEN 1\n        ELSE 0\n        END ,\n        DeleteAccess = CASE\n        WHEN permission_name = 'DELETE'\n        THEN 1\n        ELSE 0\n        END ,\n        DBOAccess = 0 ,\n        SysadminAccess = 0\n       FROM sys.database_permissions\n        AS sysDatabasePermission\n        INNER JOIN sys.database_principals\n        AS sysDatabasePrincipal ON sysDatabasePrincipal.principal_id = sysDatabasePermission.grantee_principal_id\n        INNER JOIN sys.server_principals\n        AS sysServerPrincipal ON sysServerPrincipal.sid = sysDatabasePrincipal.sid\n       WHERE sysDatabasePermission.class_desc = 'OBJECT_OR_COLUMN'\n        AND sysDatabasePrincipal.type_desc IN (\n        'WINDOWS_LOGIN',\n        'WINDOWS_GROUP',\n        'SQL_USER' )\n        AND sysServerPrincipal.is_disabled = 0\n   UNION ALL\n  \/* Get group members with permissions *\/\n   SELECT LoginName = sysDatabasePrincipalMember.name ,\n    SelectAccess = CASE WHEN permission_name = 'SELECT'\n      THEN 1\n      ELSE 0\n      END ,\n    InsertAccess = CASE WHEN permission_name = 'INSERT'\n      THEN 1\n      ELSE 0\n      END ,\n    UpdateAccess = CASE WHEN permission_name = 'UPDATE'\n      THEN 1\n      ELSE 0\n      END ,\n    DeleteAccess = CASE WHEN permission_name = 'DELETE'\n      THEN 1\n      ELSE 0\n      END ,\n    DBOAccess = 0 ,\n    SysadminAccess = 0\n   FROM sys.database_permissions AS sysDatabasePermission\n    INNER JOIN sys.database_principals AS sysDatabasePrincipalRole ON sysDatabasePrincipalRole.principal_id = sysDatabasePermission.grantee_principal_id\n    INNER JOIN sys.database_role_members AS sysDatabaseRoleMember ON sysDatabaseRoleMember.role_principal_id = sysDatabasePrincipalRole.principal_id\n    INNER JOIN sys.database_principals AS sysDatabasePrincipalMember ON sysDatabasePrincipalMember.principal_id = sysDatabaseRoleMember.member_principal_id\n    INNER JOIN sys.server_principals AS sysServerPrincipal ON sysServerPrincipal.sid = sysDatabasePrincipalMember.sid\n   WHERE sysDatabasePermission.class_desc = 'OBJECT_OR_COLUMN'\n    AND sysDatabasePrincipalRole.type_desc = 'DATABASE_ROLE'\n    AND sysDatabasePrincipalRole.name <> 'public'\n    AND sysDatabasePrincipalMember.type_desc IN (\n    'WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_USER' )\n    AND sysServerPrincipal.is_disabled = 0\n   UNION ALL\n  \/* Get users in db_owner, db_datareader and db_datawriter *\/\n   SELECT LoginName = sysServerPrincipal.name ,\n    SelectAccess = CASE WHEN sysDatabasePrincipalRole.name IN (\n       'db_owner',\n       'db_datareader' ) THEN 1\n      ELSE 0\n      END ,\n    InsertAccess = CASE WHEN sysDatabasePrincipalRole.name IN (\n       'db_owner',\n       'db_datawriter' ) THEN 1\n      ELSE 0\n      END ,\n    UpdateAccess = CASE WHEN sysDatabasePrincipalRole.name IN (\n       'db_owner',\n       'db_datawriter' ) THEN 1\n      ELSE 0\n      END ,\n    DeleteAccess = CASE WHEN sysDatabasePrincipalRole.name IN (\n       'db_owner',\n       'db_datawriter' ) THEN 1\n      ELSE 0\n      END ,\n    DBOAccess = CASE WHEN sysDatabasePrincipalRole.name = 'db_owner'\n      THEN 1\n      ELSE 0\n     END ,\n    SysadminAccess = 0\n   FROM sys.database_principals AS sysDatabasePrincipalRole\n    INNER JOIN sys.database_role_members AS sysDatabaseRoleMember ON sysDatabaseRoleMember.role_principal_id = sysDatabasePrincipalRole.principal_id\n    INNER JOIN sys.database_principals AS sysDatabasePrincipalMember ON sysDatabasePrincipalMember.principal_id = sysDatabaseRoleMember.member_principal_id\n    INNER JOIN sys.server_principals AS sysServerPrincipal ON sysServerPrincipal.sid = sysDatabasePrincipalMember.sid\n   WHERE sysDatabasePrincipalRole.name IN ( 'db_owner',\n        'db_datareader',\n        'db_datawriter' )\n    AND sysServerPrincipal.type_desc IN (\n    'WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_LOGIN' )\n    AND sysServerPrincipal.is_disabled = 0\n   UNION ALL\n  \/* Get users in sysadmin *\/\n   SELECT LoginName = sysServerPrincipalMember.name ,\n    SelectAccess = 1 ,\n    InsertAccess = 1 ,\n    UpdateAccess = 1 ,\n    DeleteAccess = 1 ,\n    DBOAccess = 0 ,\n    SysadminAccess = 1\n   FROM sys.server_principals AS sysServerPrincipalRole\n    INNER JOIN sys.server_role_members AS sysServerRoleMember ON sysServerRoleMember.role_principal_id = sysServerPrincipalRole.principal_id\n    INNER JOIN sys.server_principals AS sysServerPrincipalMember ON sysServerPrincipalMember.principal_id = sysServerRoleMember.member_principal_id\n   WHERE sysServerPrincipalMember.type_desc IN (\n    'WINDOWS_LOGIN', 'WINDOWS_GROUP', 'SQL_LOGIN' )\n    AND sysServerPrincipalMember.is_disabled = 0\n  ) AS AccessSummary\n  INNER JOIN master.dbo.syslogins AS syslogins ON syslogins.loginname = AccessSummary.LoginName\n WHERE AccessSummary.LoginName NOT IN ( 'NT SERVICE\\MSSQLSERVER',\n       'NT AUTHORITY\\SYSTEM',\n       'NT SERVICE\\SQLSERVERAGENT' )\n GROUP BY AccessSummary.LoginName ,\n  CASE WHEN syslogins.isntuser = 1 THEN 'WINDOWS_LOGIN'\n   WHEN syslogins.isntgroup = 1 THEN 'WINDOWS_GROUP'\n   ELSE 'SQL_USER'\n  END;\n<\/pre>\n
this routine will provide a security snapshot that can be used to verify and note who has what permissions. Then, the SQL audit itself keeps track of changes to the permissions; and reporting can be done if anything significant is altered. This routine is needed only as a reference to understand any changes that are subsequently shown by SQL Audit to the permission system. <\/p>\n
Audit everything – scripts for 2008 and 2012<\/h2>\n
Now we are ready to create the SQL Audit. First, we will start by creating an Audit for the Server itself: this is an object on a SQL Server instance level which is used to specify the way that the data is channelled and stored. The Server Audit may contain a Server audit specification (events on an instance level) and database audit specifications (events on a database level). The data can be stored either in binary file on disk, or written to the Windows Server application or security logs. <\/p>\n
Keep in mind that only Enterprise \/ Developer editions support database level audit specifications. <\/p>\n
Creating  the Server Audit:<\/h3>\n
-- drop server audit\n\nUSE [master]\nGO\nIF  EXISTS (SELECT * FROM sys.server_audits \n  WHERE name = N'MSSQL_Server_Audit')\nBEGIN\n ALTER SERVER AUDIT MSSQL_Server_Audit WITH (STATE = OFF) \n DROP SERVER AUDIT [MSSQL_Server_Audit]\nEND\nGO\n\n\n-- create server audit\n\nCREATE SERVER AUDIT [MSSQL_Server_Audit]\nTO FILE \n(\tFILEPATH = N'F:\\\\SQLAudit\\'\n\t,MAXSIZE = 200 MB\n\t,MAX_ROLLOVER_FILES = 2\n\t,RESERVE_DISK_SPACE = ON\n)\nWITH\n(\tQUEUE_DELAY = 1000\n\t,ON_FAILURE = CONTINUE\n)\nALTER SERVER AUDIT [MSSQL_Server_Audit] WITH (STATE = ON)\nGO\n<\/pre>\n
In this case I have chosen to save the data into a binary file on the disk system. There will be two files of 200Mb each, which will be rotated and overwritten as they reach their 200Mb size limit. <\/p>\n
Creating a Server Audit Specification<\/h3>\n
As I’ve already mentioned, the Server Audit Specification collects events on a SQL Server instance level. The script will check for the version of the SQL Server and will execute different sections based on whether it is SQL 2008 or above, since there are extra audit events in the SQL 2012 and up. <\/p>\n
-- drop server audit specification\n\nUSE [master]\nGO\n\nIF  EXISTS (SELECT * FROM sys.server_audit_specifications \n  WHERE name = N'MSSQL_Server_Specification')\nBEGIN\n ALTER SERVER AUDIT SPECIFICATION MSSQL_Server_Specification WITH (STATE = OFF) \n DROP SERVER AUDIT SPECIFICATION [MSSQL_Server_Specification]\nEND\nGO\n\n\n\n\/*\nThis section checks for the version of the SQL Server and creates the Server Audit Specification\n*\/\n\nIF (SELECT cast(left(cast(serverproperty('productversion') as varchar), 4) as decimal(5, 3))) < 11 \nBEGIN \n\t--PRINT 'SQL 2008%'\n\n\tCREATE SERVER AUDIT SPECIFICATION [MSSQL_Server_Specification]\n\tFOR SERVER AUDIT [MSSQL_Server_Audit]\n\tADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP ),\n\tADD (AUDIT_CHANGE_GROUP ),\n\tADD (BACKUP_RESTORE_GROUP ),\n\tADD (BROKER_LOGIN_GROUP ),\n\tADD (DATABASE_CHANGE_GROUP),\n\tADD (DATABASE_MIRRORING_LOGIN_GROUP ),\n\tADD (DATABASE_OBJECT_ACCESS_GROUP ),\n\tADD (DATABASE_OBJECT_CHANGE_GROUP ),\n\tADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP ),\n\tADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),\n\tADD (DATABASE_OPERATION_GROUP ),\n\tADD (DATABASE_OWNERSHIP_CHANGE_GROUP),\n\tADD (DATABASE_PERMISSION_CHANGE_GROUP ),\n\tADD (DATABASE_PRINCIPAL_CHANGE_GROUP),\n\tADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP ),\n\tADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),\n\tADD (DBCC_GROUP ),\n\tADD (FAILED_LOGIN_GROUP ),\n\tADD (FULLTEXT_GROUP ),\n\tADD (LOGIN_CHANGE_PASSWORD_GROUP),\n\tADD (LOGOUT_GROUP ),\n\tADD (SCHEMA_OBJECT_ACCESS_GROUP ),\n\tADD (SCHEMA_OBJECT_CHANGE_GROUP ),\n\tADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP ),\n\tADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),\n\tADD (SERVER_OBJECT_CHANGE_GROUP ),\n\tADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP ),\n\tADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),\n\tADD (SERVER_OPERATION_GROUP ),\n\tADD (SERVER_PERMISSION_CHANGE_GROUP ),\n\tADD (SERVER_PRINCIPAL_CHANGE_GROUP),\n\tADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP ),\n\tADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),\n\tADD (SERVER_STATE_CHANGE_GROUP),\n\tADD (SUCCESSFUL_LOGIN_GROUP ),\n\tADD (TRACE_CHANGE_GROUP )\n\tWITH (STATE = ON)\n\t\n\tEND \n\nELSE \n\n\tBEGIN \n\n\tCREATE SERVER AUDIT SPECIFICATION [MSSQL_Server_Specification]\n\tFOR SERVER AUDIT [MSSQL_Server_Audit]\n\tADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),\n\tADD (AUDIT_CHANGE_GROUP),\n\tADD (BACKUP_RESTORE_GROUP),\n\tADD (BROKER_LOGIN_GROUP),\n\tADD (DATABASE_CHANGE_GROUP ),\n\tADD (DATABASE_LOGOUT_GROUP ),\n\tADD (DATABASE_MIRRORING_LOGIN_GROUP),\n\tADD (DATABASE_OBJECT_ACCESS_GROUP),\n\tADD (DATABASE_OBJECT_CHANGE_GROUP),\n\tADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),\n\tADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP ),\n\tADD (DATABASE_OPERATION_GROUP),\n\tADD (DATABASE_OWNERSHIP_CHANGE_GROUP ),\n\tADD (DATABASE_PERMISSION_CHANGE_GROUP),\n\tADD (DATABASE_PRINCIPAL_CHANGE_GROUP ),\n\tADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP),\n\tADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP ),\n\tADD (DBCC_GROUP),\n\tADD (FAILED_DATABASE_AUTHENTICATION_GROUP),\n\tADD (FAILED_LOGIN_GROUP),\n\tADD (FULLTEXT_GROUP),\n\tADD (LOGIN_CHANGE_PASSWORD_GROUP ),\n\tADD (LOGOUT_GROUP),\n\tADD (SCHEMA_OBJECT_ACCESS_GROUP),\n\tADD (SCHEMA_OBJECT_CHANGE_GROUP),\n\tADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),\n\tADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP ),\n\tADD (SERVER_OBJECT_CHANGE_GROUP),\n\tADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),\n\tADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP ),\n\tADD (SERVER_OPERATION_GROUP),\n\tADD (SERVER_PERMISSION_CHANGE_GROUP),\n\tADD (SERVER_PRINCIPAL_CHANGE_GROUP ),\n\tADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP),\n\tADD (SERVER_ROLE_MEMBER_CHANGE_GROUP ),\n\tADD (SERVER_STATE_CHANGE_GROUP ),\n\tADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP),\n\tADD (SUCCESSFUL_LOGIN_GROUP),\n\tADD (TRACE_CHANGE_GROUP),\n\tADD (USER_CHANGE_PASSWORD_GROUP),\n\tADD (USER_DEFINED_AUDIT_GROUP)\n\tWITH (STATE = ON)\n\n\t\n    END\n<\/pre>\n
Creating the database audit specification<\/h3>\n
Sadly, the database audit specifications are only available in Enterprise \/ Developer editions. <\/p>\n
The script below iterates through all databases on the instance and creates the database audit specifications, including the model database, which will automatically bring the audit into any newly created databases. <\/p>\n
\/*\nThis section will iterate in every database and will create the database audit specifications \n\n*\/\n\nIF (SELECT cast(left(cast(serverproperty('productversion') as varchar), 4) as decimal(5, 3))) < 11 \nBEGIN \n\tEXEC sp_MSforeachdb 'USE ? \n\tIF  EXISTS (SELECT * FROM sys.database_audit_specifications WHERE name = N''DatabaseAuditSpecification'')\n\tBEGIN\n\t ALTER DATABASE AUDIT SPECIFICATION DatabaseAuditSpecification WITH (STATE = OFF)\n\t DROP DATABASE AUDIT SPECIFICATION DatabaseAuditSpecification\n\tEND\n\n\tCREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification]\n\tFOR SERVER AUDIT [MSSQL_Server_Audit]\n\tADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP ),\n\tADD (AUDIT_CHANGE_GROUP ),\n\tADD (BACKUP_RESTORE_GROUP ),\n\tADD (DATABASE_CHANGE_GROUP),\n\tADD (DATABASE_OBJECT_ACCESS_GROUP ),\n\tADD (DATABASE_OBJECT_CHANGE_GROUP ),\n\tADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP ),\n\tADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),\n\tADD (DATABASE_OPERATION_GROUP ),\n\tADD (DATABASE_OWNERSHIP_CHANGE_GROUP),\n\tADD (DATABASE_PERMISSION_CHANGE_GROUP ),\n\tADD (DATABASE_PRINCIPAL_CHANGE_GROUP),\n\tADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP ),\n\tADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),\n\tADD (DBCC_GROUP ),\n\tADD (SCHEMA_OBJECT_ACCESS_GROUP ),\n\tADD (SCHEMA_OBJECT_CHANGE_GROUP ),\n\tADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP ),\n\tADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP)\n\tWITH (STATE = ON)'\nEND\n\nELSE \n\nBEGIN\n\tEXEC sp_MSforeachdb 'USE ? \n\tIF  EXISTS (SELECT * FROM sys.database_audit_specifications WHERE name = N''DatabaseAuditSpecification'')\n\tBEGIN\n\t ALTER DATABASE AUDIT SPECIFICATION DatabaseAuditSpecification WITH (STATE = OFF)\n\t DROP DATABASE AUDIT SPECIFICATION DatabaseAuditSpecification\n\tEND\n\n\tCREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification]\n\tFOR SERVER AUDIT [MSSQL_Server_Audit]\n\tADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP ),\n\tADD (AUDIT_CHANGE_GROUP ),\n\tADD (BACKUP_RESTORE_GROUP ),\n\tADD (DATABASE_CHANGE_GROUP),\n\tADD (DATABASE_LOGOUT_GROUP),\n\tADD (DATABASE_OBJECT_ACCESS_GROUP ),\n\tADD (DATABASE_OBJECT_CHANGE_GROUP ),\n\tADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP ),\n\tADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),\n\tADD (DATABASE_OPERATION_GROUP ),\n\tADD (DATABASE_OWNERSHIP_CHANGE_GROUP),\n\tADD (DATABASE_PERMISSION_CHANGE_GROUP ),\n\tADD (DATABASE_PRINCIPAL_CHANGE_GROUP),\n\tADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP ),\n\tADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),\n\tADD (DBCC_GROUP ),\n\tADD (FAILED_DATABASE_AUTHENTICATION_GROUP ),\n\tADD (SCHEMA_OBJECT_ACCESS_GROUP ),\n\tADD (SCHEMA_OBJECT_CHANGE_GROUP ),\n\tADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP ),\n\tADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),\n\tADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP ),\n\tADD (USER_CHANGE_PASSWORD_GROUP ),\n\tADD (USER_DEFINED_AUDIT_GROUP )\n\tWITH (STATE = ON)'\nEND\n<\/pre>\n
     How to read the log<\/strong> <\/h2>\n
Now that we have setup the Server and Database level auditing, lets see what is in our audit file. <\/p>\n
We can read the binary file from SQL Server query window via the following script:<\/p>\n
SELECT  TOP 100 *\nFROM    sys.fn_get_audit_file('E:\\\\SQLAudit\\MSSQL_Server_Audit*.sqlaudit',\n                              DEFAULT, DEFAULT)\nORDER BY event_time DESC;\n<\/pre>\n
The outcome of the query will look similar to this:<\/p>\n
     \"2406-9cbb8c17-d099-4330-8954-b3aca7e4cee\"<\/p>\n
As we can see, there is a column called      action_id<\/strong>, which points to a specific action that was performed.  <\/p>\n
The actions can be listed by using the following query:<\/p>\n
SELECT TOP 10\n        action_id ,\n        name\nFROM    sys.dm_audit_actions;\n<\/pre>\n
The output will look like this:<\/p>\n
     \"2406-7a4965ba-3919-4f9d-ba09-ba5fd4506b5\"<\/p>\n
There are over 500 actions that are audited, and we can see how many actions are triggered in our Audit by running the following query:<\/p>\n
SELECT  COUNT(*) ActionsCount ,\n        f.action_id ,\n        a.name ,\n        a.class_desc\nFROM    sys.fn_get_audit_file('E:\\\\SQLAudit\\MSSQL_Server_Audit*.sqlaudit',\n                              DEFAULT, DEFAULT) f\n        JOIN sys.dm_audit_actions a ON a.action_id = f.action_id\nGROUP BY f.action_id ,\n        a.name ,\n        a.class_desc;\n<\/pre>\n
The result will look similar to this:<\/p>\n
     \"2406-d154b951-f9a9-4267-8e9e-037ca3fbec5\"<\/p>\n
     Automate and centralize<\/strong> <\/h2>\n
The technique and scripts above will audit everything – all the possible events on server and database level. Depending on the requirements, there can be different approaches on how to go from here. You have to be able to analyze and query the log.<\/p>\n
There is a built in function in SQL Server which will query the log: sys.fn_get_audit_file. There are a few issues to be aware of, however. In a busy production system there might be Gigabytes of log data created in a few minutes, which is a challenge in itself to process. <\/p>\n
Another caveat is that if the log is written to files, as suggested earlier in the article, a special attention needs to be paid to the file locking mechanisms during the log file reading and the rollover \/ deletion process. In other words, if there is a process which is using the sys.fn_get_audit_file function to read the audit files, then it will create a lock on the files and they wont be able to be deleted after rolling over, until the reading process is stopped. <\/p>\n
A workaround would be to use the Security log in Windows to write the audit events instead of the files on the file system. This way, the locking can be avoided and the rollover and log recycling is a bit easier. However, this creates another issue – some tweaking needs to be performed so the SQL Server account can have the access to write to the Security log, since it does not have the permission by default. <\/p>\n
This is done by giving access to the HKLM\\SYSTEM\\CurrentControlSet\\Services\\Eventlog\\Security key to the SQL Server account and also add the SQL Server account in the Generate Security Audits local group on the server. Of course, a SQL Server instance restart is needed. <\/p>\n
There are quite a few options when it comes to processing the collected data. I cannot say that SQL Server has anything ready “out of the box” which will help the user do a full audit investigation and reporting. There are several ways to go from here and the most popular ones include third party tools like ELK, Splunk or EXASOL to get the data and index it in order to do reporting and alerting on it. <\/p>\n
It really depends on what the goal of the audit is, how much data is generated and how much of it needs to be analyzed, and the answers to these questions will dictate the choice of tools down the road. <\/p>\n
ELK is a great open source tool, the name stands for Elastic Search + Logstash + Kibana. <\/p>\n
    \n
  •          The pros<\/strong> here are that it is a free, open source stack, which can handle the centralization, the indexing and the graphical display of realtime events from the Security Audit events.      <\/li>\n
  •          The cons <\/strong>here are that there is no support, a flexible Linux-based architecture needs to be setup in-house, and if there is no competence in-house, it will cost some time and funds.      <\/li>\n<\/ul>\n
    Splunk is another option to go for<\/p>\n
      \n
    •          The pros<\/strong> here are that it is fast, flexible, easily handles large volumes of data and supplies realtime reporting and search functionality. Splunk has a great support and staff which will setup everything for you.      <\/li>\n
    •          The cons <\/strong>here are that the solution costs a lot, and the bill comes by GB of data per day which is indexed and stored in Splunk. So, if you have a busy system, generating 50GB of data per day in security logs (I have personally seen much more than that!) you can imagine that it comes at a considerable cost.     <\/li>\n<\/ul>\n
      EXASOL is a very fast in-memory database which has a great capability to store and process large volumes of data.<\/p>\n
        \n
      •          The pros<\/strong> here are that it is fast, flexible, easily handles large volumes of data, it is fairly easy to setup and maintain     <\/li>\n
      •          The cons <\/strong>here are that the solution has costs associated to it, and it does not have its own reporting front-end. It is teamed up with Tableau, and reporting if fairly easy and fast, at a certain cost, of course.      <\/li>\n<\/ul>\n
        In short, there is no real ‘out of the box’ solution provided by Microsoft to help us process the security audit data, thus we have to find a way to bring in other third party tool, depending on the audit goal and the estimated capacity requirements. <\/p>\n
             Audit the audit<\/strong> <\/h2>\n
        A few words on the security of the security auditing: it is understood that sysadmins and db_owners can tamper with the Server Audit specifications and the Database audit specifications. One way to deal with this is to create another audit which audits the events related to the AUDIT_ CHANGE_GROUP.  <\/p>\n
        This can be done with the following script:<\/p>\n
        USE master;\n\nCREATE SERVER AUDIT SPECIFICATION MSSQL_Server_AdminAudit \nFOR SERVER AUDIT MSSQL_Server_Audit \nADD (AUDIT_CHANGE_GROUP) WITH (STATE = ON) ;\n<\/pre>\n
        The AUDIT_CHANGE_GROUP event is raised to alert on creation, modification or deletion of audit events. So in this case there will be two audits – one that audits the security events in the system and the other will be auditing if admins are tampering with the first audit.<\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
        SQL Server Server Audit has grown in functionality over the years but it can be tricky to maintain and use because it lacks centralization and analysis tools. It can do a fast and lightweight audit of many different activities including DML and DDL at both Instance and Database Levels – even the work of the DBAs. How do you check logins and  permissions? How do you script an enterprise-wide audit solution?  How can you hope to analyse the log data you get? Feodor gets you started.…<\/p>\n","protected":false},"author":221902,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[],"coauthors":[],"class_list":["post-2205","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2205","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221902"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=2205"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2205\/revisions"}],"predecessor-version":[{"id":92202,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2205\/revisions\/92202"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=2205"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=2205"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=2205"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=2205"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}