locked
Auditing SQL Server 2012 RRS feed

  • Question

  • 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.


    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.

    5. 3 state agencies are interested in auditing, not the vendor.




    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.

    Tuesday, January 20, 2015 9:45 PM

Answers

  • Hi brian,

    Form my point of view, SQL Server auditing should be based on your business requirements. As you said, the state agencies are interested in auditing, then it’s better to ask the state agencies that what actions they hope to audit.

    Besides, below are best practices for SQL Server auditing. For more details about SQL Server auditing, please review this article .
    • Write audit logs to a centralized location
    • To facilitate processing of the audited data, load the logs into a database
    • Use a file as a target for optimal performance
    • Use targeted auditing to minimize the collected data and better performance
    • When writing to the Windows logs, ensure that the roll-over policy of the Windows Logs, coincides with that of your audit strategy


    Thanks,
    Lydia Zhang

    If you have any feedback on our support, please click here.


    Lydia Zhang
    TechNet Community Support


    • Marked as answer by brian manning Wednesday, January 21, 2015 9:49 PM
    Wednesday, January 21, 2015 3:02 AM

All replies

  • Hi brian,

    Form my point of view, SQL Server auditing should be based on your business requirements. As you said, the state agencies are interested in auditing, then it’s better to ask the state agencies that what actions they hope to audit.

    Besides, below are best practices for SQL Server auditing. For more details about SQL Server auditing, please review this article .
    • Write audit logs to a centralized location
    • To facilitate processing of the audited data, load the logs into a database
    • Use a file as a target for optimal performance
    • Use targeted auditing to minimize the collected data and better performance
    • When writing to the Windows logs, ensure that the roll-over policy of the Windows Logs, coincides with that of your audit strategy


    Thanks,
    Lydia Zhang

    If you have any feedback on our support, please click here.


    Lydia Zhang
    TechNet Community Support


    • Marked as answer by brian manning Wednesday, January 21, 2015 9:49 PM
    Wednesday, January 21, 2015 3:02 AM
  • Hello Lydia,

    Thanks for your response. I understand auditing is based on business requirements. This is a new project where state agencies share data. We have no business requirements between the state agencies. My agencie is doing all the work and hosting Data Warehouse. We are trying to develop requirements. So I am asking from your experience what are the best audit actions to use without creating huge logs and filling up drive space. I just want your opinion.

    thanks

    Brian


    SQL

    Wednesday, January 21, 2015 9:48 PM