Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Auditing SQL Server 2012 Database

Author  Topic 

manninb20
Starting Member

4 Posts

Posted - 2015-01-15 : 13:52:02
I work for the State of California. I am working on a project setting up a Data Warehouse sharing data between 3 state agencies. The Data Warehouse will be encrypted. I would like to know what are the best SQL Server audit actions groups to use. I want to balance auditing without creating too much auditing that can affect server performance and filling up drive space. I would like your anyone's suggestions. Below is what I have come up with for auditing. If this is too much auditing please let me know. If any questions please let me know.


Server Audit Specifications (Captures events at instance level):
• FAILED_LOGIN_GROUP
• SUCCESSFUL_LOGIN_GROUP
• AUDIT_CHANGE_GROUP
• SERVER_ROLE_MEMBERSHIP_CHANGE_GROUP
• SERVER_PERMISSION_CHANGE_GROUP
• DATABASE_OBJECT_ACCESS_GROUP
• DATABASE_CHANGE_GROUP

Database Audit Specifications (Audit events at database level):
• DATABASE_OBJECT_CHANGE_GROUP
• DATABASE_PERMISSION_CHANGE_GROUP
• DATABASE_PRINCIPAL_CHANGE_GROUP
• DATABASE_ROLE_MEMBER_CHANGE_GROUP
• SCHEMA_OBJECT_CHANGE_GROUP
• SCHEMA_OBJECT_ACCESS_GROUP


RRACE Database Encryption:
• Encrypted At Rest(Self Encrypted SAN Storage)


RRACE Audit Logs:
• Audit Logs will be stored to a network location off of the RRACE Database Server.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2015-01-16 : 14:59:50
The OBJECT_ACCESS events will generate a HUGE amount of data. The SUCCESSFUL_LOGIN_GROUP will also generate a fair bit of data.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.
Go to Top of Page

manninb20
Starting Member

4 Posts

Posted - 2015-01-20 : 15:06:19
Thanks. Any other audits you would remove
Go to Top of Page

manninb20
Starting Member

4 Posts

Posted - 2015-01-20 : 16:07:32
OK. I am thinking I am doing an overkill on auditing. I modified changes to my auditing list below. Do you think this will be sufficient for my project? Below is more info for your feedback. Please recommend if more audits should be removed. If any more questions please let me know.

1. This is a pilot project for 3 months. We will award a contract to a vendor to run Predictive Data Analytics from an application server.
2. I am doing a one-time setup for Data Warehouse hosting data from 3 state agencies. This Data Warehouse will be hosted within my state agency internal network. Other agencies will not have access to Data Warehouse.
3. Vendor will run Predictive Data Analytics for 3 months against the Data Warehouse and storing results in another database with write and read access.
4. Vendor will only have read only access to Data Warehouse.


Server Audit Specifications (Captures events at instance level):
• FAILED_LOGIN_GROUP
• SUCCESSFUL_LOGIN_GROUP
• AUDIT_CHANGE_GROUP
• SERVER_ROLE_MEMBERSHIP_CHANGE_GROUP
• SERVER_PERMISSION_CHANGE_GROUP
• DATABASE_CHANGE_GROUP
• LOGOUT_GROUP

Database Audit Specifications (Audit events at database level):
• DATABASE_OBJECT_CHANGE_GROUP
• DATABASE_PERMISSION_CHANGE_GROUP
• DATABASE_PRINCIPAL_CHANGE_GROUP
• DATABASE_ROLE_MEMBER_CHANGE_GROUP
• SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP
• DATABASE_PRINCIPAL_IMPERSONATION_GROUP


RRACE Database Encryption:
• Encrypted At Rest(Self Encrypted SAN Storage)


RRACE Audit Logs:
• Audit Logs will be stored to a network location off of the RRACE Database Server.
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2015-01-21 : 12:10:03
We use two types of audits. We use SQL Server's Change Tracking with the DDL_EVENT. That gets us permission changes, schema changes and new user changes, etc. I've found this to be very useful.

Logins are harder. You need to decide if you want pooled and non-pooled logins. We only wanted new logins and not the reuse of a pooled login. For that we created a filtered trace and then imported those.



=================================================
Creating tomorrow's legacy systems today. One crisis at a time.
Go to Top of Page
   

- Advertisement -