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.
| 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_GROUPDatabase 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_GROUPRRACE 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. |
 |
|
|
manninb20
Starting Member
4 Posts |
Posted - 2015-01-20 : 15:06:19
|
| Thanks. Any other audits you would remove |
|
|
|
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_GROUPDatabase 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_GROUPRRACE 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-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. |
|
|
|
|
|
|
Auditing SQL Server 2012 Database