• Home
• Login with External IDP
• Custom Database Connection using miniOrange for Authentication

Custom Database Connection using miniOrange for Authentication

---

What is authentication using Custom Database Connection?

miniOrange provides the ability for database connection and use it as authentication user store to enable Single Sign-on (SSO), Multi-factor authentication, etc. for your external applications. Here, miniOrange makes custom database work as user store for authentication. Users will login using their custom database credentials only once and have a seamless SSO login into the other applications. We develop connection between miniorange and your database as user store.

What is a custom database and why use miniOrange for authentication?

• A custom database can be any DB which you can use for saving user data and other user information for your custom application for authentication or login or any other purposes. miniOrange provides connection support for all the popular custom database, such as MongoDB, MySQL, MS-SQL, Oracle, PostgreSQL, MicrosoftSQL, MariaDB, SQL Server, etc.
• The highlighting feature for using custom database connection for authentication and login is that you do not have to move your identities to any other place. So all user data is secure in your DB itself.
• Enable Single Sign-on, MFA for authentication on your Custom applications or CRM/HRM/CMS/LMS where users are stored in DB and do not support any Single Sign-On protocol inherently like WordPress, Moodle, Drupal or rather any custom application which is storing the users.
• miniOrange provides provisioning for on-the-fly user creation at the time of SSO login and you can import users using JSON / CSV file.
• Configure multiple user store for login to your applications with multiple authentication protocol support such as SAML, OAuth, etc. for different user groups based on roles and responsibilities.

1. Setup custom Database connection as user store

• Login into miniOrange Admin Console.



• Under the Choose Identity Provider, select Database from the All dropdown.



• Select the Database you want to configure and fill in the required details.

  Note: If your database type is not listed, contact us to add support for your database type.





• Select the DB type, we currently support:
  • MariaDB
  • MicrosoftSQL
  • MySQL
  • OracleDB
  • PostgresSQL
• Enter the custom Database user store Identifier. It can be any name relevant to the User store.

  Database Type	Database Host	Port Number Default
  My-SQL/MariaDB	jdbc:mysql://hostname:port/database-name	3306
  MS-SQL	jdbc:sqlserver://hostname:port/database-name	1433
  Postgres sql	jdbc:postgresql://hostname:port/database-name	5432
  OracleDB	jdbc:oracle:thin:@hostname:port/database-name	1521

• Database hostname:port (you can check with your administrator for this). For defaults port numbers, refer to defaults given above.
• The custom Database name (database-name) is the database in which your users are stored.
• Enter the Username and Password of the user which has permissions to access the DB mentioned above.
• Enter the User Table name, where users are stored for authentication.
• Enter the column name for Usernames (which can be Email-address, Unique IDs) and Password respectively.



• Click on Next to proceed to the Advanced settings for the database.




User-activated query	In which you can enter the query for checking if the User is Active or not. Example: select user_activated_column from user_tables where user_name=? and user_activated_column='true';
Add Query Strings	Add the query strings for the columns you want to import from the database. This can be useful for attribute mapping: Example: SELECT '##FIRSTNAME##', FIRSTNAME FROM users WHERE USERNAME=?
Password Hash Algorithm	Select the Hashing type used for hashing the password. We support the below Hashing types such as: SHA256, SHA512, SHA1, MD5, PHPPASS, BCRYPT.
Enable for EndUsers	Enable this option, if you want your endusers to log in to their corresponding End-User Dashboard using IDP Credentials.
Sync Users in miniOrange	Users will be created/updated in miniOrange when logging into the End User dashboard. Only applies when 'Enable for End Users' is enabled.
Fallback Authentication	If you enable this option, then the users present in an external database will be authenticated directly through the miniOrange IdP, without being created in the miniOrange IdP. It is helpful in case when the database from where the authentication is being performed contains some private or sensitive information about users.

• Click on Next to proceed to next step.
• In the User Sync tab, enter the SQL queries for User Exists, Create User, Update User, and Delete User operations.




User Exists Queries	Check if a user already exists in the database. Example: select * from users where username = '##username##'
Create User Queries	Create a user in the database when a user is created in miniOrange. Use ##attribute## notation to map attributes. Example: insert into users (username, password, firstname, lastname, email_address) values ('##username##', 'defaultPassword123#', '##firstname##', '##lastname##', '##email_address##')
Update User Queries	Update a user in the database when user details are changed in miniOrange. Example: update users set firstname='##firstname##', lastname='##lastname##', email_address='##email_address##' where username='##username##'
Delete User Queries	Delete a user from the database when a user is deleted in miniOrange. Example: delete from users where username='##username##'

• Add attributes allows us to send attributes from DB 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 '##username##', username from users  where username=?
• Go to the Attributes tab and configure the attributes for the database.
• Send Custom Attributes, if you enable this option, then only the attributes configured below will be sent in attributes at the time of login.

  Note: Add a query string for each attribute you want to fetch from the database.

• Click on Add Attribute to add a new attribute.
• Enter the Attribute Name sent to SP which is the attribute name that will be sent to the Service Provider.
• Enter the Attribute Name from Database which is the attribute name that will be fetched from the Database.
• Example: first_name → ##FIRSTNAME##, last_name → ##LASTNAME##, username → ##USERNAME##, email → ##EMAIL##



• Click on Save to save the attribute.
• Click on Save to save the configuration.

2. Testing database connection

• In order to check if connection is established with the Database or not, Test Connection is required to be done. Kindly navigate to Actions baside the identity provider to click on three dot icon and select Test Connection.



• Enter the login credentials of the user stored in the DB (user store) for testing if the database connection is correctly set up. Click on Test to check if connection was successful or not.
  
  
  
  

3. Configure your application in miniOrange

4. User Provisioning with database

• Navigate to Provisioning settings.



• Select the Database from the drop-down menu.
• Check the provisioning features.



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



• Now go to the Users >> User List and you will find the all the users imported from Database.

External References

• Custom Database Connection
• What is Identity Brokering?
• Learn more about SSO