Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Generating A Password in SQL Server with T-SQL from Random Characters


By:   |   Read Comments (4)   |   Related Tips: More > Security

Problem

How can I use T-SQL to generate a random password that meets my organization's password requirements?

Solution

In this tip, we will show how to create your own user defined function in T-SQL that will return a randomly generated password. We will also demonstrate how you can customize the function to meet the password requirements for your organization. We will walk through sections of the T-SQL and then have a complete listing of the code at the end. For this example, we will create a password with a length of 10 that contains upper case and lower case letters, numbers and selected symbols. Furthermore, we will be utilizing the RANDBETWEEN function described in the tip Create Your Own RANDBETWEEN Function in T-SQL.

Let's begin with the function declaration. We will pass no parameters to the function and we will return a varchar(10). The variable @randInt will hold the randomly generated decimal ASCII code value, @NewCharacter will hold the generated character, and the variable @NewPassword will hold the password. We initialize @NewPassword to an empty string in line 8.

Function declaration and variables

In line 13, we call the RANDBETWEEN function to generate a value between 48 and 122. We use the IF statement in line 15 to restrict the character set used in the password. In line 17 we use the CHAR function to return the ASCII character represented by the decimal value. In line 18, we concatenate the new character to the password using the CONCAT function. The WHILE loop ensures this process is repeated until we have 6 characters.

First six characters

In lines 23 and 24, we generate a random lower case letter and append it to the password string. In lines 27 and 28, we generate and append a random upper case letter. In lines 31 and 32, we generate and append a random number.

Specific character types

In lines 35 through 44, we are using a WHILE loop to append selected symbols to the password until the password has a length of 10. In line 46 we return our new password.

Specific symbols

In the image below, we see the how to execute the function and an example of the password returned.

Executing the function and its output

The complete T-SQL code is listed below.

CREATE FUNCTION dbo.GeneratePassword ()
RETURNS varchar(10)
AS
BEGIN
  DECLARE @randInt int;
  DECLARE @NewCharacter varchar(1); 
  DECLARE @NewPassword varchar(10); 
  SET @NewPassword='';

  --6 random characters
  WHILE (LEN(@NewPassword) <6)
  BEGIN
    select @randInt=dbo.randbetween(48,122)
	--      0-9           < = > ? @ A-Z [ \ ]                   a-z      
    IF @randInt<=57 OR (@randInt>=60 AND @randInt<=93) OR (@randInt>=97 AND @randInt<=122)
    Begin
      select @NewCharacter=CHAR(@randInt)
      select @NewPassword=CONCAT(@NewPassword, @NewCharacter)
    END
  END

  --Ensure a lowercase
  select @NewCharacter=CHAR(dbo.randbetween(97,122))
  select @NewPassword=CONCAT(@NewPassword, @NewCharacter)
  
  --Ensure an upper case
  select @NewCharacter=CHAR(dbo.randbetween(65,90))
  select @NewPassword=CONCAT(@NewPassword, @NewCharacter)
  
  --Ensure a number
  select @NewCharacter=CHAR(dbo.randbetween(48,57))
  select @NewPassword=CONCAT(@NewPassword, @NewCharacter)
  
  --Ensure a symbol
  WHILE (LEN(@NewPassword) <10)
  BEGIN
    select @randInt=dbo.randbetween(33,64)
	--           !               # $ % &                            < = > ? @
    IF @randInt=33 OR (@randInt>=35 AND @randInt<=38) OR (@randInt>=60 AND @randInt<=64) 
    Begin
     select @NewCharacter=CHAR(@randInt)
     select @NewPassword=CONCAT(@NewPassword, @NewCharacter)
    END
  END

  RETURN(@NewPassword);
END;
GO

SELECT dbo.GeneratePassword() AS 'NewPassword';
Next Steps

You can adjust the T-SQL code above to meet the password requirements of your organization. Also, please check out these other tips and tutorials on T-SQL and the RAND() function on MSSQLTips.com.



Last Update:






About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL 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    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Wednesday, May 27, 2015 - 2:46:19 PM - SeBaFlu Back To Top

Hi, Folks!

Dallas idea to create passwords using T-SQL is a great one...

...but Jasons solution seems a liitle more handy to me.

And regarding Jasons cool idea a played a little bit around and tried to make it more robust in some ways:

  • avoiding some special characters
  • make the "Table Value Constructor" stuff more clear to me (and may be to others too)
  • adding some assertions using RAISERROR
  • trying to replace all implicit ordering features with explicit order stuff to make more clear, how Jasons idea works

Kind regards from Berlin!

Sebastian

 

DECLARE
@PW_Length INT = 12,-- Sets the generated password length
@LowerCount INT = 1,-- Sets the min number of lower case alpha characters
@UpperCount INT = 1,-- Sets the min number of upper case alpha characters
@NumberCount INT = 1,-- Sets the min number of numeric characters
@SpecCount INT = 1;-- Sets the min number of special characters


IF OBJECT_ID('tempdb..#Lowers') IS NOT NULL DROP TABLE #Lowers
-- builds the random numbered list of available lower case characters (values are entered twice to allow repeats)
-- some characters commented out to prevent from danger of confusion due to look-alike looking characters
SELECT
        ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn,
        'L' AS ct,
        PotentialPasswordCharacterTable.SingleCharacter
    INTO #Lowers
    FROM
        (VALUES
            ('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')
            ,
            ('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')
        ) AS PotentialPasswordCharacterTable (SingleCharacter)


IF OBJECT_ID('tempdb..#Uppers') IS NOT NULL DROP TABLE #Uppers
-- builds the random numbered list of available upper case characters (values are entered twice to allow repeats)
-- some characters commented out to prevent from danger of confusion due to look-alike looking characters
SELECT
        ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn,
        'U' AS ct,
        PotentialPasswordCharacterTable.SingleCharacter
    INTO #Uppers
    FROM
        (VALUES
            ('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')
            ,
            ('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')
        ) AS PotentialPasswordCharacterTable (SingleCharacter)


IF OBJECT_ID('tempdb..#Numbers') IS NOT NULL DROP TABLE #Numbers
-- builds the random numbered list of available numeric characters (values are entered twice to allow repeats)
-- some characters commented out to prevent from danger of confusion due to look-alike looking characters
SELECT
        ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn,
        'N' AS ct,
        PotentialPasswordCharacterTable.SingleCharacter
    INTO #Numbers
    FROM
        (VALUES
            /*('0'),('1'),*/('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')
            ,
            /*('0'),('1'),*/('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')
        ) AS PotentialPasswordCharacterTable (SingleCharacter)


IF OBJECT_ID('tempdb..#SpecialChars') IS NOT NULL DROP TABLE #SpecialChars
-- builds the random numbered list of available special characters (values are entered twice to allow repeats)
-- some characters commented out to prevent from danger of confusion due to look-alike looking characters
-- or special characters hard to type in (e.g. "^") or characters which making source code writing hard (e.g. double quotes >"SELECT
        ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn,
        'S' AS ct,
        PotentialPasswordCharacterTable.SingleCharacter
    INTO #SpecialChars
    FROM
        (VALUES
            ('!')/*,('"')*/,('#'),('$'),('%'),('&')/*,('''')*/,('('),(')'),('*'),('+'),(','),('-'),('.'),('/')/*,(':')*/,(';'),('            ,
            ('!')/*,('"')*/,('#'),('$'),('%'),('&')/*,('''')*/,('('),(')'),('*'),('+'),(','),('-'),('.'),('/')/*,(':')*/,(';'),('        ) AS PotentialPasswordCharacterTable (SingleCharacter)

/*
SELECT * FROM #Lowers
SELECT * FROM #Uppers
SELECT * FROM #Numbers
SELECT * FROM #SpecialChars
*/

IF @LowerCount + @UpperCount + @NumberCount + @SpecCount > @PW_Length
    RAISERROR( 'The combined required minimum character count must be less than or equal to the password length!', 18, 0 )

IF @PW_Length >
                ( SELECT COUNT( * ) FROM #Lowers )
                + ( SELECT COUNT( * ) FROM #Uppers )
                + ( SELECT COUNT( * ) FROM #Numbers )
                + ( SELECT COUNT( * ) FROM #SpecialChars )
    RAISERROR( 'The password length exceeds the total number of provided characters!', 18, 0 )

IF @LowerCount > ( SELECT COUNT( * ) FROM #Lowers )
    RAISERROR( 'More lower characters required as provided!', 18, 0 )

IF @UpperCount > ( SELECT COUNT( * ) FROM #Uppers )
    RAISERROR( 'More upper characters required as provided!', 18, 0 )

IF @NumberCount > ( SELECT COUNT( * ) FROM #Numbers )
    RAISERROR( 'More number characters required as provided!', 18, 0 )

IF @SpecCount > ( SELECT COUNT( * ) FROM #SpecialChars )
    RAISERROR( 'More special characters required as provided!', 18, 0 )



; WITH
GetChars1 AS
( -- grabs the @PW_Length number of characters from the 4 character types
SELECT
        x.rn
        , x.ct
        , x.SingleCharacter
        , CASE
            WHEN
                x.rn
                                CASE x.ct
                    WHEN 'L' THEN @LowerCount
                    WHEN 'U' THEN @UpperCount
                    WHEN 'N' THEN @NumberCount
                    WHEN 'S' THEN @SpecCount
                END
            THEN 1 -- number of dedicated character type not already reached
            ELSE 2 -- number of dedicated character type already reached
            END
            AS Order1
        , NEWID() AS Order2
    FROM
        (
            SELECT TOP (@PW_Length) rn, ct, SingleCharacter FROM #Lowers ORDER BY rn
            UNION ALL
            SELECT TOP (@PW_Length) rn, ct, SingleCharacter FROM #Uppers ORDER BY rn
            UNION ALL
            SELECT TOP (@PW_Length) rn, ct, SingleCharacter FROM #Numbers ORDER BY rn
            UNION ALL
            SELECT TOP (@PW_Length) rn, ct, SingleCharacter FROM #SpecialChars ORDER BY rn
        ) x
)
--SELECT * FROM GetChars1 ORDER BY Order1, Order2

,
GetChars2 AS
( -- grabs the TOP @PW_Length number of characters from prepared list, starting with the required number of characters for each character type
SELECT
    TOP (@PW_Length)
        SingleCharacter
        , Order1
        , Order2
    FROM GetChars1
    ORDER BY
        Order1
        , Order2
)
--SELECT * FROM GetChars2

,
GetChars3 AS
( -- create a new independent random order criteria
SELECT
        SingleCharacter
        , Order1
        , NEWID() AS Order3
    FROM GetChars2
)
--SELECT * FROM GetChars3

-- Uses the FOR XML to build a single string in random order
SELECT
    (
        SELECT
                gc.SingleCharacter
            FROM GetChars3 gc
            ORDER BY Order3
            FOR XML PATH(''), TYPE
    ).value('.','NVARCHAR(MAX)')
;

-- housekeeping
IF OBJECT_ID('tempdb..#Lowers') IS NOT NULL DROP TABLE #Lowers
IF OBJECT_ID('tempdb..#Uppers') IS NOT NULL DROP TABLE #Uppers
IF OBJECT_ID('tempdb..#Numbers') IS NOT NULL DROP TABLE #Numbers
IF OBJECT_ID('tempdb..#SpecialChars') IS NOT NULL DROP TABLE #SpecialChars


Thursday, May 07, 2015 - 6:49:56 PM - Jason Long Back To Top

I agree with Glenn in that "nonsense" are nearly impossible to remember and will inevitably lead to people writing them down in easy to find places...

More importantly, I agree with Andy’s comments… Being able to know what type of character is going to be in any given location is a bit of a deal breaker…

So with that said, I though this seemed like an interesting exercise and wanted to see if I could produce a solution that addressed the issues raised by Andy (and make it a bit more customizable in the process).

 

The following is how I decided to spend the last hour of my day… Take a look and see what you think...


DECLARE 
@PW_Length INT = 8,-- Sets the generated password length
@LowerCount INT = 1,-- Sets the min number of lower case alpha characters
@UpperCount INT = 1,-- Sets the min number of upper case alpha characters
@NumberCount INT = 1,-- Sets the min number of numeric characters
@SpecCount INT = 1;-- Sets the min number of special characters

IF @LowerCount + @UpperCount + @NumberCount + @SpecCount > @PW_Length
BEGIN 
PRINT 'The combined required minimum character counts requirement must be less than or equal to the password length.'
END;

WITH 
Lowers AS (-- builds the list of available lower case characters (values are entered twice to allow repeats)
SELECT 
ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn,
'L' AS ct,
c
FROM (VALUES ('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'),
 ('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')) x (c)
), 
Uppers AS (-- builds the list of available upper case characters (values are entered twice to allow repeats)
SELECT 
ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn,
'U' AS ct,
FROM (VALUES ('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'),
 ('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')) x (c)
), 
Numbers AS (-- builds the list of available numeric characters (values are entered twice to allow repeats)
SELECT 
ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn,
'N' AS ct,
FROM (VALUES ('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('0'),
 ('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('0')) x (c)
), 
SpecialChars AS (-- builds the list of available special characters (values are entered twice to allow repeats)
SELECT 
ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn,
'S' AS ct,
FROM (VALUES ('!'),('@'),('#'),('$'),('%'),('^'),('&'),('*'),('('),(')'),('-'),('_'),('+'),('='),('?'),('.'),(','),('<'),('>'),('\'),('/'),('|'),('{'),('}'),('['),(']'),('~'),('`'),
 ('!'),('@'),('#'),('$'),('%'),('^'),('&'),('*'),('('),(')'),('-'),('_'),('+'),('='),('?'),('.'),(','),('<'),('>'),('\'),('/'),('|'),('{'),('}'),('['),(']'),('~'),('`')) x (c)
), 
GetChars AS (-- grabs the required number of characters from the 4 character types plus any "extras" needed to fulfill the total password length
SELECT TOP (@PW_Length)
x.c
FROM (
SELECT TOP (@PW_Length) rn, ct, c FROM Lowers UNION ALL
SELECT TOP (@PW_Length) rn, ct, c FROM Uppers UNION ALL
SELECT TOP (@PW_Length) rn, ct, c FROM Numbers UNION ALL
SELECT TOP (@PW_Length) rn, ct, c FROM SpecialChars
) x
ORDER BY 
CASE 
WHEN x.rn <= CASE x.ct 
WHEN 'L' THEN @LowerCount
WHEN 'U' THEN @UpperCount
WHEN 'N' THEN @NumberCount
WHEN 'S' THEN @SpecCount
END 
THEN LEFT('000000000' + CAST(NEWID() AS VARCHAR(36)), 36) 
ELSE CAST(NEWID() AS VARCHAR(36)) 
END 
)
-- Uses the FOR XML to build a slngle string
SELECT 
(SELECT gc.c
FROM GetChars gc
FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)');

Wednesday, May 06, 2015 - 8:02:25 AM - GlennN Back To Top

Note to your organization's password requirements policy creators:  while not worse then those using "password" for their password, I can only presume that those receiving a nonsense password similar to the auto-generated example shown will immediately write it down and tape it to their monitor or other likely obvious place around their desk for the convenience of friends and hackers alike.


Wednesday, May 06, 2015 - 5:15:32 AM - Andy Scott Back To Top

Whilst the random password generated will "pass the house rules" in terms of complexity it remains weaker than those rules intended becuase we know that position 7 will always be lowercase, position 8 upper case, position 9 numeric and position 10 a symbol. So perhaps less "random" than we set out to acheive. Perhaps a better way would be to generate those same characters and then mash them up so that their sequence was less predictable. There are a number of ways to tackle this but perhaps adding a RANDOMISE function (which takes an input string and then returns the characters of that string in a random order) to the catalogue of "random" functions we have available would be a good move since we could then use it for other purposes?


Learn more about SQL Server tools