Cisco Systems Servers Sample Routine for Generating a PAP Authentication SQL Procedure

11-35
Cisco Secure ACS 3.0 for Windows 2000/NT Servers User Guide
78-13751-01, Version 3.0
Chapter11 Working with User Databases ODBC 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 the CHAP stored procedure is configured.
For example, with Telnet or PAP authentication, the passwords cisco or CISCO
or CiScO will all work if the SQL Server is configured to be case insensitive.
For CHAP/ARAP, the passwords cisco or CISCO or CiScO are not the same,
regardless of whether or not 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 CSNTAuthUserP ap in
Microsoft SQL Server, the default procedure used by Cisco Secure ACS for PAP
authentication. Table and column names that could vary for your database schem a
are presented in variable text. The Cisco Secure ACS product CD includes a stub
routine for creating a procedure in either SQL Server or Oracle. For more
information about data type definitions, procedure parameters, and procedure
results, see the “ODBC Database” section on page 11-30.
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