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.
The SQL Server database is not performing proper collation and does not recognize case sensitivity in passwords.
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
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.
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.
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.
SQL Server pads the strings that are being compared and makes their lengths equal.
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:
Copyright © 2010 CA. All rights reserved. | Email CA about this topic |