Microsoft SQL Server and Case-Sensitive Passwords
If you want your passwords to be case sensitive and are using Microsoft SQL Server as your ODBC-compliant relational database, configure your SQL Server to accommodate this feature. If your users are authenticating by using PPP via PAP or Telnet login, the password might not be case sensitive, depending on how you set the case-sensitivity option on the SQL Server. For example, an Oracle database will default to case sensitive, whereas Microsoft SQL Server defaults to case insensitive. However, in the case of CHAP/ARAP, the password is case sensitive if you configured the CHAP stored procedure.
For example, with Telnet or PAP authentication, the passwords cisco or CISCO or CiScO will all work if you configure the SQL Server to be case insensitive.
For CHAP/ARAP, the passwords cisco or CISCO or CiScO are not the same, regardless of whether the SQL Server is configured for case-sensitive passwords.
Sample Routine for Generating a PAP Authentication SQL Procedure
The following example routine creates a procedure named CSNTAuthUserPap in Microsoft SQL Server, the default procedure that ACS uses for PAP authentication. Table and column names that could vary for your database schema appear in variable text. For your convenience, the ACS product CD includes a stub routine for creating a procedure in SQL Server or Oracle. For more information about data type definitions, procedure parameters, and procedure results, see ODBC Database.
if exists (select * from sysobjects where id = object_id (`dbo.CSNTAuthUserPap') and
sysstat & 0xf = 4)drop procedure dbo.CSNTAuthUserPap
GO
CREATE PROCEDURE CSNTAuthUserPap
@username varchar(64), @pass varchar(255)
AS
SET NOCOUNT ON
IF EXISTS( SELECT username
FROM users
WHERE username = @username
AND csntpassword = @pass )
SELECT 0,csntgroup,csntacctinfo,"No Error"
FROM users
WHERE username = @username
ELSE
SELECT 3,0,"odbc","ODBC Authen Error"
GO
GRANT EXECUTE ON dbo.CSNTAuthUserPap TO ciscosecure
GO
Sample Routine for Generating an SQL CHAP Authentication Procedure
The following example routine creates in Microsoft SQL Server a procedure named CSNTExtractUserClearTextPw, the default procedure that ACS uses for CHAP/MS-CHAP/ARAP authentication. Table and column names that could vary for your database schema appear in variable text. For more information about data type definitions, procedure parameters, and procedure results, see ODBC Database.
if exists (select * from sysobjects where id = object_id(`dbo.CSNTExtractUserClearTextPw')
and sysstat & 0xf = 4) drop procedure dbo.CSNTExtractUserClearTextPw
GO
CREATE PROCEDURE CSNTExtractUserClearTextPw
@username varchar(64)
AS
SET NOCOUNT ON
IF EXISTS( SELECT username
FROM users
WHERE username = @username )
SELECT 0,csntgroup,csntacctinfo,"No Error",csntpassword
FROM users
WHERE username = @username
ELSE
SELECT 3,0,"odbc","ODBC Authen Error"
GO
GRANT EXECUTE ON dbo.CSNTExtractUserClearTextPw TO ciscosecure
GO
Sample Routine for Generating an EAP-TLS Authentication Procedure
The following example routine creates in Microsoft SQL Server a procedure named CSNTFindUser, the default procedure that ACS uses for EAP-TLS authentication. Table and column names that could vary for your database schema appear in variable text. For more information about data type definitions, procedure parameters, and procedure results, see ODBC Database.
if exists (select * from sysobjects where id = object_id(`dbo.CSNTFindUser') and
sysstat & 0xf = 4) drop procedure dbo.CSNTFindUser
GO
CREATE PROCEDURE CSNTFindUser
@username varchar(64)
AS
SET NOCOUNT ON
IF EXISTS( SELECT username
FROM users
WHERE username = @username )
SELECT 0,csntgroup,csntacctinfo,"No Error"
FROM users
WHERE username = @username
ELSE
SELECT 3,0,"odbc","ODBC Authen Error"
GO
GRANT EXECUTE ON dbo.CSNTFindUser TO ciscosecure
GO
Reference:
http://www.cisco.com/en/US/docs/net_mgmt/cisco_secure_access_control_server_for_windows/4.0/user/guide/d.html#wp355420
