Unable to change password via Store Procedure due to error "ODBC SQL Server Driver Function sequence error" in SiteMinder Policy Server.

Products

• CA Single Sign On Secure Proxy Server (SiteMinder)
• CA Single Sign On SOA Security Manager (SiteMinder)
• CA Single Sign-On

Issue/Introduction

Description:

I have created the following procedure in my SQL Server Database:

<- Begin

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

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

return 0

-> End

And I have modified my sql scheme in order to change the password via store procedure:

Set User Password: call ChangePW %s , %s

However, the procedure fails with the following errors:

Snippet of smps.log:

<- Begin

[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>>>) .

-> End

Profiler:

<- Begin

[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>>>)][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][]

-> End

I'm running SiteMinder Policy Server R12 SP1 CR02 on windows 2003 and using SQL Server 2005 as User store.

Solution:

The issue is with the store procedure, it is not returning all the information as expected by SiteMinder Policy Server.

Please make the following changes:

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.

You can use the following procedure as template:

<- Begin

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

-> End

Environment

Release:
Component: SMPLC