Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

 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

 


Learn more about SQL Server tools