Custom SQL Authentication Class for NetIQ Access Manager

Custom SQL Authentication Class for NetIQ Access Manager

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.

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

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

  4. 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.)

  5. 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. 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.

  2. 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 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 NameProperty ValueDefault value (for optional properties)
ldapHostIP 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
ldapPortLDAP or LDAPS port, usually 389 or 636389
ldapBaseThe base container where the users will be created in the LDAP Directoryo=novell
ldapAdminCN (in LDAP format) of the LDAP administrative accountcn=admin,o=novell
ldapPasswordPassword of the LDAP administrator
ldapObjectClassObject class for the users that will be created in LDAP directoryUser
debugDebug level. One of: ALL, TRACE, DEBUG, INFO, WARN, ERROR, FATAL, OFF Debug level. One of: ALL, TRACE, DEBUG, INFO, WARN, ERROR, FATAL, OFFOFF
traceidA string to insert into the log file for easy searching for operations related to this SQL Custom Authentication ClassSQLAUTHN
pwdInputCharsetCharset of incoming passwordUTF-8
pwdBinaryEncodingBinary encoding of passwordBase64
pwdHashAlgorithmHash algorithmSHA-256
pwdDbCharsetCharset that the password is saved in inside the SQL databaseUTF-8
sqlDbAdminSQL database administrator account name
sqlDbHostThe 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>;
sqlDbAdminPwdSQL database administrator password
sqlPasswordFieldThe field inside the SQL database that holds the passwordPassword
sqlUserQueryThe SQL database query to locate the user and select the column data to return with itselect Password, FirstName, LastName, Email from dbo.Users where UserID = ?
ldapMappingMaps SQL field name to LDAP attribute nameFirstName=givenName,
LastName=sn,Email=mail
pwdsEncryptedAllows 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, falsefalse
authenticationProcessWhether to use the faster authentication algorithm versus the safer one. One of : safe, fastSafe
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.

NAM_SQLAuthClass-2

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
Labels (1)
Attachments

DISCLAIMER:

Some content on Community Tips & Information pages is not officially supported by Micro Focus. Please refer to our Terms of Use for more detail.
Top Contributors
Version history
Revision #:
3 of 3
Last update:
‎2020-01-31 22:07
Updated by:
 
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.