Previous Topic: How to Configure an ODBC User Directory ConnectionNext Topic: How to Configure a Custom User Directory Connection

Policy Server Guides › Policy Server Configuration Guide › User Directories › How to Configure an ODBC User Directory Connection › Configure ODBC Directory Connections

Configure ODBC Directory Connections

You can configure a user directory connection that lets the Policy Server communicate with an ODBC user store.

If you are using a SQL database for audit logs and caching is turned on, under heavy load, SiteMinder performance may suffer as the Policy Server queues messages for logging. Turn on asynchronous auditing for the realms associated with the resources being accessed by a high volume of users to alleviate the problem.

To configure the user directory connection

  1. Click Infrastructure, Directory.
  2. Click User Directory, Create User Directory

    The Create User Directory pane opens.

    Note: Click Help for descriptions of settings and controls, including their respective requirements and limits.

  3. Select ODBC from the Namespace list.

    ODBC settings open.

  4. Complete the remaining required connection information on the General and Directory Setup group boxes.

    Note: If the Policy Server is operating in FIPS mode and the directory connection is to use a secure SSL connection when communicating with the Policy Server, the certificates used by the Policy Server and the directory store must be FIPS compliant.

  5. Select a SQL query scheme.
  6. (Optional) Select Require Credentials on the Administrator Credentials group box, and type the user name and password of an administrator who has an account on the user directory in the fields on the group box.

    Note: The user name must match the user who owns the tables containing user directory data. For example, if you are using the SmSampleUsers schema, this user must be the owner of the SmUser, SmUserGroup, and SmGroup tables. The administrator's account must have read or read/write privileges for the user directory.

  7. (Optional) Specify the user directory profile attributes that are reserved for SiteMinder's use in the fields on the User Attributes group box.
  8. (Optional) Click Create on the Attribute Mapping List group box.

    The Create Attribute Mapping pane opens.

  9. Click Submit.

    The Create User Directory task is submitted for processing.

More information:

SQL Query Schemes

Configure ODBC Data Source Failover

Define an Attribute Mapping

SQL Server User Store Case Insensitivity and Extra Trailing Spaces Password Issues

A SQL Server user store can be case insensitive and also ignore extra trailing spaces in users’ passwords. This causes a problem because users entering passwords that are case insensitive or with extra trailing spaces can gain access to protected resources. For example, if a password policy is configured so that a user's password must be the case sensitive "ABCD", but the user enters "ABcd", SQL Server allows entry. In another example, if the password policy is configured so that a user's password must be " A B C", but the user enters " A B C ", SQL Server ignores the extra trailing spaces in the password and allows entry.

The following are solutions to these two issues.

First Issue: SQL Server User Store is Case Insensitive

The SQL Server database is not performing proper collation and does not recognize case sensitivity in passwords.

Solution 1

To impose case sensitivity to a SQL Server user store, select the proper collation during table creation when installing the database.

For more information about the collation, see the following:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_collation_3oa6.asp

Solution 2

If you already installed SQL Server with the default collation and the database does not recognize case sensitivity in passwords, create the proper collation when creating tables for the SiteMinder user store.

To specify the collation, modify and then import one of the following scripts.

siteminder_install\db\SQL\smsampleusers_sqlserver.sql

siteminder_install\db\SQL\smsampleusers_sqlserver_upgrade.sql

Note: These two script files use the default US English localization.

smsampleusers_sqlserver.sql Script File

Change the following lines highlighted in bold in the smsampleusers_sqlserver.sql script file:

CREATE TABLE SmGroup (
       GroupID          int NOT NULL,
       Name nvarchar(255) COLLATE Latin1_General_CS_AS NOT NULL ,
       PRIMARY KEY (GroupID)
)
DROP TABLE SmUser
go
CREATE TABLE SmUser (
   UserID         int NOT NULL,
   Name  nvarchar(255) COLLATE Latin1_General_CS_AS NOT NULL,
   Password nvarchar(255) COLLATE Latin1_General_CS_AS NOT NULL,
   LastName nvarchar(255) COLLATE Latin1_General_CS_AS NOT NULL,
   FirstName nvarchar(255) COLLATE Latin1_General_CS_AS NOT NULL,
   EmailAddress nvarchar(255) COLLATE Latin1_General_CS_AS NOT NULL,
   TelephoneNumber nvarchar(255) COLLATE Latin1_General_CS_AS NOT NULL ,
     Disabled nvarchar(255) COLLATE Latin1_General_CS_AS NOT NULL ,
     PIN      nvarchar(255) COLLATE Latin1_General_CS_AS NOT NULL ,
     Mileage int NOT NULL,
     PasswordData varchar(2000) NOT NULL,
     PRIMARY KEY (UserID)
 )
go

After modifying the script, you need to import it into the SQL Server database.

Important! Back up any existing data as running this script removes existing data and creates new tables.

smsampleusers_sqlserver_upgrade.sql Script File

The following lines highlighted in bold are changes you need to make in the smsampleusers_sqlserver_upgrade.sql script file:

/* Upgrade table SmGroup*/
ALTER TABLE  SmGroup ALTER COLUMN Name nvarchar(255) COLLATE Latin1_General_CS_AS NOT NULL
go
/* Upgrade table SmUser*/
ALTER TABLE  SmUser ALTER COLUMN Name nvarchar(255) COLLATE Latin1_General_CS_AS NOT NULL
go
ALTER TABLE  SmUser ALTER COLUMN Password nvarchar(255) COLLATE Latin1_General_CS_AS NOT NULL
go
ALTER TABLE  SmUser ALTER COLUMN LastName nvarchar(255) COLLATE Latin1_General_CS_AS NOT NULL
go
ALTER TABLE  SmUser ALTER COLUMN FirstName nvarchar(255) COLLATE Latin1_General_CS_AS NOT NULL
go
ALTER TABLE  SmUser ALTER COLUMN EmailAddress nvarchar(255) COLLATE Latin1_General_CS_AS NOT NULL
go
ALTER TABLE  SmUser ALTER COLUMN TelephoneNumber nvarchar(255) COLLATE Latin1_General_CS_AS NOT NULL
go
ALTER TABLE  SmUser ALTER COLUMN Disabled nvarchar(255) COLLATE Latin1_General_CS_AS NOT NULL 
go
ALTER TABLE  SmUser ALTER COLUMN PIN nvarchar(255) COLLATE Latin1_General_CS_AS NOT NULL
go

After modifying the script, you need to import it into the SQL Server database.

Important! Back up any existing data as running this script removes existing data and creates new tables.

Second Issue: SQL Server Ignores Trailing Spaces in the Password

SQL Server pads the strings that are being compared and makes their lengths equal.

Solution

To have SQL Server recognize trailing white spaces in passwords stored in the database, modify the Authenticate User query in the ODBC Query Scheme object using the Administrative UI. To have SQL Server compare strings without padding or trimming, incorporate the LIKE predicate instead of the = operator. When the right side of a LIKE predicate expression features a value with a trailing space, SQL Server does not pad the two values to the same length before the comparison occurs. An example authentication query is:

select Name from SmUser where Name = '%s' and Password LIKE '%s'

Important! Using the LIKE predicate expression in the password matching query can open a security hole. If a user enters a ‘%’ in the password, SQL Server treats it as wild card character for the LIKE predicate and this user can authenticate using more than one password.

Note the following: