Updated June 29, 2020
Use this guide as a reference to create SQL tables and stored procedures that are needed for SecureAuth® Identity Platform to utilize the SQL database for membership and/or profile information.
- Applies to Identity Platform versions up to 19.07.01-10 (with hotfix 10)
For Identity Platform versions 19.07.01-11+ (with hotfix 11 or later) see SQL tables and stored procedures configuration (19.07.01-11+)
- An on-premises SQL user data store
- Integrate the SQL user data store with the Identity Platform:
- Ensure ports are open to enable connection to the SQL User Data Store
- Basic SQL knowledge to deploy the scripts below
Click the link to go to the designated section.
NOTE: If the JSON Data Format is selected for Fingerprints, Push Notification Tokens, OATH Tokens, or Access Histories Profile Fields, then the JSON script must be executed for Create Object Table Type, Get User Profile Stored Procedure, Update User Profile Stored Procedure, and Create Database operations
SQL Table Samples
This single table contains the User ID, Password, and Profile Information.
Object Table Type
This script creates the Object Table Type, which is used for Fingerprint and PUSH Notification token information.
If Fingerprint and PUSH Notification token information is configured as JSON, then use this script to create the Object Table Type.
This table contains Browser / Device Fingerprinting information for each user.
PUSH Notification Table
This table contains PUSH Notification token information for each user.
OATH Token Table
Access History Table
This table contains Access History information for each user.
Stored Procedure Samples
The following are sample stored procedures for use with the Identity Platform SQL Membership and Profile providers
Membership Stored Procedures
This stored procedure checks if the username exists, and returns that same username in the case that it does.
This stored procedure resets the password for the given user.
Get Password With Format
This stored procedure inserts the username and password into the user table, and returns a MembershipCreateStatus enumeration, with zero (0) signaling success
For more information on adding new enumeration values to the Create User stored procedure, please click here
Update Group List
This stored procedure is utilized on the Create User page to update a user's GroupList column.
Profile Stored Procedures
The sample stored procedures below include all profile information and they must be modified to match the profile information that maps to the SQL profile provider in the Identity Platform Web Admin (Data tab).
For example, if only using SQL to store and retrieve Fingerprint information, then only the Fingerprint parameter needs to be sent by the Identity Platform.
Get User Profile
This stored procedure retrieves the profile of the given username.
Get User Profile (JSON)
This stored procedure retrieves the profile of the given username if using JSON.
Update User Profile
This stored procedure updates the user profile with the given profile information.
Update User Profile (JSON)
This stored procedure updates the user profile with the given profile information, if using JSON.
This stored procedure locks an end-user account.
This stored procedure unlocks an end-user account.
Roles and Permissions
This script creates a service account user, service account role, and adds the user to the role.
Change the username (SecureAuthSQLUser) and password (Password) to the actual values.
All-inclusive Create Database Script
If you have a display issue with not being able to view over 600 lines of code, you can click and download the sql scripts.
The CreateDatabase.sql script creates a "UserStore" database and also creates all of the above tables, objects, and stored procedures within that database.
The CreateDatabaseJson.sql script creates a "UserStore" database and also creates all of the above tables, objects, and stored procedures within that database in JSON format.