Post reply

SQL Server Auditing

  • June 28, 2017 at 8:48 am

    #331337

    We are installing SQL Server 2016 and are looking to use the Auditing functionality to have a proper audit trail of who (and when) is making changes to data within our databases.

    My question is whether we can capture username from the application rather than the hostname or the SQL login account. The users will be using a web application create in Entity framework.

    Thanks

  • June 28, 2017 at 8:57 am

    #1948566

    simonsmithbsa - Wednesday, June 28, 2017 8:48 AM

    We are installing SQL Server 2016 and are looking to use the Auditing functionality to have a proper audit trail of who (and when) is making changes to data within our databases.

    My question is whether we can capture username from the application rather than the hostname or the SQL login account. The users will be using a web application create in Entity framework.

    Thanks

    In your application's Web.config file you will need to add <identity impersonate="true" />.  It will then pass the credentials of the Windows user to SQL.  There are other IIS settings that need to be adjusted, such as disabling Anon access and only having Windows Auth on.  More good reading about this here:
    https://stackoverflow.com/a/7441365

  • June 28, 2017 at 9:00 am

    #1948571

    Most likely not.  The application would have to pass the (web) user's name to the SQL Layer.  SQL Server can only see the communication between the web server and the SQL Server.  It can't see the communication between the client and the web server. 

    You could use the IIS logs to track who is logging in, and hitting which page, but it will be a chore to match a particular SQL statement with an individual session on the web server.

  • June 28, 2017 at 9:04 am

    #1948572

    ryanbesko - Wednesday, June 28, 2017 8:57 AM

    simonsmithbsa - Wednesday, June 28, 2017 8:48 AM

    We are installing SQL Server 2016 and are looking to use the Auditing functionality to have a proper audit trail of who (and when) is making changes to data within our databases.

    My question is whether we can capture username from the application rather than the hostname or the SQL login account. The users will be using a web application create in Entity framework.

    Thanks

    In your application's Web.config file you will need to add <identity impersonate="true" />.  It will then pass the credentials of the Windows user to SQL.  There are other IIS settings that need to be adjusted, such as disabling Anon access and only having Windows Auth on.  More good reading about this here:
    https://stackoverflow.com/a/7441365

    I will investigate this, thanks.

  • June 29, 2017 at 4:56 am

    #1948698

    For details about SQL Server Auditing, please refer to the following article.
    https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine
    https://www.codeproject.com/articles/166264/audits-on-ms-sql-server
    Alternatively, you may try this automated solution ( https://www.lepide.com/lepideauditor/sql-server-auditing.html ).

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply