Authentication and Auditing: Incorrect Username in Audit tables

Discussion in 'ASP .Net' started by JimLad, Sep 12, 2006.

  1. JimLad

    JimLad Guest


    I've been tasked with reviewing the Authentication and Auditing of an
    application and database.

    ASP/ASP.NET 1.1 app with SQL Server 2000 database. Separate audit trail
    database on same server.

    The system is intranet based and currently uses Basic Authentication on
    IIS6. The application itself is mostly classic ASP, but has been
    migrated into a .NET 1.1 Framework Project. So there are both .asp and
    ..aspx pages. We have auditing triggers on the tables in the database,
    but the wrong username or no username are currently being inserted.
    Authenticated users have logons to db with full DML permissions

    We now have Active Directory. Most of the data access is done through a
    SQLXML virtual directory using templates,schemas and updategrams. This
    is using a shared login, so no user info is coming through to

    We intend to swap from Basic Authentication to Integrated Windows

    Basically I want to get the real username available to the triggers in
    SQL Server. I also want to tighten up the security. I am unsure what I
    need in terms of impersonation, application roles or shared database
    role etc. I don't have the resources for a complete redesign, but could
    probably do some significant changes if necessary.

    My initial stab would be use Integrated Windows Authentication on IIS
    and SQLXML Virtual Directory. I think that means that I need to enable
    Impersonation for the aspx pages (the asp pages should impersonate by
    default ??). I should then enable an app role in the templates??? Can
    you use app roles with updategrams???? Also, given that the triggers
    reference a separate audit trail database, wouldn't an app role limit
    access to this?

    The other option is to use a shared db login for everyone and pass the
    actual username in as a parameter, but that would seem to require
    changes to every single query/sp in the database/app.

    I'm not sure that I explained myself very well above. Hope someone can
    help me figure this out! There are perhaps 2 issues here. I need to get
    the authenticated user's name through to the audit table with the
    minimum of fuss. 2 - I'd like to stop every man and his dog having
    write access to all the tables in the database via ODBC.

    Oh, I haven't mentioned app authorisation - that bit seems fine! A
    table in the database.


    JimLad, Sep 12, 2006
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.