Error : State = 42000 Internal Code Unable to authenticate in SQL Server

Products

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

Issue/Introduction

 

Authenticating users via stored procedure when running SiteMinder
Policy Server 12SP1CR02 on Windows 2003 SP1 with SQL Server 2005 as
User Store.

The User's Authentication is defined in the query scheme as shown
below :

  call AuthUser %s, %s

Policy Server throws 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(?,?) }]

 

Cause

 

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 Policy
Server.

Taking 2 environments (Env1 & Env2) sharing the same ODBC User Store.

Even though both environments (Env1 & Env2) are connected to same
database, the Env1 environment fails when the Env2 executes as
expected.

Both Env1 and Env2 environments shows the following information in the
SQL Profiler:

Env1 :

  exec sp_cursoropen @p1 output,N' EXEC @[email protected] [email protected] 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

Env2 :

  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 issue is caused by because the call from in Env1 environment was
extra padding the output parameters with a question mark character
"?".

The only difference between the two environments is the operation
system version. The Env2 environment was running Windows 2003 SP2 when
Env1 runs on Windows 2003 SP1 and that suggests a possible problem
related to the OS.

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.

 

Resolution

 

Apply the OS system patches to solve the issue;

The issue will no longer present in the Env1 after install all the
Windows security and update patches on the Policy Server Machine.