Generate Strong Passwords for Standard SQL Logins

,

This script will generate random Strong Passwords for all Standard SQL Logins for a specified SQL Server.  It currently does ALL standard logins, including SA, so you would want to add to the WHERE clause in the SELECT_LOGINS constant if you want to limit the logins.  The password generated will be 10 - 15 characters in length.  You can edit the GenPasswd function in the script to customize the generated password to fit your requirements.
Just save the entire script to a file with a .VBS extension.
Execute the script with a /? argument to see usage information.

'
' NAME: STRONG PASSWORD
'
' AUTHOR: Steve Harrell
' DATE  : 3/25/2002
'
' COMMENT: This code provided as is, no warranty is implied or provided.
'          Bear in mind that this script will change the passwords of all
'          Standard SQL Logins (including SA!) AND write the new password
'          to the log file for to enable the DBA to disseminate them to
'          the appropriate users.
'          USE AT YOUR OWN RISK.
'
'Date		Alias		Comment
'----		-----		-------------------------------------
'3/25/2002	sharrell	Beta Release
'
'==========================================================================


Option Explicit

	'On error resume next

	'------------------------------------------------------
	' Global Variables 
	'------------------------------------------------------
	Dim WSHShell, WSHNetwork, WSHFile
	Dim ComputerName     ' This is local computer name
	Dim sPassword
	Dim SQLServerName   ' SQL Server name can be named instance.
	Dim InstanceName    ' Instance name. Eg. i1 if SQLServeName is mysql\i1
	Dim sConnectionString ' Connection string of the SQL Server
	Dim oLogFile	' Log file object
	Dim sOutputFileName	' Log filename with passwords
	Const SELECT_LOGINS = "SELECT Name FROM Syslogins WHERE isntname=0 Order by Name"

	'------------------------------------------------------
	' Create Global Objects
	'------------------------------------------------------
	Set WSHNetwork = WScript.CreateObject("WScript.Network")
	Set WSHShell = WScript.CreateObject("WScript.shell")
	Set WSHFile = Wscript.CreateObject("Scripting.FileSystemObject")
	If err.Number <> 0 then 
		Wscript.Echo "Error occurred: (" & Err.Number & ") " & Err.Description
		Wscript.Quit Err.number 
	End If
	' create log file
	sOutputFileName	= "changepassword.log"
	Set oLogFile = WSHFile.CreateTextFile(sOutputFileName)

	PreSetup
	ParseArgs
	SetPasswd
	WScript.Quit 0

' *********************************************************
'
' Function:	LogMessage
'
' Purpose:	Writes message to the log
'
' Input:	sMessage - the message
'
' Output:	None
'
' *********************************************************

Sub LogMessage(sMessage)
	oLogFile.WriteLine(sMessage)
End sub


'------------------------------------------------------
'Sub: SetPasswd()
'
'This sub will set passwords for standard logins
'
'------------------------------------------------------
Sub SetPasswd()
    ON Error Resume Next
    Dim strSQL
    Dim oConnection
    Dim oRS
    Dim sLogin

    '------------------------------------------------------
    ' Set cscript if not set. 
    '------------------------------------------------------
    Config_Preconfig

    set oConnection = Wscript.CreateObject("ADODB.Connection")
    oConnection.ConnectionString = sConnectionString
    If err.Number <> 0 Then err.Clear	
    oConnection.open
    If err.Number <> 0 then
	LogMessage(err.Description)
	WScript.Quit
    End If

    Set oRS = oConnection.Execute(SELECT_LOGINS)
    If err.Number <> 0 then
	LogMessage(err.Description)
	WScript.Quit
    End If

    Do While Not oRS.EOF
	sLogin = UCase(oRS.Fields(0).Value)
	sPassword = ""
    	' Call GenPasswd() function to generate a random password
    	sPassword = GenPasswd
	strSQL = "exec sp_password NULL, [" & sPassword & "], '" & sLogin & "'"

	' Execute sp_password to set the password
	oConnection.Execute strSQL, , 128      ' adExecuteNoRecords

	LogMessage(sLogin & Chr(9) & sPassword)

	oRS.MoveNext
    Loop

    oRS.Close
    oConnection.close
    Set oRS = Nothing
    Set oConnection = Nothing
    Err.Clear
End Sub

Sub PreSetup()

	'------------------------------------------------------
	' Obtain Environment values
	'------------------------------------------------------
	ComputerName = WshShell.ExpandEnvironmentStrings("%COMPUTERNAME%")

	'------------------------------------------------------
	' Default setting if not specified
	'------------------------------------------------------
	sPassword = ""
	SQLServerName = ComputerName 

End Sub

'------------------------------------------------------
'Func: Config_Preconfig()
'------------------------------------------------------
Function Config_Preconfig()
	
	Dim arg, temp

	on error resume next
	Set Wshshell = Wscript.CreateObject("Wscript.shell")
	'Sets up Vbscript to always run in command window ######
	If instr(1, wscript.fullname, "cscript.exe", 1) = 0 then
		if err.number <> 0 then err.clear
		temp = wshshell.Run("cmd /c ""cscript //h:cscript //nologo //s 1>nul 2>nul""", 0, true)
		temp = MsgBox ("The script has changed the default output of Windows Scripting Host to the command prompt." & vbCrLf & "This pop up is normal, just re-run the script!", 0, "WSH default changed to cscript.")
		config_preconfig = 1
		wscript.quit 1
		exit function
	end if

	config_preconfig = 0

End Function 'Set Script Host

'------------------------------------------------------
'
' Function GenRan(lowerbound, upperbound)
' Description:  Generate a random number between lowerbound and upperbound
'       Reseed everytime calling this function.
' Return:   a random integer
'
'------------------------------------------------------
Function GenRan(lowerbound, upperbound)
    Randomize
    GenRan = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
End Function


'------------------------------------------------------
'
' Function GenPasswd()
' Description:  Generate a random password between the password
'       	length 10 and 15
' Return:   a random password string
'
'------------------------------------------------------
Function GenPasswd()
    Dim passwdLen
    Dim i, r
    Dim strPasswd

    wscript.echo "Generating a random and strong password..."
    strPasswd = ""

    passwdLen = GenRan(10, 15)

    For i = 1 To passwdLen
        r = GenRan(33, 126)
        strPasswd = strPasswd & Chr(r)
    Next
    
    ' SA password cannot include "[" or "]"
    strPasswd = Replace(strPasswd, "[", "$")
    strPasswd = Replace(strPasswd, "]", "?")
    GenPasswd = strPasswd
End Function

'------------------------------------------------------
'Sub ParseArgs()
'------------------------------------------------------
Sub ParseArgs()
	Dim oArgs

	On Error Resume Next

	Set oArgs = Wscript.Arguments
	If oArgs.count > 0 Then
		PreSetup
		If oArgs(0) = "/?" or oArgs(0) = "-?" then
			ShowUsage()
			WScript.Quit 1
		Else
			SQLServerName = oArgs(0)
		End If
	End If

	' Setup connection string
	sConnectionString = "driver={SQL Server};server=" & SQLServerName & ";TRUSTED_CONNECTION=YES;database=master"
End Sub

'------------------------------------------------------
'Function IsValidArg()
'------------------------------------------------------
Function IsValidArg(str)
	Dim s 

	s = Left(str, 1)
	If s = " " or s = "/" or s = "-"  or s = "" then
	   IsValidArg = False
        Else
	   IsValidArg = True
        End If
End Function

'------------------------------------------------------
'Sub: ShowUsage()
'------------------------------------------------------
Sub ShowUsage()
  On Error Resume Next
  Dim begdate
  Dim begtime
  Dim sHeader
  Dim strScriptName

  begtime = Time
  begdate = Date

  sHeader = vbCrLf & _
    "          _______________________________________________" & vbCrLf & _
    "         |                                               |" & vbCrLf & _
    "         |                                               |" & vbCrLf & _
    "         |     SQL Server Strong Password Script         |" & vbCrLf & _
    "         |                                               |" & vbCrLf & _
    "         |_______________________________________________|"

  sHeader = sHeader + vbCrLf + "         \\" & computername & " - " & begdate & " " & begtime
  strScriptName = Replace(Wscript.scriptname, ".vbs", "")

	WScript.Echo sHeader & vbCrLf & vbCrLf
	WScript.Echo "  Name: " & strScriptName
	Wscript.Echo "  Usage: " & strScriptName & " [SQLServerName][\InstanceName]"
        Wscript.Echo "  Description: " & strScriptName & " is used to " & _
              "change the Password of Standard Logins" 
	Wscript.Echo "               to a random strong Password."
              
	Wscript.Echo vbCrLf + "Example: " & strScriptName & " myserve"
   	Wscript.Echo vbCrLf + "Example: " & strScriptName & " myserve\VSdotNET"
   	Wscript.Echo vbCrLf + "Example: " & strScriptName
   	Wscript.Echo vbCrLf + "Example: " & strScriptName & " /?"
	Wscript.Echo vbCrLf + "Note: If you don't specify a SQL Server Name" 
	Wscript.Echo "      the local machine name will be assumed."
	Wscript.Echo "      In that case, only the default instance of"
	Wscript.Echo "      SQL Server will have the Passwords set."
	Wscript.Echo vbCrLf + "      You must be a member of the SysAdmin"
	Wscript.Echo "      Server Role to run this script."

	Wscript.Echo vbCrLf + "Done"
	wscript.quit 1
end sub

Rate

Share

Share

Rate