Simple SQL Server Function to Generate Random 8 Character Password


By:   |   Updated: 2018-01-31   |   Comments (1)   |   Related: More > Security

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


Last Updated: 2018-01-31


get scripts

next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips
Related Resources





Comments For This Article




Sunday, February 18, 2018 - 4:01:01 AM - Eli Leiba Back To Top (75234)

 Please note this if you use thus function:

 

The usage of crypt_gen_random will indeed use a cryptographically secure pseudorandom number generator, but given the limitations you have included in the system (8 characters of Base64 encoded data), you will limit the entropy for the password to 48 bits (6 bits of entropy per byte).

 

 

Furthermore, it is very likely that a password generated this way will not meet most password complexity rules for the following reasons:

 

·        Password length may be too short for most modern password complexity policies

 

·        There is a very high probability of not having any special characters in your password

 

·        There is a high probability of having a password that will consist exclusively of characters (no number or symbols)

Instead, I would recommend using a password management system to generate and securely store passwords

 



download


Recommended Reading

Enabling xp_cmdshell in SQL Server

Steps to Drop an Orphan SQL Server User when it owns a Schema or Role

Encrypting passwords for use with Python and SQL Server

Is your SQL Server environment ready for GDPR?

Understanding SQL Server fixed database roles





get free sql tips
agree to terms


Learn more about SQL Server tools