Stored Procedures

asked on May 13

Hello, I have a stored procedure in SQL that i would like to call from a Forms Lookup rule. The SP generates a random string for a password. I want the lookup rule to fill the password field in my form with that string.

Here is the Stored Procedure

USE [Laserfiche_XXXX]
GO
/****** Object: StoredProcedure [dbo].[GeneratePassword] Script Date: 5/13/2025 2:44:46 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:       <Author,,Name>
-- Create date: <Create Date,,>
-- Description:   <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GeneratePassword]
   -- random string generator
   @characterSet varchar(64) = '0123456789abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNOPQRSTUVWXYZ@#$&%' --character set
   , @randomString nvarchar(9) OUTPUT -- the size should be >= the max length required
   , @onechar nvarchar(1) -- one character
   , @i int = 0 -- counter
   , @r int = 0 --random int

AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

While @i <= 8 -- put (max length - 1) here it's 10
begin
set @r = floor(rand() * LEN(@characterSet))
set @onechar = SUBSTRING(@characterSet, @r,1)
set @randomString = concat(@randomString, @onechar);
set @i = len(@randomString)
end
END

0 0

SELECTED ANSWER

replied on May 14 • Show version history

Just in case anyone lands here after a search and is looking for help using a SQL stored procedure as a Forms lookup data source.

I modified your provided SQL script to not accept any parameters as the variables can be hard coded. The important point is that your stored procedure must return a result set to populate a form field. In the example below the result set is returning a single column named 'Password' from a SELECT query that contains the value for the newly generated password.

Note: The SQL script below is coded as a CREATE TO script and assumes you do not already have a stored procedure named GeneratePassword in the target database.

USE [ExternalLfTables]	-- Change target database to the appropriate database...
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GeneratePassword] 
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @characterSet varchar(64) = '0123456789abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNOPQRSTUVWXYZ@#$&%';
	DECLARE @passwordLength int = 8;		-- Length of the password returned...
	DECLARE @password varchar(8) = '';		-- Make sure to update this variable size if you update the password length...
	DECLARE @oneChar varchar(1) = '';
	DECLARE @index int = 0;
	DECLARE @randomNumber int = 0;

    WHILE (@index < @passwordLength)
    BEGIN
        SET @randomNumber = FLOOR(RAND() * LEN(@characterSet));
        SET @oneChar = SUBSTRING(@characterSet, @randomNumber,1);
        SET @password = CONCAT(@password, @onechar); 
        SET @index = LEN(@password);
    END

	SELECT @password AS [Password]

END
GO

After adding the stored procedure to the target database make sure you refresh the appropriate data source in the Administration section of Forms so that the stored procedure is available for selection in the form lookup rules.

In the form designer create a new lookup rule, select the appropriate database, query type, and stored procedure. Select the target form field to receive the password value returned from the stored procedure and manually key in the name of the result set column that will be be used to populate the form field.

Note: The lookup rule UI is not smart enough to determine the result set column names returned from the stored procedure. You must manually type the column name into the result set column field.

2 0

View 1 previous reply

replied on May 15

A couple of words of caution:

this procedure can generate weak password ("12345678" or similar is theoretically possible with the given character set)
when the form is submitted, the field value will be saved in clear text in the database

0 0

replied on May 15

Marce, could you tell us more about the business process you're trying to build here? Is it an account registration/signup form or something? If we know the objective, we can provide guidance on doing so securely.

There are real security implications to generating passwords and returning them to Forms. We recommend against it except in limited, special circumstances.

The password generator algorithm above can generate "12345678"
If the form is submitted after the lookup, it will be stored in plaintext in Forms. Organizational security policies generally do not allow this.

0 0

replied on May 15

@████████ This is a new employee form that HR fills out, it gets sent to us in IT to setup the users accounts. The first thing the users do after we set them up is change their password . This is just a way to save us time in the account creation step on our way to full automation. We are generating a couple of passwords for different things.

0 0

Question

Question

Answer

Replies

Sign in to reply to this post.