Setup Database as Authentication Source via miniOrange Broker Service

In this setup, we'll be adding Database as an Authentication Source for logging the users into different applications.

Where is this applicable:

Custom applications or CRM/HRM/CMS/LMS where users are stored and do not support any Single Sign On protocol inherently like Wordpress, Moodle, Drupal or rather any custom application which is storing the users.

Step 1: Setup Database as Authentication Source

  • Login into your miniOrange Admin console and navigate to User Stores->Add User Store, and select the Database Tab.

    Database Authentication userstore
  • Enter the Database user-store Identifier it can be any name relevant to the User-store.
  • Select the Database type, we currently support:-
    • MySQL
    • Postgres-SQL
    • MS-SQL
  • Database Type Connection URL Port Number Default Hashing Default
    My-SQL
    jdbc:mysql://hostname:port/database-name
    3306 SHA-256
    MS-SQL
    jdbc:sqlserver://hostname:port/database-name
    1433 SHA-512
    Postgres sql
    jdbc:postgresql://hostname:port/database-name
    5432 MD5
  • Database HostName (You can check with your administrator for this) and port number (refer to defaults above)
  • The Database name(database-name) to be connected with, which stores the users.
  • Enter the Username and Password of the user which has permissions to access the database mentioned above.
  • Enter the Table name, where users are stored for authentication.
  • Enter the column name for Usernames (which can be Email-address, Unique IDs) and Password columns respectively.
  • Add attributes allows us to send attributes from Database to any configured application. You can write a query to fetch the attributes which should be of below format:
    For example, We want to fetch FirstName from table users with a where clause and username, the '?' will be replaced by the actual username fetched from the Username column mentioned above.SELECT 'FirstName' as VALUE, FirstName FROM `users` WHERE username = ?;
  • Select the Hashing type used for hashing the password. We support the below Hashing types:
    1. SHA512
    2. SHA1
    3. MD5
    4. PHPPASS
    5. BCRYPT
  • Default Settings for Wordpress as User-store:

  • You can access wp-config.php in the root Wordpress folder to get the database hostname, port number, database name using the below keys.

    Database Type MySQL
    Database Host DB_HOST
    Admin username and Password DB_USER and DB_PASSWORD
    UserTable wp_users
    Username Column user_login
    Password Column user_pass
  • Attribute Query example for First Name:  SELECT 'first_name' as VALUE, first_name FROM `wp_usermeta` WHERE userid= (select ID from wp_users where user_login=?);

Step 2: Testing Configuration:

  • After clicking on save, click on Select-> Test Connection. Enter the credentials of the user stored in the database for testing if the connection is correctly set up.

    Database Authentication View Configured IDPs
  • Step 3: Configure Your application in miniOrange

    • Login to miniOrange Admin Console.
    • Go to Apps >> Manage Apps Click Configure Apps button.
    • azure ad saml Apps
    • Then click on SAML tab. Search for Custom App.

    • azure ad saml Apps azure ad saml Apps
    • Get the ACS URL and SP Entity ID from your application.

    • Enter the following values OR click on Import SP Metadata:

    • Service Provider Name Choose appropriate name according to your choice
      SP Entity ID or Issuer Your Application Entity ID
      ACS URL X.509 Certificate (optional) Your Application Assertion Consumer Service URL
      NameID Format  Select urn:oasis:names:tc:SAML:1.1:nameid-format:emailAddress
      Response Signed Unchecked
      Assertion Signed Checked
      Encrypted Assertion Unchecked
      Group policy Default
      Login Method

    • Click on Save to configure Your applcation.
    • Now to get the IDP metadata of the app configured, Go to apps >> your_app >> select >> metadata tab.

    • azure ad saml Apps
    • Click on the Show Metadata details in the Information required to Authenticate via External IDPs section. Download the metadata XML file by clicking on Download Metadata button or copy the Metadata URL link.
    • azure ad saml Apps
    • You need to Upload this metadata in your application.

    Step 4: User Provisioning

    • Navigate to Users-> User Provisioning settings.
    • Database Authentication user provisioning settings
    • Select the Databasefrom the drop down menu.
    • Check the provisioning features.
    • Database Authentication user provisioning save configuration
    • To import the users from Database, go to the User Provisioning, Click on the Import Users button.
    • Select the Database from the drop down menu and save the configuration.
    •  Database Authentication user provisioning database configuration
    • Now go to the Users >> User List and you will find the all the users imported from Database.
    • Database Authentication user provisioning user list