Jump to content
Sign in to follow this  
njordur

Reset SQL Password without Privileged Account Credentials

Recommended Posts

Changed the default script so I could have the option of allowing the SQL user change his password by providing the old password. This way I don't have to define a Privileged Account with the password reset script.


<#

.SYNOPSIS

Connect to a Microsoft SQL server using the current SQL user, and change the password for a local SQL account.

.NOTES

Requires database connections on in-use Port to be allowed through Firewall

SQL user needs to be allowed to change own password

#>

function Set-SQLPassword2

{

	[CmdletBinding()]

	param (

		[String]$HostName,

		[String]$InstanceName,

		[String]$SQLPort,

		[String]$UserName,

		[String]$NewPassword,

		[String]$OldPassword

	)

	

#$SQLScript to be called once a database connection has been established. Add one command per line.

$SQLScript = @"

	ALTER LOGIN $UserName WITH PASSWORD = '$NewPassword' OLD_PASSWORD = '$OldPassword'

"@

	

	try

	{

		#Declare some connection string variables

		[String]$InstanceNameString = ''

		[String]$SQLPortString = ''

		

		#Construct the Instance Name section of the connection string if required

		if ($InstanceName -ne '') {

			$InstanceNameString = '\' + $InstanceName

		}

		

		#Construct the Port Number section of the connection string if required

		if ($SQLPort -ne '') {

			$SQLPortString = ',' + $SQLPort

		}

		

		$SQLConnection = New-Object System.Data.SqlClient.SqlConnection

		$SQLConnection.ConnectionString = "Server=" + $HostName + $InstanceNameString + $SQLPortString + ";User ID=" + $UserName + ";Password=" + $OldPassword + ";"

	

		$SQLConnection.Open()

		$SQLCommand = New-Object System.Data.SqlClient.SqlCommand($SQLScript, $SQLConnection)

		$SQLCommand.ExecuteScalar()

		$SQLConnection.Close()

		Write-Output "Success"

	}

	catch

	{

		switch -wildcard ($error[0].Exception.ToString().ToLower())

		{

			"*A network-related or instance-specific*" { Write-Output "Failed to execute script correctly against Host '$HostName' for the account '$UserName'. Please check SQL details are correct, and that a firewall is not blocking access - default Port is 1433."; break }

			"*because it does not exist or you do not have permission*" { Write-Output "Failed to execute script correctly against Host '$HostName' for the account '$UserName'. Error = Account does not exist or you do not have permission."; break }

			"*Login failed for user*" { Write-Output "Failed to connect to the Host '$HostName' to reset the password for the account '$UserName'. Please check the Privileged Account Credentials provided associated with the Password Reset script are correct"; break }

			#Add other wildcard matches here as required

			default { Write-Output "Failed to reset the password for the SQL account '$UserName' on Host '$HostName'. Error = " + $error[0].Exception }

		}

	}

}



#Make a call to the Set-SQLPassword2 function

Set-SQLPassword2 -HostName '[HostName]' -InstanceName '[SQLInstanceName]' -SQLPort '[DatabasePort]' -Username '[UserName]' -NewPassword '[NewPassword]' -OldPassword '[OldPassword]'

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×