Generating random passwords and One Time Passcodes (OTPs) is a pretty common requirement. I am sure you can find several different versions of T-SQL code available on the internet for this very purpose. Most of these solutions simply use the RAND() function to generate a random number and pick corresponding ASCII characters to generate the password.
Rcently, I had a requirement to create over 50 local SQL authenticated logins. Me being an automation minded DBA, I’m never going to create those 50+ logins manually. That’s when I needed an automatic password generator. I looked around and came across a few good ones, but none of them offered the flexibility that I desired.
What I needed was a single stored procedure, that would:
- generate a simple or complex password
- generate a numeric-only OTP
- allow me to configure the length of the password or OTP
- allow me to control the number of upper & lower case letters in the passowrd/OTP
- allow me to decide how many numbers or symbols go into the passowrd/OTP
Here comes my own version of password and OTP generator
I decided to create my own version that meets all the requirements that I’d stated above. I created RandomPasswordAndOTPGenerator and it has the below input and output parameters:
| Parameters |
|---|
| @Length – tinyint Controls password length. Valid values are between 4 and 128 characters. Defaults to 12. |
| @UpperLetters – tinyint Controls how many UPPER case letters are included in the password. Valid values are between 0 and 128. Defaults to 4. |
| @LowerLetters – tinyint Controls how many lower case letters are included in the password. Valid values are between 0 and 128. Defaults to 4. |
| @Numbers – tinyint Controls how many numbers are included in the password. Valid values are between 0 and 128. Defaults to 2. |
| @Symbols – tinyint Controls how many symbols/special characters are included in the password. Valid values are between 0 and 128. Defaults to 2. |
| @Password – nvarchar(128) This is an OUTPUT parameter. Used to return the generated password or OTP. Even if you do not use this output parameter, the generated password/OTP is still returned as a single row resultset. |
| How the parameters @UpperLetters @LowerLetters @Numbers and @Symbols work? You can specify any value between 0 and 128 for the above parameters. The sum of the above parameters must equal the length of the password (@Length parameter). If the sum of the above parameters is not equal to @Length: – the procedure would discard the values provided for @UpperLetters @LowerLetters @Numbers and @Symbols – and default to 33% of UPPER case letters, 33% of lower case letters, 16.5% of numbers and 16.5% of symbols |
OK! Enough of documentation – Where’s the code?
Here it is!
SQL
DROP PROC IF EXISTS dbo.RandomPasswordAndOTPGenerator
GO
CREATE PROC dbo.RandomPasswordAndOTPGenerator
(
@Length smallint = 12,
@UpperLetters tinyint = 4,
@LowerLetters tinyint = 4,
@Numbers tinyint = 2,
@Symbols tinyint = 2,
@Password nvarchar(128) = '' OUTPUT
)
AS
BEGIN
/*
Created by Vyas Kondreddi
https://dutyDBA.com
*/
SET NOCOUNT ON
IF @Length < 4 OR @Length > 128
BEGIN
RAISERROR('Password length must be between 4 and 128 characters', 12, 1)
RETURN (-1)
END
IF @UpperLetters + @LowerLetters + @Numbers + @Symbols <> @Length
BEGIN
RAISERROR('Upper, lower, number and symbols together must equal password length. Reverting to defaults.', 0, 1)
SELECT @UpperLetters = @Length * 0.33, @LowerLetters = @Length * 0.33, @Numbers = @Length * 0.165, @Symbols = @Length * 0.165
END
DECLARE
@AlphabetStr nvarchar(52) = 'a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,',
@NumberStr nvarchar(20) ='0,1,2,3,4,5,6,7,8,9,',
@SymbolStr nvarchar(54) = '!,",$,%,^,&,*,(,),[,{,],},-,_,=,+,#,@,:,;,/,\,?,<,>,.,'
/*
In case of odd password length (eg. 7 or 13 characters), it is not possible
to equally split the upper, lower letters or numers and symbols.
In this case, the below loop quickly adjusts the individual password
component lengths to make sure the requested password length is achieved.
*/
WHILE ((@UpperLetters + @LowerLetters + @Numbers + @Symbols) <> @Length)
BEGIN
IF (@UpperLetters + @LowerLetters + @Numbers + @Symbols) < @Length
SET @UpperLetters += 1
IF (@UpperLetters + @LowerLetters + @Numbers + @Symbols) > @Length
SET @UpperLetters -= 1
IF (@UpperLetters + @LowerLetters + @Numbers + @Symbols) < @Length
SET @LowerLetters += 1
IF (@UpperLetters + @LowerLetters + @Numbers + @Symbols) > @Length
SET @LowerLetters -= 1
IF (@UpperLetters + @LowerLetters + @Numbers + @Symbols) < @Length
SET @Numbers += 1
IF (@UpperLetters + @LowerLetters + @Numbers + @Symbols) > @Length
SET @Numbers -= 1
IF (@UpperLetters + @LowerLetters + @Numbers + @Symbols) < @Length
SET @Symbols += 1
IF (@UpperLetters + @LowerLetters + @Numbers + @Symbols) > @Length
SET @Symbols -= 1
END
SET @Password =
(
--The STRING_AGG() function joins together the returned random
--characters in a random order, as its ordered by NEWID()
SELECT STRING_AGG(value,'') WITHIN GROUP (ORDER BY NEWID())
FROM
(
/*
1. The TOP (n) in each of the SELECTs makes sure requested number of characters are returned
2. The STRING_SPLIT() function splits the provided comma separated character array
into rows of individual characters
3. The REPLICATE() function repeats the specified string to at least 128 characters long
to cater for the maximum password length of 128
*/
SELECT TOP (@LowerLetters) value
FROM STRING_SPLIT(REPLICATE(@AlphabetStr, 5), ',')
WHERE value <> ''
ORDER BY NEWID()
UNION ALL
SELECT TOP (@UpperLetters) value
FROM STRING_SPLIT(REPLICATE(UPPER(@AlphabetStr), 5), ',')
WHERE value <> ''
ORDER BY NEWID()
UNION ALL
SELECT TOP (@Numbers) value
FROM STRING_SPLIT(REPLICATE(@NumberStr, 13), ',')
WHERE value <> ''
ORDER BY NEWID()
UNION ALL
SELECT TOP (@Symbols) value
FROM STRING_SPLIT(REPLICATE(@SymbolStr, 5),',')
WHERE value <> ''
ORDER BY NEWID()
) AS x
)
SELECT @Password AS GeneratedPassword
RETURN (0)
END
GOExample stored procedure calls and sample output:
12 letter password (default parameters)
--Generate a 12 character password with all default parameters
EXEC dbo.RandomPasswordAndOTPGenerator
/*
Sample output:
Gk7Xg_k%Pz1S
xBaT"?Pd6Z2f
(6>oykwU8NCD
*/Numeric 6 digit OTP
--Generate a numeric 6 digit OTP
EXEC dbo.RandomPasswordAndOTPGenerator
@Length = 6,
@UpperLetters = 0,
@LowerLetters = 0,
@Numbers = 6,
@Symbols = 0
/*
Sample output:
020232
673634
793028
*/
16 character password with default complexity
--Generate a 16 character password with default complexity options
EXEC dbo.RandomPasswordAndOTPGenerator
@Length = 16
/*
Sample output:
v8@gPrIo8SWSmaN$
DugPNhx-0tOtRL9=
{xKR7vBIqJwr\Ys5
*/4 letter UPPER case OTP
--Generate a simple 4 letter upper case character OTP
EXEC dbo.RandomPasswordAndOTPGenerator
@Length = 4,
@UpperLetters = 4,
@LowerLetters = 0,
@Numbers = 0,
@Symbols = 0
/*
Sample output:
CFWZ
DXJO
NCQK
*/12 letter password (6 UPPER & 6 lower case)
--Generate a 12 character password with 6 UPPER and 6 lower case letters
EXEC dbo.RandomPasswordAndOTPGenerator
@Length = 12,
@UpperLetters = 6,
@LowerLetters = 6,
@Numbers = 0,
@Symbols = 0
/*
Sample output:
KLmGdgFgsQjX
kRTNqYUrxHxb
SxZjJTgWEroa
*/
4 UPPER, 4 lower, 4 numbers, 4 symbols
--Generate a 16 letter password (4 UPPER case, 4 lower case , 4 numbers,4 symbols
EXEC dbo.RandomPasswordAndOTPGenerator
@Length = 16,
@UpperLetters = 4,
@LowerLetters = 4,
@Numbers = 4,
@Symbols = 4
/*
Sample output:
7Z_1%qvMM+s"2Y4z
Tw&Q1Hm8.E]93k#j
)m6vJ/J%6l5TK{x0
*/Using the OUTPUT parameter
--Capture the password Using the OUTPUT parameter
DECLARE @MyPassword nvarchar(128)
EXEC dbo.RandomPasswordAndOTPGenerator
@Length = 16,
@Password = @MyPassword OUTPUT
SELECT @MyPassword AS CapturedViaOutputParameter
GO
/*
Sample output:
CapturedViaOutputParameter
--------------------------
4$gYJYeaX/mOn8Dn
*/
I hope I have given you plenty of examples to showcase the flexibility and ease of use of my password and OTP generator. Enjoy! As always, share your thoughts by leaving a comment below!
