locked
Audit Active Directory Group RRS feed

  • Question

  • Hi,

    I need to audit an active directory group which has been added to my sql DB server.

    How is it possible???

    Saturday, July 2, 2016 11:40 AM

Answers

  • Hi ArashMasroor,

    In this case, you could use SQL audit. There are two levels of auditing: Server-level auditing is supported in all SQL Server editions, and database-level auditing is supported in Enterprise/Developer/Evaluation editions only. In this case, if your are using enterprise edition, I would suggest you to use database-level auditing to monitor activities on specified database by certain server principal(AD user group) by using the following steps:

    1. Create server principal for AD Group.
    2. Create an audit by right clicking on SSMS/Object Explorer/Security/Audits and select new audit. Note that an audit must be created before you create audit specifications.
    3. Clicking on SSMS/Object Explorer/Your Database/Security/Database Audit Specifications and select new database audit specification.
    4. Select your audit in the “Audit” dropdown list, then configure your audit action type, object name and principal name(which in this case is the server principal of the AD Group) .
    5. After configuration, right clicking on both audit and database audit specification and enable them. To view the audit log, right clicking on the audit and click on “View audit logs”.

    In addition, you could also do it via TSQL. For more information, please refer to CREATE AUDIT and CREATE DATABASE AUDIT SPECIFICATION.

    If you have any other questions, please let me know.

    Regards,
    Lin

    Monday, July 4, 2016 9:05 AM
  • CDC won't be helpful here, since it doesn't say *who* did the change.

    Server Audit might be the answer, assuming that the anonymization of the parameters for parameterized queries isn't a problem. Best way to find out is to do a proof-of-concept and see if the audit meets the organization's requirements. If not, then hunt further for some alternative technology/tool.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, July 4, 2016 11:28 AM
  • I don't think this will be reliable.  There may be a way to determine AD Members in a Group from SQL Server; but if these logins are members of other AD groups with access to the same SQL Server; and/or if they have their own AD login registered; then you will end up auditing all activity for the user; irrespective if they have been authenticated from within the AD Group you wish to Audit.

    Please click "Mark As Answer" if my post helped. Tony C.


    Monday, July 4, 2016 3:53 PM

All replies

  • Depends on what you mean by auditing. Do you need to track changes in membership in that group? Or anything else?

    For monitoring changes in a group you can either use a scheduled task and a script like this: https://gallery.technet.microsoft.com/Monitor-Active-Directory-4c4e04c7 or you can use Auditing and attach a task to the Event ID 5136 which would trigger every time an object in your AD gets modified.

    HTH


    Evgenij Smirnov

    msg services ag, Berlin -> http://www.msg-services.de
    my personal blog (mostly German) -> http://it-pro-berlin.de
    Windows Server User Group, Berlin -> http://www.winsvr-berlin.de
    Mark Minasi Technical Forum, reloaded -> http://newforum.minasi.com

    In theory, there is no difference between theory and practice. In practice, there is.

    Saturday, July 2, 2016 7:46 PM
  • I need to audit them in SQL Server.To see what changes on my Database is made by them.

    Sunday, July 3, 2016 3:38 AM
  • Hi Arash,

    Have you looked at Change Data Capture ?

    Please checkout this informative article and I hope, it should help you to resolve your purpose - https://technet.microsoft.com/en-us/magazine/2008.11.sql.aspx

    In case you need more granular report, you can try this SQL server auditing tool which helps to track every critical changes/access into real time and provides the insight report at granular level.

    Monday, July 4, 2016 6:39 AM
  • Hi ArashMasroor,

    In this case, you could use SQL audit. There are two levels of auditing: Server-level auditing is supported in all SQL Server editions, and database-level auditing is supported in Enterprise/Developer/Evaluation editions only. In this case, if your are using enterprise edition, I would suggest you to use database-level auditing to monitor activities on specified database by certain server principal(AD user group) by using the following steps:

    1. Create server principal for AD Group.
    2. Create an audit by right clicking on SSMS/Object Explorer/Security/Audits and select new audit. Note that an audit must be created before you create audit specifications.
    3. Clicking on SSMS/Object Explorer/Your Database/Security/Database Audit Specifications and select new database audit specification.
    4. Select your audit in the “Audit” dropdown list, then configure your audit action type, object name and principal name(which in this case is the server principal of the AD Group) .
    5. After configuration, right clicking on both audit and database audit specification and enable them. To view the audit log, right clicking on the audit and click on “View audit logs”.

    In addition, you could also do it via TSQL. For more information, please refer to CREATE AUDIT and CREATE DATABASE AUDIT SPECIFICATION.

    If you have any other questions, please let me know.

    Regards,
    Lin

    Monday, July 4, 2016 9:05 AM
  • CDC won't be helpful here, since it doesn't say *who* did the change.

    Server Audit might be the answer, assuming that the anonymization of the parameters for parameterized queries isn't a problem. Best way to find out is to do a proof-of-concept and see if the audit meets the organization's requirements. If not, then hunt further for some alternative technology/tool.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, July 4, 2016 11:28 AM
  • I don't think this will be reliable.  There may be a way to determine AD Members in a Group from SQL Server; but if these logins are members of other AD groups with access to the same SQL Server; and/or if they have their own AD login registered; then you will end up auditing all activity for the user; irrespective if they have been authenticated from within the AD Group you wish to Audit.

    Please click "Mark As Answer" if my post helped. Tony C.


    Monday, July 4, 2016 3:53 PM