SQL Database Generate Password Function

Most of you might have come across need for creating a random password for the users. I prefer using passwords that are generated completely random, containing a-zA-Z0-9 and some special characters, making them tough to guess. However, thinking from the user's perspective those passwords are really hard to remember. So, if you want to leverage complexity of the passwords for making them easy to remember then here is what I have used.

The key concept behind my algorithm is, a set of letters (like Basheer) forming a meaningful name is easy to remember than a set of letters (like 6ZuK3s) that makes no sense. I compiled a table with Given Names pulled from sparkleware. To generate a random password, I pick a random given name and pull out only first 6 characters from it. Then I create a random set of special characters and set of random hex digits from NewID, UUID/GUID. The random given-name part, special chars part, hex digits part all together frame a random password. This is makes password not only easy to remember, compared to completely randomized text, but also somewhat tough to predict them as well.

Please note that this technique might generate passwords that can be predicted using dictionary attack, if considerable precautions were not taken. If your application’s password needs to highly secure then, I suggest you follow completely randomized text generation as detailed in here,

ALTER FUNCTION dbo.udf_generate_password(@newid VARCHAR(50), @rand REAL) RETURNS VARCHAR(100)

AS

BEGIN

--****************************************************

--   FUNCTION: DBO.UDF_GENERATE_PASSWOR

--   AUTHOR: SHYAM K. ARJARAPU

--   PARAMETER: @NEWID = NEWID() A UUID/GUID/NEWGUID

--      @RAND = RAND()

--   REASON: STUPID SQL SERVER DOES NOT ALLOW NON-

--      DETERMINISTIC FUNCTIONS RAND(), GETDATE(), NEWID()

--      INSIDE THE FUNCTIONS.

--  DESCRIPTION: THOUGH RANDOM PASSWORDS ARE SECURE, THEY

--     ARE HARD TO REMEMBER. USERS CAN EASILY REMEMBER

--     GROUP OF LETTERS FORMING A WORD. THE TABLE

--     DBO.TB_PASSWORD_DICTIONARY CONTAINS SET OF FIRST NAMES

--     FROM WHICH A RANDOM NAME IS SELECTED AND A SPECIAL

--     CHARACTER WITH RANDOM STRING IS APPENDED TO MAKE THE

--     PASSWORD NOT ONLY SECURE BUT ALSO ITS EASY TO REMEMBER

--  USAGE:

--     SELECT dbo.udf_generate_password(NEWID(), RAND())

--*****************************************************

 DECLARE @randPassword VARCHAR(100)

 DECLARE @randNewID VARCHAR(10)

 DECLARE @randFirstName VARCHAR(6)

 DECLARE @randSpecialChar VARCHAR(2)

 DECLARE @maxID INT

 DECLARE @CONST_FN_LEN TINYINT

 DECLARE @CONST_NID_LEN TINYINT

 DECLARE @CONST_SPL_LEN TINYINT

 DECLARE @CONST_SPL_CHARS VARCHAR(15)

 --DEFINE THE REQUIRED LENGTHS & SPECIAL CHARS

 SET @CONST_FN_LEN = 6

 SET @CONST_NID_LEN = 4

 SET @CONST_SPL_LEN = 2

 SET @CONST_SPL_CHARS = '@#$^*;,._!'

 --FRAME THE RANDOM TEXT PART

 SELECT @randNewID = LEFT(@newid, @CONST_NID_LEN)

 SELECT @maxID = MAX([KEY_ID])

   FROM dbo.tb_password_dictionary -- Contains KEY_ID Identity(1,1), [Value] Varchar. All the given-names goes here

 --FRAME RANDOM FIRST NAME PART

 SELECT @randFirstName = LEFT([VALUE], @CONST_FN_LEN)

   FROM dbo.tb_password_dictionary

  WHERE [KEY_ID] = CONVERT(INT, @rand *@maxID) + 1

 SET @randSpecialChar = ''

 --FRAME THE SPECIAL CHARTS PART

 WHILE( @CONST_SPL_LEN <> 0)

 BEGIN

  SET @randSpecialChar = @randSpecialChar +

   SUBSTRING(@CONST_SPL_CHARS, CONVERT(INT, @rand * LEN(@CONST_SPL_CHARS)) + 1, 1)

  SET @rand = @rand * @rand

  SET @CONST_SPL_LEN = @CONST_SPL_LEN - 1

 END

 --FRAME THE PASSWORD ALTOGETHER

 SET @randPassword = @randFirstName + @randSpecialChar + @randNewID

 RETURN @randPassword

END

Courtesy: Shyam K. Arjarapu