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 the Identity Platform version 19.07.01-11 (with hotfix 11) or later
At minimum, you must have version 19.07.01-11 (with hotfix 11) or later to use SQL stored procedures: LockUser, UnLockUser, EnableUser, and DisableUser.
- 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
Single table contains the User ID, Password, and Profile Information.
Object Table Type
Script to create the Object Table Type used for Fingerprint and Push Notification token information.
Use this script to create the Object Table Type when Fingerprint and Push Notification token information is configured in JSON.
Table contains Browser / Device Fingerprinting information for each user.
Push Notification Table
Table contains Push Notification token information for each user.
OATH Token Table
Access History Table
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
Checks for existing username and returns the same username when it exists.
Resets the password for the given user.
Get Password With Format
Inserts the username and password into the user table, and returns a MembershipCreateStatus enumeration, with zero (0) signaling success.
For more information about adding new enumeration values to the Create User stored procedure, see MembershipCreateStatus Enum.
Update Group List
Used on the Create User page to update the GroupList column for a user.
Profile Stored Procedures
The stored procedure examples include all profile information. You must modify your stored procedures to match the profile information mapped to the SQL profile provider in the profile properties.
For example, to only use SQL to store and retrieve Fingerprint information, then send only the Fingerprint parameter to the Identity Platform.
Get User Profile
Retrieves the profile of the given username.
Get User Profile (JSON)
Retrieves the profile of the given username if using JSON.
Update User Profile
Updates the user profile with the given profile information.
Update User Profile (JSON)
Updates the user profile with the given profile information, if using JSON.
Locks an end user account.
Unlocks an end user account.
Disables an end user account.
Enables an end user account.
Roles and Permissions
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 the SQL script links to download and view in a code editor.
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.