Frank, the DBA, has been asked to create a series of passwords that will be used to protect a collection of symmetric keys. These keys will in turn be used to protect some sensitive data in a database.

Frank knows that the expectation is that these passwords will need to be strong passwords. Using the names of his cat's new born litter will not suffice. He made that mistake at his last job. Reviewing his notes to remind himself of the definition of a strong password he identifies the following:

1) The length of the password must be 8 characters or greater.
2) Does not include a series of letters that make a word or phrase.
    For example: "Password", "BettyIsHot", "FrankRocks".
3) The series of passwords that will be created are not sequential.
    For example:  "Password1", "Password2", "Password3".
4) Contains a variety of characters including uppercase letters, lowercase letters,
    numeric values and special characters.

With a sigh, Frank makes his first attempt at his series of passwords. It wasn't long until his human nature creped in and his so-called random series of characters didn't appear as random they should be. The injection of significant dates (08131969, 12071941 and 05291453), his favorite bands (U2, UB40 and B52s) and pseudo-words (M0V31T, R4d1c@L and P@sSw0rd) kept sneaking in.

After a few minutes of contemplation, a bag of beef jerky and a bottle of Yoo-Hoo, inspiration descended upon Frank like a Marcel Duchamp painting. He opened Management Studio and began typing furiously. The result was the following T-SQL script that assembles a series of characters in a loop that ultimately produces a strong password:

-- Variables
DECLARE @PassLen int; 
DECLARE @Pass varchar(50);
DECLARE @LoopCt int;
DECLARE @Rnd int;

-- Password length
SET @PassLen = 10;
-- Starting value of password
SET @Pass = '';
-- Starting value of loop
SET @LoopCt = 1;

-- Loop to generate the password 
WHILE @LoopCt <= @PassLen
    BEGIN
        -- Generate a random number to select a character
        SET @Rnd = Convert(int,(1 + RAND() * (9-1)));

        -- Build the password using the selected character
        SELECT @Pass = @Pass +
            CASE 
                -- This option is weighted
                WHEN @Rnd >=1 AND @Rnd <=3 THEN
                    -- a-z  
                   CHAR(Convert(int,(97 + RAND() * (122-97))))
                 -- This option is weighted
                WHEN @Rnd >=4 AND @Rnd <=6 THEN
                    -- A-Z 
                   CHAR(Convert(int,(65 + RAND() * (90-65))))
                WHEN @Rnd = 7 THEN
                    -- 0-9 
                    CHAR(Convert(int,(48 + RAND() * (57-48))))
                ELSE
                    -- #$%& (Special Character)
                    CHAR(Convert(int,(35 + RAND() * (38-35))))
             END

       -- Advance the loop    
        SET @LoopCt = @LoopCt + 1;
    END            

-- Return the password
SELECT @Pass;

GO

In his code, Frank thought it wise to weight the uppercase and lowercase letters so that they would be selected more often than the numeric and special character to prevent the occurrence of passwords that look like "1234567%9#", "#$%&%$#%$1". A sampling of the passwords that Frank generated through this code are: DqOC0bPkqF, Q3t6%mQk%l, UAFdK%gvvq and Rrs#qm%#f$.

After a few executions of this code Frank completed his task at hand. He handed his list of generated passwords to Betty, the Junior DBA, to implement. The data was once again safe. Betty was happy to no longer see Frank's lame flirting attempts through passwords like "W1llUg02th3M0v1eZw1thM3?".