SiteMinder Authentication via SQL Stored Procedure

SiteMinder's documentation provides some basic guidance on the proper format to use when building a SQL stored procedure to handle authentication. The documentation reads: If a stored procedure is required for ODBC authentication:

SQLServer:When you configure the directory, the SQL query of the ODBCQuery object

associated with the user directory must call the stored procedure as follows

Call <ProcedureName> %s, %s

For example:

Call EncryptPW %s, %s

On the database, the stored procedure used to authenticate must meet these

specifications:

. The stored procedure must return an integer value.

. The first procedure parameter is the UserName Parameter and the second

is the password parameter.

. All parameters must be defined with the keyword OUT.

Here is an SQLServer example:

CREATE PROCEDURE EncryptPW

@UserName varchar(20) OUT ,

@PW varchar(20) OUT

AS

SELECT Smuser.name from Smuser where Smuser.name= @UserName and password = @PW

SELECT Smuser.password from Smuser where name= @UserName and password = @PW

return 0

A recent client of mine wished to move authentication to a SQL stored procedure (sproc) while maintaining a custom hashing algorithm in the form of a .NET component. The sproc needed to call out to this DLL (which was registered as an assembly within SQL server) to determine whether the provided password matched with the value stored in the database. I used the sample provided in the SiteMinder documentation to construct the sproc, and I added my custom logic above the final SELECT statements. Unfortunately SiteMinder consistently errored out when calling the sproc, and the logs showed the following error:

[DataDirect][ODBC SQL Server Driver]Function sequence error][][][]

After multiple attempts to isolate where this query was failing, we were finally able to pinpoint the cause. The sproc was missing one simple line of code at the top:

SET NOCOUNT ON

You can read about NOCOUNT here, but the bottom line is not having this statement in place was causing SiteMinder to receive unexpected results from SQL Server.

So if you're going to be using any custom auth logic beyond the sample provided in the SiteMinder guides, be sure the format looks like this:

CREATE PROCEDURE EncryptPW@UserName varchar(20) OUT ,

@PW varchar(20) OUT

AS

SET NOCOUNT ON

[CUSTOM LOGIC HERE]

SELECT Smuser.name from Smuser where Smuser.name= @UserName and password = @PW

SELECT Smuser.password from Smuser where name= @UserName and password = @PW

return 0