Database Error executing query and Function sequence error - unable to change password via ODBC stored procedure due to Policy Server

Products

• CA Single Sign-On
• SITEMINDER

Issue/Introduction

Creating the following procedure in a SQL Server Database:

create PROCEDURE ChangePW
@UserName varchar(4000) out,
@PW varchar(4000) out
AS

Update smuser
set password = @PW
where Smuser.name= @UserName

return 0

Modifying the SQL Authentication Scheme to change the password via the stored procedure:

Set User Password: call ChangePW %s , %s

However, the procedure fails with the following errors:

smps.log:

[2128/1848][Wed Mar 04 2009 16:47:19][SmDsOdbcProvider.cpp:1688][ERROR] Database Error executing query ( '{ ? = CALL ChangePW(?,?) }'). Error: Internal Error: Database error. Code is -4007 (DBMSG: <<< State = HY010 Internal Code = 0 - [DataDirect][ODBC SQL Server Driver]Function sequence error>>>) .

smtracedefault.log:

[16:25:09][Finish processing SQL statement.][][][][CDb.cpp:233][2128][1848][03/04/2009][16:25:09.171][CSmRecordset::DoSelect] [Exception][][][][][][][][][][][][Internal Error: Database error. Code is -4007 (DBMSG: <<< State = HY010 Internal Code = 0 - [DataDirect][ODBC SQL Server Driver]Function sequence error>>>)][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][{ ? = CALL ChangePW(?,?) }][][][][][][][][][]
[16:25:09][Caught an exception 'Internal Error: Database error. Code is -4007 (DBMSG: <<< State = HY010 Internal Code = 0 - [DataDirect][ODBC SQL Server Driver]Function sequence error>>>)'][][][][CDb.cpp:405][2128][1848][03/04/2009][16:25:09.171][CSmRecordset::Open][][][][][][][][][][][][][Internal Error: Database error. Code is -4007 (DBMSG: <<< State = HY010 Internal Code = 0 - [DataDirect][ODBC SQL Server Driver]Function sequence error>>>)][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][]

Cause

The issue is with the stored procedure; it is not returning all the information as expected by Policy Server.

Resolution

Change the following:

1. Modify the procedure declaration to first list the password and the User ID second;
2. Add "SET NOCOUNT ON" to the begining of the procedure;
3. Add a SELECT statement to retrieve the UserID of the user that is being modified;
4. Return 0 upon success;

Use the following procedure as a template:

create PROCEDURE ChangePW
@PW varchar(4000) out,
@UserName varchar(4000) out
AS
SET NOCOUNT on;

Update smuser
set password = @PW
where Smuser.name= @UserName

select name
from smuser
where Smuser.name= @UserName

return 0