February 14, 2012 at 10:34 am
HI ALL,
I'm working on writing standards for SQL Server 2008 login audit there arenearly 30 different settings. i'm not sure about three of the settings under Administrator Activity (listed below).
The default setting is for SQL Server 2008 to monitor:
•Changes to a user’s security profile and attributes
•Changes to a system or application interface security profile and attributes
•Successful/unsuccessful access to user data
What does the “User’s security profile and attributes” involve? Presumably this would include the user’s name, phone number, other contact information, and information about the user’s location and role. For Active Directory the user profile would only include the user’s account name and password, and everything else is stored elsewhere.
Likewise, what is contained in the “system or application interface security profile and attributes”?
Finally, what does “user data” refer to in this context? If this refers to access to sensitive information in database tables, we would want to monitor change attempts.
We are implementing a SIEM solution to forward all critical assets logs. We only want to send the interesting logs so it want be resource intensive of SIEM hardware.
What are best practices?
any assistance would be helpful.
February 14, 2012 at 11:08 am
I think these audit standards are likely to bog your server down and generate large amounts of non-useful data..
With that said.. I normally specify a certain number of audits.
The first is a login/out/fail audit, where all attempted connections are logged. This covers 3 audit groups SUCCESSFUL_LOGIN_GROUP, FAILED_LOGIN_GROUP, and LOGOUT_GROUP. On a fairly busy server this generates 1.5-2.5GB of audit data a day.
The second is a server operations group capturing nearly everything else, including:
DATABASE_ROLE_MEMBER_CHANGE_GROUP
SERVER_ROLE_MEMBER_CHANGE_GROUP
BACKUP_RESTORE_GROUP
AUDIT_CHANGE_GROUP
DBCC_GROUP
DATABASE_PERMISSION_CHANGE_GROUP
DATABASE_OBJECT_PERMISSION_CHANGE_GROUP
SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP
SERVER_OBJECT_PERMISSION_CHANGE_GROUP
SERVER_PERMISSION_CHANGE_GROUP
DATABASE_PRINCIPAL_IMPERSONATION_GROUP
SERVER_PRINCIPAL_IMPERSONATION_GROUP
DATABASE_CHANGE_GROUP
DATABASE_OBJECT_CHANGE_GROUP
DATABASE_PRINCIPAL_CHANGE_GROUP
SCHEMA_OBJECT_CHANGE_GROUP
SERVER_OBJECT_CHANGE_GROUP
SERVER_PRINCIPAL_CHANGE_GROUP
DATABASE_OPERATION_GROUP
APPLICATION_ROLE_CHANGE_PASSWORD_GROUP
LOGIN_CHANGE_PASSWORD_GROUP
SERVER_STATE_CHANGE_GROUP
DATABASE_OWNERSHIP_CHANGE_GROUP
DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP
SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP
SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP
TRACE_CHANGE_GROUP
This generates 2-400MB a week..
The third is a sever operations audit but is limited to the SERVER_OPARTIONS_GROUP, this is broken out because it generates more data and includes things like VIEW SERVER STATE which if you have monitoring software and some other things will generate a fair amount of traffic. This generates about 1-2GB a week.
Of the 35 server groups in R2, this leaves only 4, DATABASE_OBJECT_ACCESS_GROUP, SCHEMA_OBJECT_ACCESS_GROUP, BROKER_LOGIN_GROUP, and DATABASE_MIRRORING_LOGIN_GROUP. Of these if you aren't using broker or DB mirrorin you can probably leave them off, if you are, I might consider adding them to the login audit..
The other 2, DATABASE_OBJECT_ACCESS_GROUP, SCHEMA_OBJECT_ACCESS_GROUP are capable of generating a LOT (let me emphasize that, LOT!) of audit data. And in most ways are equivalent to a statement trace. I wouldn't recommend having these groups audited for long periods of time.
Based on these numbers about 10GB of raw audit data a week.
I have been in many meetings where when asked what they wanted audited the answer was everything! The question I like to counter with is "are you willing to have the system a lot slower to have universal auditing?" And I haven't had a case where they said yes. Instead we got them onto the path of what they really needed to audit and where. Some tables/objects require more detailed auditing due to their nature, we should focus on them. We can get a lot of usefull change data from the audits I have specified above but the actual statements changing a row of data in a user table is not typically one of them. But you can.
I would also suggest policy base management as well. Such as policies to verify that the audits/audit specs are enabled.
As a side point, what are you going to DO with the audit data, storing it doesn't get you much Are you going to analyze it?
CEWII
February 14, 2012 at 12:34 pm
The logs will be forwarded to SIEM solution to be analyzed.
There's 30 different settings to log Administrator activity.
I'm not really sure whether these settings would be useful to monitor.
What does the “User’s security profile and attributes” involve?
what is contained in the “system or application interface security profile and attributes”?
what does “user data” refer to in this context?
Do you recommend enable these log settings or are they too resource intensive?
February 14, 2012 at 12:56 pm
Those descriptions don't make much sense but.
What does the “User’s security profile and attributes” involve?
I'm guessing login activity and security changes related to it.
what is contained in the “system or application interface security profile and attributes”?
I see this real close to the one above.. but a lot depends on how security is handled in this app so it is really unclear.
what does “user data” refer to in this context?
I can only guess it is the data in the tables. User is a highly subjective definition.
Do you recommend enable these log settings or are they too resource intensive?
I would say that auditing of everything but DATABASE_OBJECT_ACCESS_GROUP and SCHEMA_OBJECT_ACCESS_GROUP can probably be done without TOO much of a hit. Those two in particular should rarely be run for a long period, unless you are running a HIGHLY restricted server. I would push back on any vendor requesting this. Logging access to certain objects perhaps but not all access. Perfect example, fairly busy server, those two groups generated 107MB of data in less than two minutes.
The logs will be forwarded to SIEM solution to be analyzed.
This is in-house? What do you expect to get from the analysis?
CEWII
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply