Sql password generator function

Strong Password Generation can be a chore

The following sql server 2008 password generator was inspired in part a post regarding a password complexity Requirement. According to the post,  seven characters or more were suggested. A list of acceptable symbol characters were provided. Within the article, a mix of upper, lower, numeric and a group of symbols were recommended.

Most password generators rely in some fashion on sql random number functions. In the below function 15 characters are generated using a random number generator function in SQL Server 2008. This function also depends on a view ‘Rand_v’ which merely selects a random number using the rand() function.

To create such a view in Sql Server you could execute this:

create View [dbo].[Rand_v]
as
select RAND() as RandNum

The sql server random password function listed below does not address the difficulty in remembering highly randomized text for humans. In theory, one could construct an elaborate ‘memory palace’ to remember such a series of text. However that requires good concentration and some practice to master.

Another possible negative side effect of highly randomized text, is that persons would be even more prone to writing down the password (which is not recommended).

Still, for purposes of generating an initial random password that a user must later reset, this function may be helpful.

CREATE function [dbo].[fun_GetRandomGDPass]() RETURNS varchar(81)

as

BEGIN

DECLARE @counter smallint,

@charsToPick Varchar(81) = ‘!@#%*)(_-=+|[]{}.,/abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789′

,@pos int=0,

@retVal varchar(81)=”;

SET @counter = 0;

WHILE @counter < 15

BEGIN

SELECT @pos = round(1 + RandNum *(80),0) from Rand_v

select @retVal = @retVal + SUBSTRING(@charsToPick,@pos,1)

SET @counter = @counter + 1

END

return @retVal

END

Evaluation of the above password generator:

Since the returned value is randomly chosen there is an extremely small chance that a returned value would have a ‘weak key'; for example all lower case letters.

Also, There is nothing in this function to ensure it is a strong password by requiring numbers and symbols. One might accomplish this by polling the characters to ensure some minimum number of characters are symbols for example.

A stronger sql password function would ensure at least one character was numeric, one was a symbol, one was upper case and one was lower case. An example of one such implementation of a random password generator is here.

The function above has the length of characters hard-coded in. Ideally, the user would be able to change this.

Additionally, there is the problem of ensuring uniqueness. There is no restriction preventing a password from being generated twice. The chance of this is exceedingly small however it is theoretically possible.

Despite these problems, the above t-sql password function is a good starting point.

The above Sql password generator can be used as follows… within SQL Server Management Studio, open a new query and type in this…

select dbo.fun_GetRandomGDPass()

The above function could be used as the basis for some useful programs, not only as a password generator. In another post I described other uses for random text generators here.