2018-01-04

Generating Random Passwords In Bulk

Idera Software hosted another SQL Chat on Twitter yesterday. One of the questions that came up was about security:


Dave Mason - Random Password Generator - SQL Server - T-SQL - PowerShell

Something I've worked on recently is generating new passwords for SQL authentication logins. Not just one or two, but in bulk. Hundreds at a time. For the longest time, I used my own T-SQL code whenever a new login request arrived. I liked it so much, I used it to generate passwords for my personal use. Here are two variations, one with special characters and another with just alpha-numeric characters:

/*
 Simple routine to generate a random password consisting of 
 ASCII printable characters (character code 32-127).
*/
DECLARE @Pwd VARCHAR(MAX) = ''
DECLARE @Int SMALLINT
DECLARE @PasswordLength TINYINT = 25
DECLARE @Loop TINYINT = 1

WHILE @Loop <= @PasswordLength
BEGIN

 --NOTE: the first 32 characters in the ASCII-table are unprintable control 
 --codes and are used to control peripherals such as printers.
 SET @Int = 0

 --ASCII printable characters (character code 32-127)
 WHILE @Int < 32
 BEGIN
  SET @Int = CAST(RAND() * 127 AS INT)
 END

 --PRINT CHAR(@Int)
 SET @Pwd = @Pwd + CHAR(@Int)
 SET @Loop += 1
END

PRINT @Pwd

/*
 Simple routine to generate a random password consisting of 
 alpha-numeric characters (0-9, A-Z, a-z).
*/
DECLARE @Pwd VARCHAR(MAX) = ''
DECLARE @Int SMALLINT
DECLARE @PasswordLength TINYINT = 25
DECLARE @Loop TINYINT = 1

WHILE @Loop <= @PasswordLength
BEGIN

 --NOTE: the first 32 characters in the ASCII-table are unprintable control 
 --codes and are used to control peripherals such as printers.
 SET @Int = 0

 --ASCII printable characters (character code 32-127)
 WHILE @Int NOT BETWEEN 48 AND 57
  AND @Int NOT BETWEEN 65 AND 90
  AND @Int NOT BETWEEN 97 AND 122
 BEGIN
  SET @Int = CAST(RAND() * 123 AS INT)
 END

 --PRINT CHAR(@Int)
 SET @Pwd = @Pwd + CHAR(@Int)
 SET @Loop += 1
END

PRINT @Pwd

There's really nothing special there. On line 7 I specify how many characters long I want the password to be. I can run the code as many times as needed, or put it in a UDF or a loop if I want to get fancy.

Recently, though, I had the need to generate passwords outside of a T-SQL environment. I immediately went looking in the .NET Framework, and none to my surprise, I found something: the function System.Web.Security.Membership.GeneratePassword(). I did some initial testing in C#, then decided to proceed with a PowerShell script:

function Get-NewPassword([System.Int32]$length,`
    [System.Int32]$numberOfNonAlphanumericCharacters)
{
    $pwd = [System.Web.Security.Membership]::GeneratePassword(
        $length,$numberOfNonAlphanumericCharacters)

    #Validation: one UPPER case, one lower case, one digit.
    [bool]$valid = ($pwd -cmatch '[A-Z]') `
        -and ($pwd -cmatch '[a-z]') `
        -and ($pwd -cmatch '[0-9]')

    if (-Not $valid)
    {
        #Call function recursively, try again.
        $pwd = Get-NewPassword -length $length `
            -numberOfNonAlphanumericCharacters $numberOfNonAlphanumericCharacters
    }
    
    return $pwd
} 

# Prompt user.
Write-host "How many passwords do you want to generate?" -ForegroundColor Yellow 
[System.Int32]$passwordCount = Read-Host " (Enter a whole number) " 

# Prompt user.
Write-host "What is the password length?" -ForegroundColor Yellow 
[System.Int32]$passwordLength = Read-Host " (Enter a whole number) " 

$sb = [System.Text.StringBuilder]::new()

try
{
    For ($iLoop = 0; $iLoop -lt $passwordCount; $iLoop++)
    {
        $pwd = Get-NewPassword -length $passwordLength `
            -numberOfNonAlphanumericCharacters 2
        Write-Host $pwd -foregroundcolor "green"
        [void]$sb.AppendLine($pwd)
    }
}
catch
{
    Write-Error  $_
}

Set-Clipboard -Value $sb.ToString().TrimEnd()
Write-host "Passwords have been copied to the clipboard." -ForegroundColor Yellow  


The important part is the Get-NewPassword function. Within that function on lines 4-5, we see the call to System.Web.Security.Membership.GeneratePassword(). On lines 8-10, I decided to "validate" the generated password by checking for the presence of an upper case character, a lower case character, and a digit. This could easily be tweaked (or removed) to meet your password rules/requirements. Note the input parameter $numberOfNonAlphanumericCharacters guarantees a "special" character, as long as the parameter value is 1 or greater. Also note the recursion (lines 12-17) if the password isn't valid. The code below the function definition should be fairly easy to follow: it prompts the user twice, then uses a loop to generate some passwords.

Notes/Observations

Those T-SQL scripts I used to use for everything--I've given them up for the PowerShell version. I don't know if my T-SQL code has holes in it, or if my algorithm for randomness is air tight and can't be predicted in some manner (probably not). There is some peace of mind in using Microsoft's code instead of my own. But I might just be a bit paranoid. Mostly, I like the flexibility of the .NET Framework--it's easier to "validate" a password with PowerShell (or C#) than it is with T-SQL. That's just my take, though. I'll let you decide for yourself.

Also, the System.Web.Security.Membership.GeneratePassword() never seems to return a password with a single quote character (ASCII 39) or a semi-colon character (ASCII 59). That works out well for SQL authentication login passwords. As you know, single quote characters within T-SQL pose their own problems. And ODBC connection strings, which are bound to exist with just about any SQL app, are full of semi-colons. I've seen passwords with those characters that break apps, often with misleading error messages. Not fun.


SHARE

No comments :

Post a Comment

Subscribe