ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

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

book

Article ID: 53657

calendar_today

Updated On:

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