SQL Server stored procedure to generate random passwords


By:   |   Updated: 2011-11-14   |   Comments (31)   |   Related: More > T-SQL

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 :;<=>[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:

sample random password output

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.


Last Updated: 2011-11-14


get scripts

next tip button



About the author
MSSQLTips author Brent Shaub Brent Shaub has been creating, renovating and administering databases since 1998.

View all my tips




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
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, August 04, 2016 - 10:02:18 AM - Jeff Moden Back To Top

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

Same procedure with more explanation:

CREATE PROC dbo.uspRandChars
    -- Set the length for the generated password.  12 is the default value if no argument is provided.
    @len INT = 12,

    -- Define the allowable password characters. This could include additional punctuation or Unicode chars.
    -- The default set of characters exclude ones that can be misread (0 and O; 1, l, and I).
    @Charset NVARCHAR(256) = '23456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz.,#$%^&*-+_/=',

    @output NVARCHAR(50) OUTPUT
AS
    SET NOCOUNT ON;
    SET @output = '';
/*
CRYPT_GEN_RANDOM(8)           Generate 8 random bytes
CAST(... AS BIGINT)           Convert them to a BIGINT to allow arithmetic operations
... / 9223372036854775808.5   Divide by the largest possible value (2^63) to get a number in the range -1.0 < n < 1.0
                              The BIGINT value will be implicitly converted to NUMERIC(38,18) for maximum precision
ABS(...)                      Get the absolute value resulting in a number in the range 0 <= n < 1.0
... * LEN(@Charset)           Multiply by the length of the @Charset string resulting in 0 <= n < Length
FLOOR(...)                    Convert to integer using FLOOR to get a precise range of 0 <= n < Length
... + 1                       Final result is a random index (1..Length) to pick one character from @Charset
SELECT @output += SUBSTRING(@Charset, ..., 1)    Pick that one character and append it to the password 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;
/*
The table master.dbo.spt_values has nothing to do with the result, it is just used to make the SELECT statement run the desired number of times.

SELECT * FROM master.dbo.spt_values                                            A table in the master database that has a variety of constant definitions    
SELECT number FROM master.dbo.spt_values WHERE type = 'P'                      Returns a number sequence 0..2047
SELECT number FROM master.dbo.spt_values WHERE type = 'P' AND number < @len    Returns a number sequence [email protected]

The query could be rewritten easily (and perhaps more clearly) to use TOP() to determine the password length.

    SELECT TOP(@len) @output += SUBSTRING(@Charset, FLOOR(ABS(CAST(CRYPT_GEN_RANDOM(8) AS BIGINT) / 9223372036854775808.5) * LEN(@Charset)) + 1, 1)
    FROM sys.objects;
*/

 


Saturday, April 09, 2016 - 2:56:49 PM - Sobhan Dutta Back To Top

Hi Scott,

GREAT CODE!

 

Could you please explain the script in details. You can email ([email protected]) me as well.

Thanks in advance.

 

With regards,

Sobhan Dutta


Thursday, July 30, 2015 - 2:56:04 PM - Scott Back To Top

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

I am using this script and it's just great . . . . 

I have just changed my version of sql though to MSsql 2014 

I use this stored procedure from within another procedure when adding new customers and give them a password.
Suddenly the password that is getting saved is 'password' every time.

Any ideas why ? 


Friday, June 12, 2015 - 6:29:03 AM - Andile Back To Top

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

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

Thanks,

Very Nice SP for Random Number generation.


Friday, June 21, 2013 - 3:26:09 PM - Max Montenegro Back To Top

Great Job!! works excelent!!! Thank you


Monday, September 24, 2012 - 7:06:27 AM - Chami Back To Top

Good post - it really helped me !

Thanks all..

 


Sunday, July 08, 2012 - 9:11:51 AM - Brent Shaub Back To Top

@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

thanks,

how to use this code to change passward in asp.net c#

my idea,

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]

and

send nawpassword to user's email

please help me

regardes. 

 

 


Monday, March 12, 2012 - 11:21:58 AM - Ralph Wilson Back To Top

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.

In generating random passwords, there is a finite probability that certain socially unacceptable words/phrases will be generated as embedded parts of the passwords.  (I had to help deal with a variation of that problem not too long ago with regard to "manually" generated identifiers for files that were generated using the 26 letters of the alphabet and the numerals 0 through 9.)  While the passwords may be perfectly acceptable from both the stand point of the rules regarding the validity of the passwords and any automated consumption/use of the passwords, humans may be offended by the phrases, even if embedded in the middle of a password.

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

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
*/
-- Allowable chars are 2-9, A-Z (except I and O), and a-z (except l)
-- Math purists may point out that the character set length should be a power of two (32, 64, 128) so each char
-- is equally probable, otherwise a subset (the first 256 % LEN chars) are slightly more likely.  The default
-- set is 64 chars long and includes some punctuation, but excludes characters that might be misread ( 1 I l and 0 O ).
DECLARE @charset VARCHAR(256)
SET @charset = 'ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz23456789$-#+_&;'
-- Generate the password from one call to NEWID()
DECLARE @newid BINARY(16)
SET @newid = NEWID()
-- Build the hash string by using 10 bytes of the GUID to select allowable chars.
-- The binary bytes are not used in CHAR() functions, so no ASCII value arithmetic is needed.
-- Each byte is used to select a character from @charset, which could be NVARCHAR if you wanted to include non-ASCII characters
DECLARE @hash CHAR(10)
SET @hash = ''
SELECT @hash = STUFF(@hash, number, 1, SUBSTRING(@charset, (CAST(SUBSTRING(@newid, 14-number, 1) AS INT) % LEN(@charset)) + 1, 1))
FROM (      SELECT number
            FROM master.dbo.spt_values
            WHERE type='P' AND number BETWEEN 1 AND 10 ) x
PRINT @hash
-----------------------------------------------------------------------------------------------------------------------------
/*  Random integer generating view
    Using NEWID() rather than RAND() allows set-based queries instead of one-char-at-a-time WHILE loops.
    A view can be used to extract an integer from the NEWID() uniqueidentifier.
   (A function would be better, but NEWID() is not legal in functions due to side effects.)
*/
GO
-- Return a positive 32-bit integer taken from bytes 12-15 of a NEWID() uniqueidentifier
CREATE VIEW dbo.Rand32 AS
SELECT RandInt =  CAST(SUBSTRING(CAST(NEWID() AS BINARY(16)), 12, 4) AS INT) & 0x7FFFFFFF
GO
-----------------------------------------------------------------------------------------------------------------------------
/*  This procedure generates passwords up to 50 characters long, taken from the character set of your choosing.
    Math purists may point out that the character set length should be a power of two (32, 64, 128) so each char
    is equally probable, otherwise a subset (the first 256 % LEN chars) are slightly more likely.  The default
    set is 64 chars long and includes some punctuation, but excludes characters that might be misread ( 1 I l and 0 O ).
*/
GO
-- Create a random password
CREATE PROC dbo.GenerateRandomPassword
    @len        INT = 10,
    @charset    VARCHAR(256) = 'ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz23456789$-#+_&;',
    @pwd        VARCHAR(50) OUTPUT
AS
SET @pwd = '' ;
SELECT @pwd = @pwd + SUBSTRING(@charset, (RandInt % LEN(@charset)) + 1, 1)
FROM master.dbo.spt_values
CROSS JOIN dbo.Rand32
WHERE type = N'P' AND number BETWEEN 1 AND @len ;
GO
DECLARE @pwd VARCHAR(99) ;
EXEC dbo.GenerateRandomPassword @pwd = @pwd OUTPUT ; PRINT @pwd
EXEC dbo.GenerateRandomPassword @len = 15, @pwd = @pwd OUTPUT ; PRINT @pwd
EXEC dbo.GenerateRandomPassword @len = 8, @charset = '-=[]\;,./abcdefghijkmnopqrstuvwxyz23456789', @pwd = @pwd OUTPUT ; PRINT @pwd

-----------------------------------------------------------------------------------------------------------------------------
/*  This procedure generates random passwords that meet complexity requirements for different classes of characters.
    The individual character sets are not included as parameters, but could be if you feel that is necessary.
*/
GO
-- Create passwords that meet specific complexity rules
-- Parameters specify how many upper case, lower case, numeric, and punctuation characters to include
-- Characters are chosen randomly from each group, and mixed together in a random order
CREATE PROC dbo.GenerateComplexPassword
    @uc     INT = 4,
    @lc     INT = 4,
    @num    INT = 3,
    @punc   INT = 2,
    @pwd    VARCHAR(50) OUTPUT
AS
SET NOCOUNT ON ;
DECLARE @uc_chars VARCHAR(50),
        @lc_chars VARCHAR(50),
        @num_chars VARCHAR(50),
        @punc_chars VARCHAR(50) ;
SELECT  @uc_chars = 'ABCDEFGHJKLMNPQRSTUVWXYZ',
        @lc_chars = 'abcdefghijkmnopqrstuvwxyz',
        @num_chars = '23456789',
        @punc_chars = '`[email protected]#$%^&*()_-+={[}]\:;"''<,>.?/',
        @pwd = '' ;
SELECT  @pwd = @pwd + chr
FROM (
    SELECT  chr = SUBSTRING(@uc_chars, (RandInt % LEN(@uc_chars)) + 1, 1),
            ord = NEWID()
    FROM master.dbo.spt_values
    CROSS JOIN dbo.Rand32
    WHERE type = N'P' AND number BETWEEN 1 AND @uc
    UNION ALL
    SELECT  chr = SUBSTRING(@lc_chars, (RandInt % LEN(@lc_chars)) + 1, 1),
            ord = NEWID()
    FROM master.dbo.spt_values
    CROSS JOIN dbo.Rand32
    WHERE type = N'P' AND number BETWEEN 1 AND @lc
    UNION ALL
    SELECT  chr = SUBSTRING(@num_chars, (RandInt % LEN(@num_chars)) + 1, 1),
            ord = NEWID()
    FROM master.dbo.spt_values
    CROSS JOIN dbo.Rand32
    WHERE type = N'P' AND number BETWEEN 1 AND @num
    UNION ALL
    SELECT  chr = SUBSTRING(@punc_chars, (RandInt % LEN(@punc_chars)) + 1, 1),
            ord = NEWID()
    FROM master.dbo.spt_values
    CROSS JOIN dbo.Rand32
    WHERE type = N'P' AND number BETWEEN 1 AND @punc
) c
ORDER BY ord
GO
DECLARE @pwd VARCHAR(99) ;
EXEC dbo.GenerateComplexPassword @pwd = @pwd OUTPUT ; PRINT @pwd
EXEC dbo.GenerateComplexPassword @uc = 8, @lc = 2, @num = 0, @punc = 0, @pwd = @pwd OUTPUT ; PRINT @pwd
EXEC dbo.GenerateComplexPassword @uc = 1, @lc = 8, @num = 1, @punc = 1, @pwd = @pwd OUTPUT ; PRINT @pwd

Saturday, November 26, 2011 - 11:23:11 PM - Brent Shaub Back To Top

Hi Halford,

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.

Brent


Saturday, November 26, 2011 - 4:45:46 PM - Jeff Moden Back To Top

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

What about select right(NEWID(),10)?


Thursday, November 17, 2011 - 7:48:51 AM - Brent Shaub Back To Top

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

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

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

Thanks zberteoc.

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
 if @range is null set @range = 74

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

Procedure has a bug, If you execute:

 

declare @newpwd varchar(20)

exec [dbo].uspRandChars @len=14, @min=NULL, @range=NULL, @exclude='', @[email protected] out

select @newpwd

 

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

SQL Passwords,

Thank you for the insight.

Thank you,
Jeremy Kadlec


Tuesday, November 15, 2011 - 10:14:55 AM - SQL Passwords Back To Top

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

Hi SQL Passwords

Using this stored proc, the password can be any number of characters by extending the varchar's length definition and len parameter.  I'm sure you're aware of this based on the details in your post.

Whether a password is randomly generated or not, the GPU detection you mention will still decode it in the same timeframe based on its length and number of characters used.  It is wonderful to know the rise from 5 hours to less then 144 just from the additional characters.  My guess to the algorithm is number of possible chars ^ length.


The environment where I used the above stored proc was to change a user's web-based password.  Their passwords were stored in a database while the system used one Cold Fusion ODBC for writing to it. 

Regardless, thanks for a security check.


Monday, November 14, 2011 - 11:13:30 AM - SQL Passwords Back To Top

Three facts:

  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

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

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

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

 

cool.

 


Monday, November 14, 2011 - 7:51:15 AM - DavidP Back To Top

Great script

THX



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools