NSD1113 How to authenticate users in AD or LDAP and get the mobile number from another SQL database
Fact
Nordic Edge One Time Password Server Version 2 and 3
Situation
How to authenticate users in one LDAP database and get the mobile number from an SQL database
Solution
In this scenario the user will be authenticating (providing username and password) to an Active Directory (AD) from Microsoft but the mobile phonenumber will be read from a SQL Server.
Note, any LDAP compatible database can be used instead of AD and any JDBC or ODBC compliant SQL database can be used instead of Microsoft SQL Server
The prerequisites for this configuration are:
-
A common unique userid attribute in both the LDAP database and the SQL database. The value from the LDAP database will be searched for in the SQL database.
-
Access right to lookup, authenticate (bind) and read the common userid attribute in the first LDAP directory and access right to run the SQL query in the SQL database
-
Installation of the attached zip file GetAttributeFromOtherSQL.zip. This file contains an external database handler that will switch from one LDAP directory to a configured SQL database
Configuration steps:
1. Unzip the attached zip file OTP_V2_GetAttributeFromOtherSQL.zip for OTPVersion 2 or
OTP_V3_GetAttributeFromOtherSQL.zip for OTPVersion 3 into the OTPServer installation directory. The zip file contains two (2) files:
-
GetAttributeFromOtherSQL.cfg
-
ext/GetAttributeFromOtherSQL.class
Make sure the files are located in these two file directories.
2. Setup a Radius or native client according to the system that will connect to the OTPServer.
3. When creating the LDAP database (in this case the AD), create the connection just like a standard AD connection except for these two changes:
In the OTP Attribute enter the following value: GET_OTP
This will later match the value in the configuration file so the OTPServer will know that it should switch to
the other SQL database.
Select the External Database Handler and enter this value:
ext.GetAttributeFromOtherSQL
Note, all values are case sensitive!
4. When the LDAP Directory is created (AD in this case), select OK and create a new SQL database
In this scenario its a ODBC database
Important fields to fill in are:
Database Display Name, make sure you copy this name, since this has to be entered exactly in the
configuration file.
Driver Manager, The JDBC driver manager (or sun.jdbc.odbc.JdbcOdbcDriver if using ODBC)
Database URL, The database JDBC URL (or jdbc:odbc:SQLDatabase for ODBC)
Select OK to save the SQL database.
5. Important, once back in the Native or RADIUS Client configuration, switch back to the Active Directory
Database. Otherwise the SQL Server database will be selected as the first database and this configuration will
not work.
Select OK to save the configuration.
6. Edit the configuration file “GetAttributeFromOtherSQL.cfg” located in the OTPServer installation directory
(e.g: C:Program FilesNordicEdgeOTPServerGetAttributeFromOtherSQL.cfg) with a texteditor for example
Notepad.exe
7. Change the following parameters:
OtherSQLDB=SQL Server
This parameter must match the SQL Database Display name as entered in step 4
For advanced configuration:
If there is a need for mapping multiple userdatabases the following parameters can be used:
- DB-MAP-1=AD Exchange Demo=SQL Server
- DB-MAP-2=Internal LDAP=Other SQL DB
In this case if the user comes from the "AD Exchange Demo" userdatabase, lookup in the "SQL Server" database will occur.
If the user comes from the "Internal LDAP" userdatabase, lookup in "Other SQL DB" will occur.
The configuration key can contain unlimited number of mappings by increasing the counter in DB-MAP-x.
In the example above, the next mapping would be: DB-MAP-3=FROM_DB_NAME=TO_DB_NAME
In order to use this feature, OTPServer version 2.0.3451 or higher is required.
SQLQuery=SELECT mobile FROM Users WHERE Userid='$$NAME$$'
Enter the SQL Query that should be run in the SQL database, insert $$NAME$$ where the users LDAP name should be inserted
MatchLDAPAttribute=cn
Enter the attributename from the primary LDAP database that should replace the tag $$NAME$$ in the SQLQuery
TriggerAttribute=GET_OTP
Enter the attribute name that should trigger this configuration. Select a non existance attribute name, eg. GET_OTP, see step 3
8. Restart the OTPServer and test the configuration with for example the Testtool.
The Logfile (otpserver.log) should display something like this:
March 5, 2008 10:58:44 AM>> DBHandlerLoader [getDBHandler] Will use DBHandler: "ext.GetAttributeFromOtherSQL"
March 5, 2008 10:58:44 AM>> DBHandler [getDNSingle] Executing Searchfilter: (&(samAccountName=administrator)(objectclass=user))
March 5, 2008 10:58:44 AM>> DBHandler [getDNSingle] Found user: "CN=Administrator,CN=Users,DC=nordicedge,DC=local"
March 5, 2008 10:58:44 AM>> ext.GetAttributeFromOtherSQL [getConfig] OtherSQLDB: SQL Server
March 5, 2008 10:58:44 AM>> ext.GetAttributeFromOtherSQL [getConfig] SQLQuery: SELECT mobile FROM Users WHERE Userid='$$NAME$$'
March 5, 2008 10:58:44 AM>> ext.GetAttributeFromOtherSQL [getConfig] LDAPAttribute: cn
March 5, 2008 10:58:44 AM>> ext.GetAttributeFromOtherSQL [getConfig] TriggerAttribute: GET_OTP
March 5, 2008 10:58:44 AM>> ext.GetAttributeFromOtherSQL [getConfig] Debug: true
March 5, 2008 10:58:44 AM>> DBHandler [setupDBConnections] Establishing JDBC Connection to "SQL Server"
March 5, 2008 10:58:44 AM>> DBHandler [getStringValueOfDNNew] SQL Query: SELECT mobile FROM Users WHERE Userid='Administrator'
March 5, 2008 10:58:44 AM>> DBHandler [getStringValueOfDNNew] JDBC Field received: 111122223333
March 5, 2008 10:58:44 AM>> ext.GetAttributeFromOtherSQL [getStringValueOfDN] Result from SQL database: <111122223333>
March 5, 2008 10:58:44 AM>> OTPConnection [Test localhost] [AUTH_OTP] UserDN: "CN=Administrator,CN=Users,DC=nordicedge,DC=local" Attr: "GET_OTP" Value: "111122223333"
Attachments
Disclaimer
The Origin of this information may be internal or external to Nordic Edge™. Nordic Edge™ makes all reasonable efforts to verify this information. However, the information provided in this document is for your information only. Nordic Edge™ makes no explicit or implied claims to the validity of this information. Any trademarks referenced in this document are the property of their respective owners.
Nordic Edge Support – www.nordicedge.se
