Oftentimes organizations start their web presence by building it in-­-house. A common characteristic of such in–house developed solutions is that a database is used to hold user information such as user name, password, and additional information such as first and last name, email address, etc.

As the web presence grows and requires more and more management and maintenance, the wish and need for a COTS (commercial off the shelf) solution grows to free up those precious resources and put them to work on other projects with direct impact to the bottom line.

The challenge then becomes: How make that user data accessible for a COTS access management solution like NetIQ Access Manager so that users can still authenticate using their current credentials. A transparent migration path from custom to COTS is desirable.

NetIQ Access Manager can use an SQL database as an authentication source with this custom authentication class. The user is added to an LDAP directory after successful authentication to the SQL database so that Access Manager can have access to the user attributes, password, etc.

- NAM_SQLAuthClass_CoolSolutions.pdf
- NamSQLAuthClass.jar.zip
- http://sqlauthclass.sourceforge.net/

The authentication class supports two algorithms for authenticating users: “safe” and “fast”.

The “safe” algorithm follows these steps:

1. User tries to access a protected resource.

• User redirected to Access Manager login form page and types in username/password.

• Algorithm verifies that a user exists within the SQL database, by performing an SQL query.

• Algorithm does a password comparison to make sure that the password the user typed in matches the one stored in SQL. (The password is not required to be reversible. There just needs to be the ability to do a password comparison that returns a true or false.)

• Once the user’s SQL identity has been verified, an analogous user is created in the LDAP directory. If the user already exists in the LDAP directory, it is updated in case the user has changed their password or any other data. The user name and password can then be pulled from the Credential Profile for use in policies like Form Fill or Identity Injection. The authentication class can be configured to move any number of fields from the SQL table to the LDAP directory user and will keep those in sync for each user at login time.

The “fast” algorithm follows these steps:

1. 
   
   1. User tries to access a protected resource.
   
   

User redirected to Access Manager login form page and types in username/password.

• Algorithm tries to log the user in using the LDAP directory. If the login succeeds, the algorithm ends here. If the login fails, then the algorithm falls back to “safe”:

• Algorithm verifies that a user exists within the SQL database, by performing an SQL query.

• Algorithm does a password comparison to make sure that the password the user typed in matches the one stored in SQL. (The password is not required to be reversible. There just needs to be the ability to do a password comparison that returns a true or false.)

• Once the user’s SQL identity has been verified, an analogous user is created in the LDAP directory. If the user already exists in the LDAP directory, it is updated in case the user has changed their password or any other data. The user name and password can then be pulled from the Credential Profile for use in policies like Form Fill or Identity Injection. The authentication class can be configured to move any number of fields from the SQL table to the LDAP directory user and will keep those in sync for each user at login time.

 

The two algorithms compared:

“safe”:

• SQL database is 100% the authoritative source for authentication.

• All the relevant user attributes are synchronized to the LDAP directory for use in identity injection and form-­-fill policies on every login.

• This algorithm is ideal when its performance suffices and when SQL stays the authoritative user store.

• This algorithm is slower than the “fast” algorithm.

“fast”:

• SQL database is authoritative only for the initial login after implementation.

• LDAP Directory becomes authoritative for successful logins after initial login.

• All the relevant user attributes are only synchronized during the initial login and every time login fails and the algorithm falls back to the SQL database.

• Users will be able to login with old password after a password change in the SQL database. Logging in with the new password will trigger the update of the user in LDAP.

• This algorithm is ideal when the LDAP directory is the new authoritative user store and the SQL database will be retired eventually.

• This algorithm is faster than the “safe” algorithm

This custom authentication class enables the following scenarios:

1. SQL stays authoritative authentication source
   
   In this scenario you are using an SQL database as your user store for some or all of your applications and you have no plans on changing that. You would implement and configure this custom authentication class for the applications in question and keep it in place until plans change.
   
   Account changes (usernames, passwords, etc.) have to happen in your database through your existing tools and interfaces.

• Migrate user credential into an enterprise LDAP directory
  
  In this scenario you are planning to move away from your existing database as a user store and move towards an enterprise directory as the authoritative user source. Having an enterprise directory opens up a whole new world for your user data as it now becomes accessible by any application supporting LDAP and allows you to add value to your business immediately.
  
  You would implement and configure this custom authentication class and keep it in place until every user has logged in at least once. You can easily validate that by comparing the users in your database to the migrated users in your enterprise directory. Once all the users, or at least critical mass, have migrated their credentials seamlessly into your enterprise directory, you can remove this custom authentication class and use just the OOTB functionality of NetIQ Access Manager.

Use the documentation for importing the custom authentication class into Access Manager here:  https://www.netiq.com/documentation/netiqaccessmanager4/nacm_enu/data/bb8bwzi.html.

Configuration items specify the SQL database to connect to, the LDAP directory to create users in, and all the other details needed. See Figure 1 below for an example.

Figure 1

The following table is a listing of properties, their possible values, and their default values. Some properties do not have default values because they are required.

Property Name

Property Value

Default value (for optional properties)

ldapHost

IP or DNS of the LDAP Directory where users will be created. (All the default LDAP values listed apply to using the SingleBoxUserStore inside the NAM appliance)

localhost

ldapPort

LDAP or LDAPS port, usually 389 or 636

389

ldapBase

The base container where the users will be created in the LDAP Directory

o=novell

ldapAdmin

CN (in LDAP format) of the LDAP administrative account

cn=admin,o=novell

ldapPassword

Password of the LDAP administrator

ldapObjectClass

Object class for the users that will be created in LDAP directory

User

debug

Debug level. One of: ALL, TRACE, DEBUG, INFO, WARN, ERROR, FATAL, OFF Debug level. One of: ALL, TRACE, DEBUG, INFO, WARN, ERROR, FATAL, OFF

OFF

traceid

A string to insert into the log file for easy searching for operations related to this SQL Custom Authentication Class

SQLAUTHN

pwdInputCharset

Charset of incoming password

UTF-8

pwdBinaryEncoding

Binary encoding of password

Base64

pwdHashAlgorithm

Hash algorithm

SHA-256

pwdDbCharset

Charset that the password is saved in inside the SQL database

UTF-8

sqlDbAdmin

SQL database administrator account name

sqlDbHost

The JDBC-formatted database host of the SQL database. This value differs from db type to another. For MSSQL, it is jdbc:sqlserver://<ipaddress>;dat abaseName=<database name>;

sqlDbAdminPwd

SQL database administrator password

sqlPasswordField

The field inside the SQL database that holds the password

Password

sqlUserQuery

The SQL database query to locate the user and select the column data to return with it

select Password, FirstName, LastName, Email from dbo.Users where UserID = ?

ldapMapping

Maps SQL field name to LDAP attribute name

FirstName=givenName, LastName=sn,Email=mail

pwdsEncrypted

Allows the passwords (both LDAP and SQL) to be specified in an encrypted string so that it can't be viewed in the NAM admin console in clear text. The encryption utility is built into the jar and can be used to encrypt password before specifying the property value. One of: true, false

false

authenticationProcess

Whether to use the faster authentication algorithm versus the safer one. One of : safe, fast

Safe

passwordProviderClass Name

The password provider class to use for encryptying and decrypting passwords in the SQL database and for use in encrypting the passwords saved in the NAM configuration.

com.netiq.nam.authn .pwdprovider.Default PasswordProvider

Implementing the PasswordProvider interface.
The authentication class has an interface for password encryption since each SQL database implementation likely uses a different password encryption and comparison algorithm. This interface must be overridden. The posted example contains a DefaultPasswordProvider class which was written for a specific SQL database with a certain password encryption algorithm so it likely won't work for any other SQL database. Remove the DefaultPasswordProvider when you implement your own.

Encrypting passwords for the admin console.
You can encrypt the password so that it can't be viewed in clear text in the NAM Administration Console. You can use the encryption algorithm built in to the DefaultPasswordProvider, or you can use your own provider. The command line arguments to this utility are 1: the password to encrypt, and (optional) 2: the password provider class to use. If you want to just use the built-in encryption in the DefaultPasswordProvider, don't specify a second argument. To execute the entry point in the jar, use this command:

java -jar NamSQLAuthClass.jar <password to encrypt> <password providerclass>

This will show an output like this:

java -jar SQLNamAuth.jar test com.netiq.nam.authn.pwdprovider.myPwdProv 
Encryption: Parameter encryption 
Original:   test 
Encrypted:  q7trw3W2Q87gG2lU/gxiNA 
Validates:  true

Once you set the pwdsEncrypted property to true, then you must also specify both passwords (sqlDbAdminPwd and ldapPassword) in the NAM UI properties for the auth class using the encrypted values output by this process.

Logging.
The log file where the trace statements are written is:
/var/opt/novell/nam/logs/idp/tomcat/catalina.out

The traceid can be very useful for locating the log entries that are pertinent to the loaded custom authentication class. For instance in this log excerpt below used the traceid "Wendy" and shows that the custom authentication class was loaded.



To search for all trace statements output with the "Wendy" traceid, you can execute this command:

tailf /var/opt/novell/nam/logs/idp/tomcat/catalina.out | grep Wendy

Database sample script.
The following database sample script can be used to generate users in an SQL table for
testing purposes. Note: the encrypted password noted in the script has a decrypted value of "netiq000" so the user should be able to log in as "juser" with a password of "netiq000".

SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO

SET ANSI_PADDING ON 
GO

CREATE TABLE [dbo].[Users](
		[UserID] [nvarchar](50) NOT NULL, 
		[FirstName] [nvarchar](50) NOT NULL,
		[LastName] [nvarchar](50) NOT NULL, 
		[Password] [ntext] NULL,
		[Email] [nvarchar](255) NULL, 
		[AccountID] [nvarchar](50) NULL,
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
		[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 

GO

SET ANSI_PADDING OFF 
GO

ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_FirstName] DEFAULT ('') FOR
[FirstName] 
GO

ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_LastName] DEFAULT ('') FOR
[LastName] 
GO

INSERT dbo.Users (UserID, FirstName, LastName, Password, Email, AccountID) 
	VALUES ('juser', 'Joe', 'User',
'grzjqAe/95UoN83fth7wTdwyxiUeZecW9mkmhqhXHV1OMHYzbGwxMjM=',    'juser@netiq.com', 
'netIQuser001')
GO