2

I need to log the activity of the 'sa' database account via Microsoft SQL Server 2012 so I can review the activity periodically and look for strange behaviour for audit purposes.

Unfortunately I'm completely new to SQL Server and searching Google and Stack Exchange turned up blank. Maybe I'm just looking for the wrong thing. Can anyone assist?

If this sort of logging is enabled, will the log have a tendency to spiral out of control?

4
  • 4
    What does "strange behavior" mean? You can look into auditing but keep in mind that sa can turn this off. A better solution: change the sa password and stop giving sysadmin to people you don't trust. :-) – Aaron Bertrand Mar 3 '16 at 13:46
  • 2
    In many shops the "sa" account is disabled completely and a different sysadmin account is created for regular administration for the individuals requiring it. No one outside the DBA role should be a sysadmin and even then, depending on the systems and roles, those accounts can be paired down if responsibilities are silo'd. – Steve Mangiameli Mar 3 '16 at 14:39
  • The main issue is that if we reset the 'sa' account password, the program that relies on the database has a catastrophic failure and requires a reinstall. We've been down that route already. Also, our auditors haven't given us any direction as to what 'strange behaviour' means, so we're just going to get some logs, look for patterns and see if anything ever falls outside those patterns. – Simkill Mar 3 '16 at 15:43
  • Why on earth are your auditors okay with an application using sa as the hard-coded login? I get that a few vendor apps legitimately need to use an account in the sysadmin role, but I don't know of a single one that has to use sa explicitly. – Aaron Bertrand Mar 4 '16 at 14:00
2

Your immediate answer predominantly resides here, the official SQL Server Audit documentation. The larger discussions of performance, requirements, actual security issues addressed, threats mitigated, and what-the-hell-is-wrong-with-your-auditors has been alluded to in the comments and isn't a great candidate for Stack Exchange.

For your scenario you could do something like this:

1) Create the Server Audit

USE [master]

GO

CREATE SERVER AUDIT [Audit-GeneralServerAudit]
TO FILE 
(   FILEPATH = N'\\NetworkStore\AuditLogs'
    ,MAXSIZE = 1024 MB
    ,MAX_FILES = 70
    ,RESERVE_DISK_SPACE = ON
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = SHUTDOWN
)
GO

ALTER SERVER AUDIT [Audit-GeneralServerAudit]
WITH (STATE = ON);

2) Define the Server Audit (modify events as needed)

CREATE SERVER AUDIT SPECIFICATION [ServerName - Audit]
FOR SERVER AUDIT [Audit-GeneralServerAudit]
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SERVER_STATE_CHANGE_GROUP),
ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (USER_CHANGE_PASSWORD_GROUP),
ADD(DBCC_GROUP)
WITH (STATE = ON)
GO

3) Create Database Audit(s) as needed. Create additional Server Audits to separate log files for easy use/permissions restrictions and to implement different retention/storage strategies

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification]
FOR SERVER AUDIT [Audit-DB Specific Audit]
ADD (DELETE ON DATABASE::[DBName] BY [public]),
ADD (SELECT ON DATABASE::[DBName] BY [public]),
ADD (UPDATE ON DATABASE::[DBName] BY [public])
WITH (STATE = ON)
GO

SQL Server automatically controls log rollover and space as per your file size and number of file definitions. Or the Windows Log settings control this if you elect for that design. Still a yes though - it absolutely can get out of control in terms of log size and over-writes (loss of audit data) if you don't setup the correct capacities.

If you're auditing a single user, why not audit other users and issues? You know, in-case someone else or another account is performing unwanted behavior.

Even basic log files are probably massive... as stated in comments, auditors need a clear definition of "unusual activity" or a clear methodology to automate finding it.

Intrusion Detection is unlikely to be caught by undefined anomaly detection. An actual audit strategy needs more than just the log files. I haven't seen much in the way of pre-built solutions for SQL Audit log files, but there are a lot of web, network, server log file solutions out there... maybe one is adaptable. Some example reports and setup guides here to get started on very basic reporting.

0

Plus 1 @Dave. Additionally, I rather like this gentleman's work on the subject of implementing SQL Auditing:

http://pietervanhove.azurewebsites.net/?p=3798

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.