Distauth: ODBC ISAPI Filter

Introduction

The purpose of the ODBC ISAPI Filter is to restrict access to individual subdirectories within a DISTAUTH secured directory. Restriction to these subdirectories is based on an authorized user lists stored in a database table - which can be connected to via an ODBC connection.

How it works:

When the filter sees the unique string in a directory that is located within the DISTAUTH secured folder (usually UCD-Access), a query against a database table is done. If the query returns a non-null result, then the user is authorized to access the restricted directory. A query that returns no results means that the user is not authorized and the user will be redirected to the access denied page specified in the config file.

Directory Structure:

  1. The Secure directory must be located within the DISTAUTH secured folder (usually UCD-Access). http://download.ucdavis.edu/ucd-access/download.secure will be checked for authorization. http://download.ucdavis.edu/download.secure/ucd-access will NOT be checked for authorization.
  2. A specific string must be added to the directory that needs to be further secured. The default specific phrase is ".secure". Although the unique phrase can be changed in the configuration file, caution should be taken. If the default phrase is changed to "a", then any subdirectory that contains an "a" will need authorization. The unique string can be located anywhere in a directory name. For example, CHE192.secure, .secureChe192, and Che.secure192 will all be checked for authorization - assuming they are located after the ucd-access directory.

Nested secure directories:

A directory such as http://download.ucdavis.edu/download.secure/ls.secure/ is permitted. However, the user needs access to both the download.secure directory and the ls.secure directory to get access to the ls.secure subdirectory.

How to configure the filter:

The ODBCFilter must be run after the DISTAUTH filter since it relies on the user having a AuthUser Cookie to grab the user's Kerberos ID. For more information about the DISTAUTH filter, visit http://distauth.ucdavis.edu/.
Figure 1 (ISAPI Filters are run in order from top to bottom. Note that the ODBCFilter is located below ucd-access)
The config file must match the name of the dll except for the extension (i.e. odbcfilter.dll uses odbcfilter.txt) and reside in the same directory as the dll. The recommended location for both the DISTAUTH filter and this ODBC filter is %windir%\system32\inetsrv\ .

Example:

To secure a directory named "CHE192" on the web server in the ucd-access folder

  1. Download and unzip the ODBCFilter.dll and ODBCFilter.txt file and place them in a secured directory on the web server such as %windir%\system32\inetsrv\
  2. Open the ODBCFilter.txt file, and specify a unique key phrase. In this example, we will use the default of .secure
  3. Rename the "CHE192" directory to "CHE192.secure".
  4. Create a table (i.e. tblRestrictedAccess) in your database that lists the users and the directory they are authorized to see. i.e.
  5. Create a DSN on the web server to access this table.
  6. Write a simple query in the config file. Something like : "SELECT Kerberos FROM tblRestrictedAccess WHERE lower(Kerberos)='%s' AND lower(AuthDirectory)='%s'". The first %s will automatically be replaced by the filter with the requesting user’s kerberos ID and the second %s with the requested directory.
    KerberosAuthDirectory
    kerb01CHE192.secure
    kerb02CHE192.secure
    ......
  7. Add the ODBCFilter to your web site using Internet service manager.
  8. Test it.
  9. Add as many authDirectory as you need. You still should only need one query string.

Note: Additional fields can be added to the table above - for example, expiration date and account status. Download.ucdavis.edu checks for account status (field Status) and account expiration date (field ExpireDT). The query checks to make sure that the status is ACTIVE and the expiration date is greater than today. Our query string looks like:

DB_QUERY=SELECT Kerberos FROM tblDLUser WHERE lower(Kerberos)='%s' AND lower(AuthDirectory)='%s' AND Status='ACTIVE' AND (ExpireDT is NULL OR ExpireDT > GETDATE())

Troubleshooting:

If you have problems getting the filter to work there are two things to check. One, make sure that the filter actually loaded correctly in IIS (Open Internet Information Services (IIS) Manager, right click on the web site and view the properties, then click on the ISAPI Filters tab). The status of the filter should show a green up arrow similar to Figure 1. If you don't see an arrow you may need close and reopen the properties window. If the filter seems to be running correctly but it is not properly authorizing access to the directories then you may need to look at your database connection and query. Check your DSN, username, password and query to see if they all work. Database errors (like connection or query failures will be written to your server's system event log. The error should be something similar to Figure 2. The only part of the event error you need to really look at is the last line after "The following information is part of the event:". You should see either something like "Connection Failed" or "...cannot execute".

Example Configuration File

# Config file for the ODBCFilter ISAPI Filter

# Unique string used to determine restricted directory.  Maximum directory size : 50 characters.
UNIQUE_KEY=.secure

# Cache timeout period. (in minutes)
CACHE_TIMEOUT=5

# DSN to use to connect to the database
DB_DSN=myDSN

# The username and password to access the database.  Comment out both lines if there is none
DB_USER=MyUser
DB_PASSWORD=********

# Query to be run when verifying access.  Maximum query size : 1023 characters.
# Requirements :
#	Query must be on one line
#	Query must include a place holder (%s) for both kerberos and requested Directory (Kerberos first, AuthDirectory second)
#Sample Querys:
#DB_QUERY=SELECT Kerberos FROM tblDLUser WHERE lower(Kerberos)='%s' AND lower(AuthDirectory)='%s'
#DB_QUERY=SELECT Kerberos FROM tblDLUser WHERE lower(Kerberos)='%s' AND lower(AuthDirectory)='%s' AND Status='ACTIVE' AND (ExpireDT is NULL OR ExpireDT > GETDATE())

DB_QUERY=SELECT Kerberos FROM tblDLUser WHERE lower(Kerberos)='%s' AND lower(AuthDirectory)='%s' AND Status='ACTIVE' AND (ExpireDT is NULL OR ExpireDT > GETDATE())

# Redirection URL to redirect to if authentication fails
ACCESS_DENIED=http://www.ucdavis.edu/authentication/denied.html