SERVER LEVEL AUDIT IN MS SQL SERVER DBA

 

Server level Auditing in MS SQL Server:

• Audit of an instance of SQL Server or SQL Server database involves tracking and logging events that occur on the system.
• The SQL Server Audit object collects a single server instance or on the basis of equity and action groups to monitor.
• Verification is the SQL Server instance. You can have multiple audits per SQL Server instance.
• The specification of objects of verification server describes what to check. As its name suggests, this article focuses on the actions of the server instance.
• An audit server specification is associated with a server audit to design audit data is written.
• There is a one-to-one relationship between the specification of server object auditing and purpose of checking the server.
• A key element of any data security strategy is the ability to track who has accessed or attempted to access your data.
• This provides the ability to detect unauthorized access attempts or, where appropriate, to replenish stock malicious insiders misused their legitimate access.
• In addition, a rich and robust tracking capability can monitor sensitive configuration changes made by administrators.

       Audit SQL Server is designed with the following main objectives:

• Security - The audit function, and its objects are to be really sure.
• Performance - Performance impact should be minimized.
• Management - The audit function should be easy to manage.
• DiscoverAbility - Audit focuses on issues should be easy to answer.

permissions

Users with ALTER ANY DATABASE AUDIT permission can to create basic audit specification data and link them to any audit.

After an audit specification database is created, it can be accessed by managers with the control server, ALTER DATABASE all verification authorizations, or sysadmin account.

lenova

Using Transact-SQL:

           USE master ; 

           GO 

                Create the server audit.  

                CREATE SERVER AUDIT auditname 

                TO FILE ( FILEPATH ='path' )

            [WITH (QUEUE_DELAY = 0, ON_FAILURE = SHUTDOWN)] ;  

 Enable the server audit.  

       ALTER SERVER AUDIT auditname  

       WITH (STATE = ON) ; 

Alter the server level audit:

       Transact-SQL:

       ALTER SERVER AUDIT auditname

       TO APPLICATION_LOG

       WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)

• This statement creates an audit specification of the server to monitor backup / restore events, SQL Server service starts and stops, and changes in the composition of the server roles.

Transact-SQL:

  CREATE SERVER AUDIT auditname

   FOR SERVER AUDIT PCI_Audit

  ADD (BACKUP_RESTORE_GROUP),

  ADD (SERVER_STATE_CHANGE_GROUP),

  ADD (SERVER_ROLE_MEMBERSHIP_CHANGE_GROUP)

  WITH (STATE = OFF)

• Audit server specifications can be reassigned to a different server audit after their creation by using the ALTER SERVER AUDIT SPECIFICATION.
• This statement also provides the list of action groups checked change and the state of being switched between on (ON) and off (OFF).

The following example adds an action group for the specification created in the previous statement.

Transact-SQL:

  ALTER SERVER AUDIT auditname

  ADD (SERVER_PRINCIPAL_CHANGE_GROUP)

The following statement reads all audit files for a specific audit.

Transact-SQL:

SELECT * FROM auditname(

'D:\Audits\MyAudit-_C26128D1-F97B-4B82-9E47-B6A296045B05_*.sqlaudit',

default, default)

Configuring server level auditing in GUI method Video: