Documentation

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Panel
borderColor#000000
bgColorwhite
titleColorwhite
titleBGColor#000000
borderWidth1
titleIntroduction
borderStylesolid

Use this guide as a reference to create SQL tables and stored procedures that are needed for SecureAuth IdP to utilize the SQL database for membership and / or profile information.

Panel
borderColor#444443
bgColorwhite
titleColorwhite
titleBGColor#444443
borderWidth1
titlePrerequisites
borderStylesolid

1. Have an on-premises SQL User Data Store

2. Integrate the SQL User Data Store with SecureAuth IdP (refer to Data Tab Configuration and SQL Server Configuration Guide for specific configuration steps)

3. Ensure that ports are open to enable connection to the SQL User Data Store

4. Have basic SQL knowledge to deploy the scripts below

Panel
borderColor#135570
bgColorwhite
titleColorwhite
titleBGColor#135570
borderWidth1
titleSQL Table Samples
borderStylesolid
Panel
borderColor#116490
bgColorwhite
titleColorwhite
titleBGColor#116490
borderWidth1
titleUser Table
borderStylesolid

This single table contains the User ID, Password, and Profile Information

Code Block
languagesql
titleUser Table
CREATE TABLE [dbo].[UserTable](
	[UserName] [varchar](60) NOT NULL,
	[Password] [varchar](60) NULL,
	[PasswordSalt] [varchar](128) NULL,
	[PasswordFormat] [int] NULL,
	[PwdLastSet] [datetime] NULL,
	[FirstName] [varchar](50) NULL,
	[LastName] [varchar](50) NULL,
	[Phone1] [varchar](40) NULL,
	[Phone2] [varchar](40) NULL,
	[Phone3] [varchar](40) NULL,
	[Phone4] [varchar](40) NULL,
	[Email1] [varchar](60) NULL,
	[Email2] [varchar](60) NULL,
	[Email3] [varchar](60) NULL,
	[Email4] [varchar](60) NULL,
	[AuxID1] [varchar](512) NULL,
	[AuxID2] [varchar](512) NULL,
	[AuxID3] [varchar](512) NULL,
	[AuxID4] [varchar](512) NULL,
	[AuxID5] [varchar](512) NULL,
	[AuxID6] [varchar](512) NULL,
	[AuxID7] [varchar](512) NULL,
	[AuxID8] [varchar](512) NULL,
	[AuxID9] [varchar](512) NULL,
	[AuxID10] [varchar](512) NULL,
	[pinHash] [varchar](120) NULL,
	[Questions] [varchar](1000) NULL,
	[Answers] [varchar](1000) NULL,
	[ChallengeQuestion] [varchar](1000) NULL,
	[ChallengeAnswer] [varchar](1000) NULL,
	[CertResetDate] [datetime] NULL,
	[CertCount] [int] NULL,
	[MobileResetDate] [datetime] NULL,
	[MobileCount] [int] NULL,
	[ExtSyncPwdDate] [datetime] NULL,
	[OATHSeed] [varchar](1000) NULL,
	[OneTimeOATHList] [varchar](1000) NULL,
	[iOSDevices] [varchar](50) NULL,
	[HardwareToken] [varchar](50) NULL,
	[CertSerialNumber] [varchar](1000) NULL,
	[GroupList] [varchar](1000) NULL
) ON [PRIMARY]
GO
Panel
borderColor#116490
bgColorwhite
titleColorwhite
titleBGColor#116490
borderWidth1
titleObject Table Type
borderStylesolid

This creates the Object Table Type, which is used for Fingerprint and PUSH Notification token information

Code Block
languagesql
titleObject Table Type
CREATE TYPE [dbo].[ObjectTable] AS TABLE ([ObjectValue] [varbinary](max) NULL)
GO
Panel
borderColor#116490
bgColorwhite
titleColorwhite
titleBGColor#116490
borderWidth1
titleFingerprint Table
borderStylesolid

This table contains Browser / Device Fingerprinting information for each user

Code Block
languagesql
titleFingerprint Table
CREATE TABLE [dbo].[UserFP] (
	[UserName] [varchar](60) NOT NULL
	,[DigitalFP] [varbinary](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Panel
borderColor#116490
bgColorwhite
titleColorwhite
titleBGColor#116490
borderWidth1
titlePUSH Notification Table
borderStylesolid

This table contains PUSH Notification token information for each user

Code Block
languagesql
titlePUSH Notification Table
CREATE TABLE [dbo].[UserPN] (
	[UserName] [varchar](60) NOT NULL
	,[PNToken] [varbinary](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Panel
borderColor#116490
bgColorwhite
titleColorwhite
titleBGColor#116490
borderWidth1
titleAccess History Table
borderStylesolid

This table contains Access History information for each user

Code Block
languagesql
titleAccess History Table
CREATE TABLE [dbo].[UserAccessHistory] (
	[UserName] [varchar](60) NOT NULL
	,[AccessHistory] [varbinary](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Panel
borderColor#135570
bgColorwhite
titleColorwhite
titleBGColor#135570
borderWidth1
titleStored Procedure Samples
borderStylesolid

The following are sample stored procedures for use with the SecureAuth IdP SQL Membership and Profile providers

Panel
borderColor#116490
bgColorwhite
titleColorwhite
titleBGColor#116490
borderWidth1
titleMembership Stored Procedures
borderStylesolid
Panel
borderColor#007fb2
bgColorwhite
titleColorwhite
titleBGColor#007fb2
borderWidth1
titleGet User
borderStylesolid

This stored procedure checks if the username exists, and returns that same username in the case that it does

Code Block
languagesql
titleGet User Stored Procedure
CREATE PROC [dbo].[sp_GetUser] @UserName VARCHAR(60)
AS
BEGIN
	SELECT UserName
		,ISNULL([GroupList], '')
		,ISNULL([PwdLastSet],'1/1/1900')
	FROM UserTable
	WHERE UserName = @UserName
END
GO
Panel
borderColor#007fb2
bgColorwhite
titleColorwhite
titleBGColor#007fb2
borderWidth1
titleReset Password
borderStylesolid

This stored procedure resets the password for the given user

Code Block
languagesql
titleReset Password Stored Procedure
CREATE PROC [dbo].[sp_ResetPassword]
@UserName VARCHAR(60),
@Password VARCHAR(60),
@PasswordSalt VARCHAR(128),
@PasswordFormat int
AS
BEGIN
	UPDATE UserTable SET [Password]=@Password, [PasswordSalt]=@PasswordSalt, [PasswordFormat]=@PasswordFormat, [PwdLastSet]=GetDate() WHERE @UserName = [UserName]
	IF @@ROWCOUNT > 0
	BEGIN
		SELECT 1
	END
END
GO
Panel
borderColor#007fb2
bgColorwhite
titleColorwhite
titleBGColor#007fb2
borderWidth1
titleGet Password
borderStylesolid

This stored procedure gets the password, password salt, and password format

Code Block
languagesql
titleGetPasswordWithFormat Stored Procedure
CREATE PROCEDURE [dbo].[sp_GetPasswordWithFormat]
    @UserName                       varchar(60)
AS
BEGIN
    SELECT  [Password], PasswordFormat, PasswordSalt
    FROM    dbo.UserTable
    WHERE   @UserName = UserName
END
GO
Panel
borderColor#007fb2
bgColorwhite
titleColorwhite
titleBGColor#007fb2
borderWidth1
titleCreate User
borderStylesolid

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

Code Block
languagesql
titleCreate User Stored Procedure
CREATE PROC [dbo].[sp_CreateUser]
@UserName VARCHAR(60),
@Password VARCHAR(60),
@PasswordSalt VARCHAR(128),
@PasswordFormat int,
@Status int OUTPUT
AS
BEGIN
INSERT INTO UserTable ([UserName], [Password], [PasswordSalt], [PasswordFormat], [PwdLastSet]) VALUES (@UserName, @Password, @PasswordSalt, @PasswordFormat, GetDate())
 
IF @@ROWCOUNT > 0 
SELECT @Status = 0
ELSE
SELECT @Status = 1
END
GO
Panel
borderColor#116490
bgColorwhite
titleColorwhite
titleBGColor#116490
borderWidth1
titleProfile Stored Procedures
borderStylesolid
Info

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 SecureAuth IdP 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 SecureAuth IdP

Panel
borderColor#007fb2
bgColorwhite
titleColorwhite
titleBGColor#007fb2
borderWidth1
titleGet User Profile
borderStylesolid

This stored procedure retrieves the profile of the given username

Code Block
languagesql
titleGet User Profile Stored Procedure
CREATE PROC [dbo].[sp_GetUserProfile] @UserName VARCHAR(60)
AS
BEGIN
    SELECT UserName
        ,IsNull(FirstName, '') FirstName
        ,IsNull(LastName, '') LastName
        ,IsNull(Phone1, '') Phone1
        ,IsNull(Phone2, '') Phone2
        ,IsNull(Phone3, '') Phone3
        ,IsNull(Phone4, '') Phone4
        ,IsNull(Email1, '') Email1
        ,IsNull(Email2, '') Email2
        ,IsNull(Email3, '') Email3
        ,IsNull(Email4, '') Email4
        ,IsNull(AuxID1, '') AuxID1
        ,IsNull(AuxID2, '') AuxID2
        ,IsNull(AuxID3, '') AuxID3
        ,IsNull(AuxID4, '') AuxID4
        ,IsNull(AuxID5, '') AuxID5
        ,IsNull(AuxID6, '') AuxID6
        ,IsNull(AuxID7, '') AuxID7
        ,IsNull(AuxID8, '') AuxID8
        ,IsNull(AuxID9, '') AuxID9
        ,IsNull(AuxID10, '') AuxID10
        ,IsNull(pinHash, '') pinHash
        ,IsNull(Questions, '') Questions
        ,IsNull(Answers, '') Answers
        ,IsNull(ChallengeQuestion, '') ChallengeQuestion
        ,IsNull(ChallengeAnswer, '') ChallengeAnswer
        ,IsNull(CertResetDate, '1/1/1900') CertResetDate
        ,IsNull(CertCount, 0) CertCount
        ,IsNull(CertSerialNumber, '') CertSerialNumber
        ,IsNull(MobileResetDate, '1/1/1900') MobileResetDate
        ,IsNull(MobileCount, 0) MobileCount
        ,IsNull(ExtSyncPwdDate, '1/1/1900') ExtSyncPwdDate
        ,IsNull(HardwareToken, '') HardwareToken
        ,IsNull(iOSDevices, '') iOSDevices
        ,IsNull(OATHSeed, '') OATHSeed
        ,IsNull(OneTimeOATHList, '') OneTimeOATHList
		,IsNull(GroupList, '') GroupList
    FROM UserTable
    WHERE UserName = @UserName
    SELECT DigitalFP
    FROM UserFP
    WHERE UserName = @UserName
    SELECT PNToken
    FROM UserPN
    WHERE UserName = @UserName
	SELECT AccessHistory
	FROM UserAccessHistory
	WHERE UserName = @UserName
END
GO
Panel
borderColor#007fb2
bgColorwhite
titleColorwhite
titleBGColor#007fb2
borderWidth1
titleUpdate User Profile
borderStylesolid

This stored procedure updates the user profile with the given profile information

Code Block
languagesql
titleUpdate User Profile Stored Procedure
CREATE PROC [dbo].[sp_UpdateUserProfile] @UserName VARCHAR(60)
	,@FirstName VARCHAR(50)
	,@LastName VARCHAR(50)
	,@Phone1 VARCHAR(60)
	,@Phone2 VARCHAR(40)
	,@Phone3 VARCHAR(40)
	,@Phone4 VARCHAR(40)
	,@Email1 VARCHAR(60)
	,@Email2 VARCHAR(60)
	,@Email3 VARCHAR(60)
	,@Email4 VARCHAR(60)
	,@AuxID1 VARCHAR(512)
	,@AuxID2 VARCHAR(512)
	,@AuxID3 VARCHAR(512)
	,@AuxID4 VARCHAR(512)
	,@AuxID5 VARCHAR(512)
	,@AuxID6 VARCHAR(512)
	,@AuxID7 VARCHAR(512)
	,@AuxID8 VARCHAR(512)
	,@AuxID9 VARCHAR(512)
	,@AuxID10 VARCHAR(512)
	,@pinHash VARCHAR(120)
	,@Questions VARCHAR(1000)
	,@Answers VARCHAR(1000)
	,@ChallengeQuestion VARCHAR(1000)
	,@ChallengeAnswer VARCHAR(1000)
	,@CertResetDate DATETIME
	,@CertSerialNumber VARCHAR(1000)
	,@CertCount INTEGER
	,@MobileResetDate DATETIME
	,@MobileCount INTEGER
	,@ExtSyncPwdDate DATETIME
	,@HardwareToken VARCHAR(1000)
	,@iOSDevices VARCHAR(1000)
	,@OATHSeed VARCHAR(1000)
	,@OneTimeOATHList VARCHAR(1000)
	,@DigitalFP dbo.ObjectTable READONLY
	,@PNToken dbo.ObjectTable READONLY
	,@AccessHistory dbo.ObjectTable READONLY
AS
IF NOT EXISTS (
		SELECT UserName
		FROM UserTable
		WHERE UserName = @UserName
		)
BEGIN
 INSERT INTO UserTable (UserName) VALUES (@UserName)
END
	BEGIN TRY
		BEGIN TRANSACTION
		UPDATE UserTable
		SET FirstName = @FirstName
			,LastName = @LastName
			,Phone1 = @Phone1
			,Phone2 = @Phone2
			,Phone3 = @Phone3
			,Phone4 = @Phone4
			,Email1 = @Email1
			,Email2 = @Email2
			,Email3 = @Email3
			,Email4 = @Email4
			,AuxID1 = @AuxID1
			,AuxID2 = @AuxID2
			,AuxID3 = @AuxID3
			,AuxID4 = @AuxID4
			,AuxID5 = @AuxID5
			,AuxID6 = @AuxID6
			,AuxID7 = @AuxID7
			,AuxID8 = @AuxID8
			,AuxID9 = @AuxID9
			,AuxID10 = @AuxID10
			,pinHash = @pinHash
			,Questions = @Questions
			,Answers = @Answers
			,ChallengeQuestion = @ChallengeQuestion
			,ChallengeAnswer = @ChallengeAnswer
			,CertResetDate = @CertResetDate
			,CertCount = @CertCount
			,CertSerialNumber = @CertSerialNumber
			,MobileResetDate = @MobileResetDate
			,MobileCount = @MobileCount
			,ExtSyncPwdDate = @ExtSyncPwdDate
			,HardwareToken = @HardwareToken
			,iOSDevices = @iOSDevices
			,OATHSeed = @OATHSeed
			,OneTimeOATHList = @OneTimeOATHList
		WHERE UserName = @UserName
		--- Update Fingerprints ---
		DELETE
		FROM UserFP
		WHERE UserName = @UserName
		INSERT INTO UserFP
		SELECT @UserName
			,ObjectValue
		FROM @DigitalFP
		---------------------------
		--- Update Push Notification ---
		DELETE
		FROM UserPN
		WHERE UserName = @UserName
		INSERT INTO UserPN
		SELECT @UserName
			,ObjectValue
		FROM @PNToken
		---------------------------
		--- Update Access History ---
		DELETE
		FROM UserAccessHistory
		WHERE UserName = @UserName
		INSERT INTO UserAccessHistory
		SELECT @UserName
			,ObjectValue
		FROM @AccessHistory
		---------------------------
		COMMIT
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK
	END CATCH
GO
Panel
borderColor#135570
bgColorwhite
titleColorwhite
titleBGColor#135570
borderWidth1
titleRoles and Permissions
borderStylesolid

This creates a service account user, service account role, and adds the user to the role

Info

Change the username (SecureAuthSQLUser) and password (Password) to the actual values

Code Block
languagesql
titleCreate Role and User
CREATE LOGIN SecureAuthSQLUser WITH PASSWORD = 'Password';
CREATE USER SecureAuthSQLUser FOR LOGIN SecureAuthSQLUser; 
CREATE ROLE db_serviceaccount
EXEC sp_addrolemember 'db_serviceaccount', 'SecureAuthSQLUser'
GRANT EXECUTE ON [UserStore].dbo.sp_CreateUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetUserProfile TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_UpdateUserProfile TO db_serviceaccount
GRANT EXECUTE ON TYPE::[UserStore].dbo.ObjectTable TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetPasswordWithFormat TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_ResetPassword TO db_serviceaccount
Panel
borderColor#135570
bgColorwhite
titleColorwhite
titleBGColor#135570
borderWidth1
titleAll-inclusive Create Database Script
borderStylesolid

The CreateDatabase.sql script creates a "UserStore" database and also creates all of the above tables, objects, and stored procedures within that database