Jump to content
Sign in to follow this  
njordur

Reset SQL Password without Privileged Account Credentials and Contained Databases

Recommended Posts

Hi I added more functionality to the Reset SQL Password Script.

The feature I added is the ability to change users in contained databases. However since those users are not part of the logins in the database server the connectionstrings must contain the database name. For that I changed the Passwordlist and Renamed the Generic Field 1 to Database and put the database name there. If it's not used or missing it doesn't matter and the script will still run on the SQL accounts with logins or with privileged user. The only drawback here it seems is that the custom password validation script doesn't seem to be accepting fields from variables to validate the contained database users, like [GenericField1] (I even tried , I'm assuming it's a bug. The password reset with the script however updates the heartbeat field correctly.

 


<#

.SYNOPSIS

Connect to a Microsoft SQL server and change the password for a local SQL account.

.NOTES

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

Database variable is only used for contained databases

#>

function Set-SQLPassword

{

	[CmdletBinding()]

	param (

		[String]$HostName,

		[String]$InstanceName,

		[String]$SQLPort,

		[String]$UserName,

		[String]$NewPassword,

		[String]$OldPassword,

		[String]$PrivilegedAccountUserName,

		[String]$PrivilegedAccountPassword,

		[String]$Database

	)

	

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

$SQLScript1 = @"

	ALTER LOGIN $UserName WITH PASSWORD = '$NewPassword'

"@

	

$SQLScript2 = @"

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

"@



$SQLScript3 = @"

	ALTER User $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

		

		#Establish connection with Privileged Account if required, otherwise use own account to reset password

		if ($PrivilegedAccountUserName -ne '' -and $Database -eq '')

		{ $SQLConnection.ConnectionString = "Server=" + $HostName + $InstanceNameString + $SQLPortString + ";User ID=" + $PrivilegedAccountUserName + ";Password=" + $PrivilegedAccountPassword + ";" }

		#Used for Contained Databases

		elseif ($PrivilegedAccountUserName -eq '' -and $Database -ne '')

		{ $SQLConnection.ConnectionString = "Server=" + $HostName + $InstanceNameString + $SQLPortString + ";User ID=" + $UserName + ";Password=" + $OldPassword + ";Initial Catalog=" + $Database + ";" }	

		else

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

				

		$SQLConnection.Open()

		

		if ($PrivilegedAccountUserName -ne '' -and $Database -eq '')

		{ $SQLCommand = New-Object System.Data.SqlClient.SqlCommand($SQLScript1, $SQLConnection) }

		elseif ($PrivilegedAccountUserName -eq '' -and $Database -ne '')

		{ $SQLCommand = New-Object System.Data.SqlClient.SqlCommand($SQLScript3, $SQLConnection) }

		else

		{ $SQLCommand = New-Object System.Data.SqlClient.SqlCommand($SQLScript2, $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 }

            "*The password of the account must be changed*" { Write-Output "Failed to validate the password for the SQL account '$UserName' on Host '$HostName'. Password has expired."; break }			

            "*The account is disabled*" { Write-Output "Failed to validate the password for the SQL account '$UserName' on Host '$HostName'. Account is disabled."; break }			

			"*Login failed for user*" { Write-Output "Failed to validate the password for the SQL account '$UserName' on Host '$HostName'. UserName or Password is incorrect."; break }

			#Add other wildcard matches here as required

			default { Write-Output "Failed to validate 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]' -OldPassword '[OldPassword]' -NewPassword '[NewPassword]' -PrivilegedAccountUserName '[PrivilegedAccountUserName]' -PrivilegedAccountPassword '[PrivilegedAccountPassword]' -Database '[GenericField1]'

 

2017-01-20 15_07_51-sqlcontained.jpg

2017-01-20 15_22_21-Database User - sqlcontained.jpg

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  

×