January 15, 2015 at 12:01 pm
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.
January 15, 2015 at 1:18 pm
I don't see how we can be of any help without knowing what your auditing requirements actually are. That has to be what drives your decision tree, right?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 20, 2015 at 1:57 pm
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply