SQL Stored Procedure Authentication

Plug-in Deployment 

Below are the install instructions for the initial deployment:

  1. Download the Plugins.SQLStoredProcAuth.dll
  2. Update your Verivo AppStudio plugin folder to contain the Plugins.SQLStoredProcAuth.dll
  3. Update your Verivo AppServer bin\plugin folder to contain the Plugins.SQLStoredProcAuth.dll


Data Source Connection 

The SQL Stored Procedure Authentication plug-in has the following connection parameters that you set in AppStudio: 

Plug-in Settings

Requires User Authentication - Settings which indicates if the user will enter in some of the plug-in connection parameters.  Select this checkbox for this plug-in.

General Tab

Server - The database derver where the database resides that contains the stored procedure.

DB Name - The database that contains the stored procedure.

UserID - The database UserID used to connect to the database.

Password - The database UserID Password used to connect to the database.

DB Timeout - The database time out in seconds.

mPlatform UserID - The UserID that will be sent to the stored procedure.  Select 'Prompt User' for this plug-in.

mPlatform Password - The password that will be sent to the stored procedure.  Select 'Prompt User' for this plug-in.

mplatform DeviceID -  The DeviceID that will be sent to the stored procedure.  Select 'Prompt User' for this plug-in.

Login Stored Proc - The name of the stored procedure that will be called during the login process.

Time Zone and Formats Tab

Base Offset - The timezone setting that the database will reside in.  This affects timezone handling within the application.

Daylight Savings - Checkbox to indicate if the database adheres to daylight savings conventions.

Date Format - Dropdown to indicate what Date Format the database uses.

Time Format - Dropdown to indicate what Time Format the database uses.

Time Stamp Format - Dropdown to indicate what Time Stamp Format the database uses.

 

Mappings

 

The SQL Stored Procedure Authentication plug-in does not need any Entities or Mappings associated to it for it to work.  However there needs to be a corresponding SQL Stored Procedure that will process the login request. 

 

SQL Stored Procedure Example

CREATE PROCEDURE [dbo].[sp_AuthLogin]
 @UserID varchar(100) = NULL,
 @Password varchar(100) = NULL,
 @DeviceID varchar(100) = NULL,
 @SessionID varchar(100) = NULL
AS
BEGIN
 DECLARE @MSG varchar(500)
  --Process Login Logic here
 SET @MSG = '@UserID =' + @UserID
 EXEC usp_mp_logmsg  'sp_AuthLogin',@MSG, 40, 'sp_AuthLogin', @sessionID
 SET @MSG = '@Password =' + @Password
 EXEC usp_mp_logmsg  'sp_AuthLogin',@MSG, 40, 'sp_AuthLogin', @sessionID
 SET @MSG = '@DeviceID =' + @DeviceID
 EXEC usp_mp_logmsg  'sp_AuthLogin',@MSG, 40, 'sp_AuthLogin', @sessionID
 SET @MSG = '@SessionID =' + @SessionID
 EXEC usp_mp_logmsg  'sp_AuthLogin',@MSG, 40, 'sp_AuthLogin', @sessionID

 --If the Login is successfull return a '1' back
 --If the Login is not successfull return a '0' back
 SELECT '1'  
END

Note that the order of the inputs to the stored procedure must be the same as in the example (UserID, Password, DeviceID, SessionID).  Additionally the server will look for the stored procedure to return a '1' for successful authentication and a '0' for an authentication failure.

 

Configuration

The SQL Stored Procedure Authentication plug-in is enabled in the Security tab of the Application Properties section of AppStudio.  Use the following steps:

  1. Load the app in AppStudio.
  2. Open Application Properties and navigate to the Security tab.
  3. Check the Use custom authentication box.
  4. Set the Time out (minutes) property.
  5. Enter in the Data Source Name created for the SQL Stored Procedure Authentication plug-in.
  6. Enter in the User ID Parameter for the Data Source (by default this value is 'mPlatform UserID').
  7. Enter in the Password Parameter for the Data Source (by default this value is 'mPlatform Password').
  8. Enter in the Device ID Parameter for the Data Source (by default this value is 'mPlatform DeviceID').

At this point, SQL Stored Procedure Authentication is enabled in the configuration.  The client will need to have its Authentication Type set to Proprietary to take advantage of these new settings.

 

Debugging

You can use the Verivo stored procedure usp_mp_logmsg to write to the Verivo Logging tables to assist you in any issue investigation.

The usp_mp_logmsg procedures takes the following input parameters:

  • serviceName
  • logMsg
  • logLevel (Default 20)
  • userID (Default mPCon)
  • sessionID (not required)
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk