Simple SQL Server Function to Generate Random 8 Character Password

Problem

The requirement is to create simple way to generate a strong eight-character password.

Solution

The solution involves creating a T-SQL view and a user defined function that produces a random eight-character password each time the function is called.

  1. The view returns a cryptographic random number generated by the Crypto API by calling the CRYPT_GEN_RANDOM system function.
  2. Please note that using the view rather than coding the call to the system function directly in the function to avoid the error 443:  Invalid use of a side-effecting operator ‘Crypt_Gen_Random’ within a function. Microsoft has a rule that user defined functions cannot be used to perform actions that modify the database state. The CRYPT_GEN_RANDOM is one of several functions that are assumed to change the database state, but calling it through a view from the function is allowed.
  3. The resulting binary data is then converted to a VARCHAR(MAX) type and truncated to eight characters that produce a password that is returned to the user.

T-SQL code for view and user defined function

Following is the code to create the view and the function.

-- create view for function call
CREATE VIEW vRandom
AS
SELECT randval = CRYPT_GEN_RANDOM (8)
GO
-- ============================================================
-- Author:      Eli Leiba
-- Create date: 01-2018
-- Description: a view and a scalar UDF to generate a random
-- 8 characters password
-- ============================================================
CREATE FUNCTION [dbo].[GenPass]()
RETURNS VARCHAR(8)
AS
BEGIN
   -- Declare the variables here
   DECLARE @Result VARCHAR(8)
   DECLARE @BinaryData VARBINARY(8)
   DECLARE @CharacterData VARCHAR(8)
 
   SELECT @BinaryData = randval
   FROM vRandom
 
   Set @CharacterData=cast ('' as xml).value ('xs:base64Binary(sql:variable("@BinaryData"))',
                   'varchar (max)')
   
   SET @Result = @CharacterData
   
   -- Return the result of the function
   RETURN @Result
END
GO

Example Use

In order to generate a new password, simple call the function by executing this T-SQL code:

DECLARE @newPass VARCHAR(8)
SELECT @newPass = dbo.GenPass()
PRINT @newPass

The result is:

KhH7Xql5

For every call, we will get a new random eight-character password.

Generating and Storing Password

This function only returns a generated password and does not store it anywhere. To further enhance the process, we can write the password to a table, so we can generate a bunch of passwords. 

Here is the T-SQL code for the table and stored procedure.

CREATE TABLE dbo.GenPasswords (
   requestID INT IDENTITY,
   TS DATETIME,
   pass VARCHAR(8)
   )
GO
-- =============================================
-- Author:      Eli Leiba
-- Create date: 15.1.2018
-- Description: Generate password and request ID
-- =============================================
CREATE PROCEDURE dbo.usp_GenPass @pass VARCHAR(8) = NULL OUTPUT,
   @reqID INT = 0 OUTPUT
AS
BEGIN
   SET NOCOUNT ON;
   SET @pass = dbo.GenPass();
   INSERT INTO dbo.GenPasswords
   VALUES (
      getDate(),
      @pass
      );
   SELECT @reqID = IDENT_CURRENT('GenPasswords');
   SET NOCOUNT OFF;
END
GO

Example Use

The below code will generate the password and store to the table.  The two outputs are the password and requestID value in the table.

DECLARE @p VARCHAR (8)
DECLARE @req INT
EXEC dbo.usp_GenPass @p out, @req out
PRINT @p
PRINT @req

The result is:

omm47vHo
1

When using the procedure, you could refer to the table based on the requestID to query the password.

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *