Hi,
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
SUSER_SNAME().
We intend to swap from Basic Authentication to Integrated Windows
Authentication.
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.
Cheers,
James
