Jump to content
Sign in to follow this  
support

Reset SQL Password (by Click Studios)

Recommended Posts

Script Name

Reset SQL Password

 

Description

Reset Microsoft SQL Account Password

 

System Requirements

  • Firewall to allow access

 

Successfully Tested Against

  • SQL Server 2005, 2008, 2012, 2014
  • And express editions

 

Failed Testing Against

  • None reported 

 
PowerShell Script

<#
.SYNOPSIS
Connect to a Microsoft SQL server using the supplied Privileged Account Credentials, and change the password for a local SQL account.
.NOTES
Requires database connections on in-use Port to be allowed through Firewall
#>
function Set-SQLPassword
{
	[CmdletBinding()]
	param (
		[String]$HostName,
		[String]$InstanceName,
		[String]$SQLPort,
		[String]$UserName,
		[String]$NewPassword,
		[String]$PrivilegedAccountUserName,
		[String]$PrivilegedAccountPassword
	)
	
#$SQLScript to be called once a database connection has been established. Add one command per line.
$SQLScript = @"
	ALTER LOGIN $UserName WITH PASSWORD = '$NewPassword'
"@
	
	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=" + $PrivilegedAccountUserName + ";Password=" + $PrivilegedAccountPassword + ";"
		$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-SQLPassword function
Set-SQLPassword -HostName '[HostName]' -InstanceName '[SQLInstanceName]' -SQLPort '[DatabasePort]' -Username '[UserName]' -NewPassword '[NewPassword]' -PrivilegedAccountUserName '[PrivilegedAccountUserName]' -PrivilegedAccountPassword '[PrivilegedAccountPassword]'

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  

×