Unable to authenticate via store procedure using SQL Server 2005 in SiteMinder.

Document ID : KB000053608
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

I want to authenticate users via store procedure, my environment is the following:

  • SiteMinder Policy Server R12 SP1 CR02 on Windows 2003 SP1.

  • SQL Server 2005 as User Store.

The Authenticate user is defined in the query scheme as shown below:

call AuthUser %s, %s

But Siteminder is throwing the below error when calling this stored procedure:

[13:48:28][Start processing SQL statement.][][][][][6108][3864][{ ? = CALL  AuthUser(?,?) }]
[13:48:28][State = 42000 Internal Code = 102 - [NS][ODBC SQL Server Driver][SQL  Server]Incorrect syntax near '?'
.][][][][] [6108][3864][] [13:48:28][Mapped Result: -4007 Error Message: "[NS][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '?'."
SQL State: 42000.][][][][][6108][3864][] [13:48:28][State = Internal Code = 102 - ][][][][][6108][3864][] [13:48:28][Mapped Result: -4007 Error Message: "" SQL State: .][][][][][6108][3864][] [13:48:28][Finish processing SQL statement.][][][][][6108][3864][{ ? = CALL AuthUser(?,?) }]

Solution:

SiteMinder Support was able to reproduce this issue.

The reproduction environment is as shown below:

  • SiteMinder Policy Server R12 SP1 CR02 on Windows 2003 SP1.

  • SQL Server 2005 as User Store.

The error reported in the profiler suggested a problem when the procedure was called:

Incorrect syntax near '?'

In this case the error came from the database not from the SiteMinder.

However SiteMinder Support was not able to reproduce the issue in the QA environment.

Even though both environments (Support & QA) were connected to same database, the support environment was failing.

The support environment showed the following information in the SQL Profiler:

exec sp_cursoropen @p1 output,N' EXEC @P1=EncryptPW@P2 OUTPUT?@P3 OUTPUT? ',@p3 output,@p4 output,@p5
output,N'@P1 int OUTPUT,@P2 nvarchar(4000) OUTPUT,@P3 nvarchar(4000) OUTPUT',@p7 output,@p8 output,@p9 output
select @p1, @p3, @p4, @p5, @p7, @p8, @p9

The QA environment showed the folliwing:

exec sp_cursoropen @p1 output,N' EXEC @P1=EncryptPW @P2 OUTPUT,@P3 OUTPUT ',@p3 output,@p4 output,@p5
output,N'@P1 int OUTPUT,@P2 nvarchar(4000) OUTPUT,@P3 nvarchar(4000) OUTPUT',@p7 output,@p8 output,@p9 output
select @p1, @p3, @p4, @p5, @p7, @p8, @p9

As you can see the issue is because the call from the support environment was extra padding the output parameters with a question mark character.

The only difference between the two environments was the operation system version. The QA environment was running Windows 2003 SP2 and that suggested a possible problem related to the OS.

The issue was not longer present in the support environment after install all the security and update patches on the policy Server Machine.

Since the ODBC is part of the Operation System in windows, this suggests that the ODBC layer has changed between Windows 2003 SP1 and SP2.

As a workaround the customer can install all the windows patches in your system.