Password Generation Using PowerShell

While using Windows Authenticated Logins easily falls into security best practices, beginning with SQL Server 2005 installed on Windows 2003 servers, SQL authenticated logins became easier to manage to enterprise security guidelines. 

To ensure that SQL Authenticated logins follow the same password policies as defined for Windows for password complexity, account lockout, and password expiration when creating SQL Logins using T-SQL, DBAs should specify the CHECK_POLICY=ON option and the CHECK_EXPIRATION=ON option of the CREATE LOGIN command. 

CREATE LOGIN <mySQLLogin>

     WITH PASSWORD = <enterReallyStrongPassword123!>,

     CHECK_EXPIRATION = ON,

     CHECK_POLICY = ON

If creating the login using SSMS, then be sure the options “Enforce password policy” and “Enforce password expiration” are checked. Of course, if you use the “Enforce password expiration” option, users must have a mechanism for changing their password. 

Back in the SQL 2000 days, I wrote a lengthy VBScript to generate a random password to use when changing the sa login (which should be disabled in SQL 2005 and beyond).  However, the basics for this task (generating the password) can now be performed in two simple lines of PowerShell!  And since DBAs will need to set a complex password when creating SQL Authenticated Logins, these two lines can come in handy to ensure a random password is generated for the login.  And, of course, you can use it next time you need to create a new  password for any other account, too!

[Reflection.Assembly]::LoadWithPartialName(”System.Web”) | Out-Null

[System.Web.Security.Membership]::GeneratePassword(16,2) 

The above 2 lines will load the necessary .NET Framework assembly containing the System.Web namespace.  The GeneratePassword method with the specified parameters will generate a random password 16 characters long, with at least 2 non-alphanumeric characters.

Put the two lines above into file with a filetype of .ps1 – for example, Gen-Password.ps1.  You can then run it by typing from a command prompt: powershell <PathToScript>\Gen-Password.ps1.  Occasionally, the password generated will contain a character not allowed in SQL Server passwords, so be sure to double-check the value generated.

About these ads