SQL SERVER 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 Alex) 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

- Shyam K. Arjarapu

Leave a Reply

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>