SQL Server stored procedure to generate random passwords

Problem

SQL Server is used to support many applications and one such feature of most applications is the storage of passwords.  Sometimes there is a need to reset a password using a temporary password or generate a random password for a new user.  In this tip I cover a simple stored procedure to generate random passwords that can be incorporated into your applications.

Solution

The following stored procedure creates strings of random characters based on four parameters that configure the result.

1. LEN – specifies the length of the result (required)
2. MIN – sets the starting ASCII code (optional: defaults to “48”: which is the number zero)
3. RANGE – determines the range of how many ASCII characters to include (optional: defaults to “74”  (48 + 74 = 122) where 122 is a lowercase “z”)
4. EXCLUDE – a string of characters to exclude from the final output (optional: defaults include zero, capital “O”, and these punctuation marks :;<=>?@[]`^\/)

Here is the stored procedure:

create proc [dbo].uspRandChars

    @len int,

    @min tinyint = 48,

    @range tinyint = 74,

    @exclude varchar(50) = ‘0:;<=>?@O[]`^\/’,

    @output varchar(50) output

as

    declare @char char

    set @output = ”

    while @len > 0 begin

       select @char = char(round(rand() * @range + @min, 0))

       if charindex(@char, @exclude) = 0 begin

           set @output += @char

           set @len = @len – 1

       end

    end

;

go

To use the stored procedure issue commands such as the following.

declare @newpwd varchar(20)




— all values between ASCII code 48 – 122 excluding defaults

exec [dbo].uspRandChars @len=8, @output=@newpwd out

select @newpwd




— all lower case letters excluding o and l

exec [dbo].uspRandChars @len=10, @min=97, @range=25, @exclude=’ol’, @output=@newpwd out

select @newpwd




— all upper case letters excluding O

exec [dbo].uspRandChars @len=12, @min=65, @range=25, @exclude=’O’, @output=@newpwd out

select @newpwd




— all numbers between 0 and 9

exec [dbo].uspRandChars @len=14, @min=48, @range=9, @exclude=”, @output=@newpwd out

select @newpwd

Here is sample output from the above commands:

Next Steps

• Unsure what ASCII codes to use, refer to this list of ASCII codes
• See where this procedure can be used in your application. Some examples include users inviting their friends or generating a new password by request or as a temporary replacement.
• Determine your environment’s security policy including password length and valid characters.
• Implement your password policy in the parameters.