SQL Server stored procedure to generate random passwords
By: Brent Shaub | Updated: 2011-11-14 | Comments (31) | Related: More > T-SQL
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.
The following stored procedure creates strings of random characters based on four parameters that configure the result.
- LEN - specifies the length of the result (required)
- MIN - sets the starting ASCII code (optional: defaults to "48": which is the number zero)
- RANGE - determines the range of how many ASCII characters to include (optional: defaults to "74" (48 + 74 = 122) where 122 is a lowercase "z")
- EXCLUDE - a string of characters to exclude from the final output (optional: defaults include zero, capital "O", and these punctuation marks :;<=>[email protected]`^\/)
Here is the stored procedure:
create proc [dbo].uspRandChars @len int, @min tinyint = 48, @range tinyint = 74, @exclude varchar(50) = '0:;<=>[email protected]`^\/', @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, @[email protected] out select @newpwd
-- all lower case letters excluding o and l exec [dbo].uspRandChars @len=10, @min=97, @range=25, @exclude='ol', @[email protected] out select @newpwd
-- all upper case letters excluding O exec [dbo].uspRandChars @len=12, @min=65, @range=25, @exclude='O', @[email protected] out select @newpwd
-- all numbers between 0 and 9 exec [dbo].uspRandChars @len=14, @min=48, @range=9, @exclude='', @[email protected] out select @newpwd
Here is sample output from the above commands:
- 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.
Last Updated: 2011-11-14
About the author
View all my tips
Comments For This Article
|Thursday, August 04, 2016 - 10:02:18 AM - Jeff Moden||Back To Top (43053)|
I warned of a major problem with this code and all similar code when it first came out in 2011. Ralph Wilson warned of the problem again back in 2012. The major problem is that there is no filter on the possibility of generating some really offensive words as a part of the passwords.
Be careful folks. There are some people that will sue at the drop of a hat... any hat.
|Tuesday, April 12, 2016 - 2:43:09 PM - Scott||Back To Top (41203)|
Same procedure with more explanation:
CREATE PROC dbo.uspRandChars
|Saturday, April 09, 2016 - 2:56:49 PM - Sobhan Dutta||Back To Top (41178)|
Could you please explain the script in details. You can email ([email protected]) me as well.
Thanks in advance.
|Thursday, July 30, 2015 - 2:56:04 PM - Scott||Back To Top (38328)|
On SQL 2008 and later, you can now use CRYPT_GEN_RANDOM to create up to 8000 bytes of randomness. This code uses 8 bytes, which is much more random than either RAND() or NEWID().
CREATE PROC dbo.uspRandChars @len INT = 12, @Charset NVARCHAR(256) = '23456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz.,#$%^&*-+_/=', @output NVARCHAR(50) OUTPUT AS SET NOCOUNT ON; SET @output = ''; SELECT @output += SUBSTRING(@Charset, FLOOR(ABS(CAST(CRYPT_GEN_RANDOM(8) AS BIGINT) / 9223372036854775808.5) * LEN(@Charset)) + 1, 1) FROM master.dbo.spt_values WHERE type = 'P' AND number < @len; GO
|Monday, June 29, 2015 - 10:49:35 AM - Taffmeister||Back To Top (38059)|
I am using this script and it's just great . . . .
|Friday, June 12, 2015 - 6:29:03 AM - Andile||Back To Top (37906)|
Thankx for the great post it help me a lot. Now can u help me on time count down.
|Monday, January 27, 2014 - 7:09:39 AM - Sundar||Back To Top (29230)|
Thnx it was very helpful nw neone pls help me i want to write a query in .cshtml file to execute the above procedure......
|Tuesday, July 23, 2013 - 7:42:23 AM - Ganesh Pate||Back To Top (25957)|
Very Nice SP for Random Number generation.
|Friday, June 21, 2013 - 3:26:09 PM - Max Montenegro||Back To Top (25521)|
Great Job!! works excelent!!! Thank you
|Monday, September 24, 2012 - 7:06:27 AM - Chami||Back To Top (19651)|
Good post - it really helped me !
|Sunday, July 08, 2012 - 9:11:51 AM - Brent Shaub||Back To Top (18396)|
@Ralph, I like your idea to build the string in chucks. If using two alpha characters side-by-side, that would eliminate all three- and four-character obscenities in English. Another idea is to hard-code the obscenity list and regenerate until "clean". The odds of a "bad word" in the string seems distant enough to allow that solution.
I agree that it would be useful to screen such output for the average user. It could provide humour to others.
@Sayer, your request for help is a bit out of scope from the original post, and c# is out of realm of expertise. The one thing that does stand out is only having a valid email address be sufficient to change a user's password. With that steup, a person would be able to reset other people's passwords if they knew their email address. Having a security question from the user's profile along with their email would be a better algorithm. As for the mechanics, Google stored procs in C# and see where that goes. The T-SQL would be pretty much the same I reckon.
Good luck with it.
|Wednesday, July 04, 2012 - 2:59:02 AM - sayer||Back To Top (18338)|
how to use this code to change passward in asp.net c#
when user forget passward go to page recaverypassward >
enter his email , select email from table_user
where [email protected]_email
if email exist
update from table_user set [email protected]
send nawpassword to user's email
please help me
|Monday, March 12, 2012 - 11:21:58 AM - Ralph Wilson||Back To Top (16347)|
This just showed up as a SQL Tip, so, even though I am coming late to the party, I am going to add a comment.
Therefore, you have some choices (besides just taking the attitude of, "Tough! These are randomly generated passwords, so deal with it!"). The first choice that comes to my mind is to identify and omit certain characters from the list used to create the passwords. The next choice that comes to mind is create a list of "unacceptable words" and scrub the generated random password against that list, generating a new password until one is generated that does not contain any "unacceptable words". A final choice that comes to mind would be to generatte the passwords in sections, e.g. 4 or 5 character strigns, with different sets of characters being used for each subsection, thus providing somewhat greater control on the overall character string being generated.
|Monday, February 27, 2012 - 2:45:43 PM - Scott C||Back To Top (16176)|
I've played around with similar scripts, this code shows three different versions. One difference is that I eliminated WHILE loops, and another is that I use random numbers to select from a string of allowable characters rather than using the CHAR() function. This avoids dealing with ASCII character code values, and if you use NVARCHAR lists you could throw in some Unicode characters.
The first segment generates a 10-char password with one call to NEWID().
The next piece is a view that creates random integers based on NEWID(). This allows its use in multi-row SELECT statements, where RAND() would return the same value for every row. As noted in the comments, this would be better as a function but NEWID() cannot be used in a function due to side effects. (The only point of this view is to avoid repeating the CAST(SUBSTRING(CAST())) expression in the following two procedures.)
The third fragment will generate a random password of the desired length from the given character set.
The final fragment allows specifying password complexity parameters, i.e. specifying counts individually for uppercase, lowercase, digits, and punctuation.
/* Simple random password generator
-- Generate the password from one call to NEWID()
-- Build the hash string by using 10 bytes of the GUID to select allowable chars.
SELECT @hash = STUFF(@hash, number, 1, SUBSTRING(@charset, (CAST(SUBSTRING(@newid, 14-number, 1) AS INT) % LEN(@charset)) + 1, 1))
SET @pwd = '' ;
SELECT @pwd = @pwd + SUBSTRING(@charset, (RandInt % LEN(@charset)) + 1, 1)
DECLARE @pwd VARCHAR(99) ;
SELECT @uc_chars = 'ABCDEFGHJKLMNPQRSTUVWXYZ',
SELECT @pwd = @pwd + chr
DECLARE @pwd VARCHAR(99) ;
|Saturday, November 26, 2011 - 11:23:11 PM - Brent Shaub||Back To Top (15211)|
NewID() with generate a new unique identifier that ranges from A-F and 0-9. The results are grouped into character strings of 8-4-4-4-12 each delimited with a hyphen. If your policy is to use only cap letters and digits, this strategy will work fine.
As for the randomness of newid() over random() used in the stored proc in the tip, I do not know which is more random. SQL Password's post above may have some insights, espcially that last link about cracking randomness.
|Saturday, November 26, 2011 - 4:45:46 PM - Jeff Moden||Back To Top (15210)|
I wonder how long it would take to spell out some pretty juicy swear words. ;-)
|Friday, November 25, 2011 - 10:22:36 AM - halford13||Back To Top (15207)|
What about select right(NEWID(),10)?
|Thursday, November 17, 2011 - 7:48:51 AM - Brent Shaub||Back To Top (15143)|
Hi again, zerteoc.
As you've discovered, passing in NULLs reaches the body of the stored proc. Omiting the params uses default values.
I whole-heartedly agree that error-free code is the objective; specifying values for NULL-valued parameters in the body of the proc will cover that scenario.
This stored proc will not be fed values entered by the user, by the programmer, yes. I envision a link that when clicked execs uspRandChars @len=<your security rules>, @min=<your security rules>, @range=<your security rules> etc. Parameters are optional if your security rules will always be consistent. However, the parameters enable flexibility and usage in multiple places in your environment / company where different rules exist. One could make an admin screen with the parameters' values stored in a system table for higher-level users with security rights could manage them--I appreciate code that maintains itself as business rules change.
That said, I agree with and appreciate your pointing out the error when passing in a NULL. It's useful to know also that NULL leads to an infinite loop, which can be harder on the server and performance than a fatal error which just stops.
I'm glad you're thinking about how the code works and poking it.
|Wednesday, November 16, 2011 - 11:59:39 AM - zberteoc||Back To Top (15135)|
I have to admit that I explecitely passed NULL to those parameters because in my mind I wanted to use the default values. I too didn't know this will override the default values. It is also a habit coming from using functions where you CANNOT omit parameters. HAving said that it is a good practice to make sure that you code is safe and doesn't create problems because users out there have an unlimited iimagination and could youse your code in any way possible. Besides here is form Book Online in regards to procedures parameters:
Security Note Validate all user input. Do not concatenate user input before validating it. Never execute a command constructed from unvalidated user input. For more information, see Validating User Input.
|Wednesday, November 16, 2011 - 5:42:19 AM - Brent Shaub||Back To Top (15129)|
Now I got bugs in my English. The last statement is meant to read: "in this case, possibly performance degradation for all users".
|Wednesday, November 16, 2011 - 5:40:32 AM - Brent Shaub||Back To Top (15128)|
I confirmed your statement is true. One way to work through that is having these statements before the while loop:
if @min is null set @min = 48
The purpose of the parameters with defaults is so when they aren't provided, the stored proc executes with 48 and 74. Passing in NULL explicitly doesn't make logical sense to me, and I'm uncertain what the intended outcome is. The ASCII characters have numeric values requiring a numeric starting point and range.
It's good to know that NULL-valued parameters override setting defaults in the argument list. If I make any systems where the end-user enters values into forms that are directly supplied into an argument list, the is null catch will save some headaches--in the case, possibly performance degration for all users.
|Tuesday, November 15, 2011 - 4:06:44 PM - zberteoc||Back To Top (15121)|
Procedure has a bug, If you execute:
declare @newpwd varchar(20)
exec [dbo].uspRandChars @len=14, @min=NULL, @range=NULL, @exclude='', @[email protected] out
get's into infinite cycle. I could debug it but is not my purpose here.
Otherwis, nice, short and clean code.
|Tuesday, November 15, 2011 - 10:52:45 AM - Jeremy Kadlec||Back To Top (15117)|
Thank you for the insight.
|Tuesday, November 15, 2011 - 10:14:55 AM - SQL Passwords||Back To Top (15115)|
You are correct; for a truly random password, the number of possibilities is^. Currently, we're at over 13 billion SHA1's/sec for a modern machine with modern GPU's, and I believe that's rising faster than doubling every 18 months, since we're still at the software refinement stage.
The math is simple - for truly random passwords, a couple characters longer is better than a larger character set (except with the larger character set means the attacker uses a much larger brute force space... and then a few characters longer is still better)
62 - Upper case, lower case, numeric (only valid if the password check is case sensitive... test it!)
94 - Optimistic upper case, lower case, numeric, and all keyboard symbols (which almost never works due to the need to escape characters for at least one language)
62^8 ~= 2E14
94^8 ~= 6E15
62^9 ~= 1E16
62^10 ~= 8E17 (Still only ~1.8 years for one machine with modern GPU's to search the entire space... note that 10% of fully random passwords are cracked in just over 2 months)
If your environment is a web site with a front end, I would suggest investigating PBKDF2 (Password Based Key Derivation Function 2) (http://www.ietf.org/rfc/rfc2898.txt) (.NET's SHA1 only implementation: http://msdn.microsoft.com/en-us/library/system.security.cryptography.rfc2898derivebytes.aspx) (A sample open source Java and C# implementation with variable hash functions: http://www.bouncycastle.org/).
A 62^10 password with perhaps 65,525 PBKDF2 SHA1 iterations might take 120 thousand years with a single machine... if computers never advance. Given the rate of GPU advancement, I'd expect the entire keyspace to be exhausted in less than 25 years; most that would be spent waiting for GPU's to become fast enough to start trying.
P.S. I don't believe you're using a cryptographically secure password generator; this is also a serious flaw (http://en.wikipedia.org/wiki/Random_number_generator_attack).
|Monday, November 14, 2011 - 8:17:01 PM - Brent Shaub||Back To Top (15111)|
Hi SQL Passwords
Regardless, thanks for a security check.
|Monday, November 14, 2011 - 11:13:30 AM - SQL Passwords||Back To Top (15109)|
1) SQL Server account passwords can be 128 characters long.
2) SQL Server account passwords can contain extended ASCII characters. Note that old-style ODBC accepts extended ASCII, and .NET often does not.
3) SQL Server passwords are salted, converted to Unicode, and hashed with a single SHA1 pass. A machine with modern GPU's can make over 13 billion offline attempts per second at guessing; this means that a fully random 8 character password with upper case, lower case, and numbers will have the entire password space tested in under 5 hours. A fully random 8 character password with upper case, lower case, numbers, and all keyboard symbols (not just the ones above number keys) will have the entire password space tested in less than 6 days.
And the conclusion:
If you want a password for a SQL Server account which can survive offline GPU based attacks, it had better be long and random both. If you're just going to be cutting and pasting it from a secure location (encrypted (2007) Office doc, Truecrypt container, etc.) anyway, why not make it _really_ long?
|Monday, November 14, 2011 - 9:29:04 AM - Brent Shaub||Back To Top (15106)|
For those with eagle eyes using SQL 2008,
set @len = @len - 1
can be shortened to
set @len -= 1
|Monday, November 14, 2011 - 9:23:34 AM - Brent Shaub||Back To Top (15105)|
Hi Santiago, I believe the error lies in the version of SQL you are using. In SQL 2008 R2, the "+=" syntax is supported. One way to test that is to drop and recreate the procedure with this line instead:
set @output = @output + @char
|Monday, November 14, 2011 - 8:58:53 AM - Santiago A. Quiles||Back To Top (15104)|
This is a great idea. It takes the pressure off of creating harden passwords on the fly. I found a minor syntax error during parsing,
set @output += @char (Msg 102, Level 15, State 1, Procedure USPRANDCHARS, Line 14 Incorrect syntax near '+'.)should be set @output = @char
Otherwise, kudos to the author.
|Monday, November 14, 2011 - 8:58:25 AM - Prabhakar||Back To Top (15103)|
|Monday, November 14, 2011 - 7:51:15 AM - DavidP||Back To Top (15093)|