Policy Server Guides › Policy Server Configuration Guide › User Directories › How to Configure an ODBC User Directory Connection › SQL Server User Store Case Insensitivity and Extra Trailing Spaces Password Issues

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:

• If you are creating the ODBC query scheme object using the SDK, you can specify the authentication query using the pszQueryAuthenticateUser attribute of the Sm_PolicyApi_ODBCQueryScheme_t object.
• If you are creating the ODBC query scheme object using Perl Scripting Interface, you can specify the authentication query while calling the CreateODBCQueryScheme() API.