gistfile1.txt

Function Reset-SqlSaPassword {
	<# 
	 .SYNOPSIS 
	  This script will allow administrators to regain access to SQL Servers in the event that passwords or access was lost.
	  
	  Windows Server administrator access is required.
	  
	 .DESCRIPTION
	  
	 
	 
	  THIS CODE IS PROVIDED "AS IS", WITH NO WARRANTIES.
		
	 .PARAMETER SqlServer
	  The SQL Server instance to reset.
	  
	 .PARAMETER Login
	  By default, the Login parameter is "sa" but any other SQL or Windows account can be specified. If a login does not
	  currently exist, it will be added.
	  
	 .PARAMETER IsClustered
	  Specify if the SQL instance is clustered. 
	  
	  Note! The wrong paramters can actually prevent SQL Server from starting (though this can easily be addressed in SQL 
	  Configuration Manager -> Startup Params). Because of this risk, Reset-SqlSaPassword checks to see if the Cluster 
	  service is running on the remote server, and prompts for additional confirmation if -isClustered is not specified.
	
	 .NOTES 
	  Author: Chrissy LeMaire
	  Requires: PowerShell Version 3.0, SQL Server SMO, Admin access to server (not SQL Services)
	  DateUpdated: 2015-June-05
	  Version: 0.8			

	 .LINK 
	 https://gallery.technet.microsoft.com/scriptcenter/Reset-SQL-SA-Password-15fb488d

	 .EXAMPLE   
	 Reset-SqlSaPassword -SqlServer sqlservera\sqlexpress -Login base\administrator

	  
	 .EXAMPLE   
	 Reset-SqlSaPassword -SqlServer sqlcluster -IsClustered
	 

	 #> 
			[CmdletBinding()]
			param(
				[Parameter(Mandatory = $true)]
				[string]$SqlServer,
				[string]$Login = "sa",
				[switch]$IsClustered
			)
	BEGIN {

	Function Start-WmiSqlService {
	 <#
		.SYNOPSIS
		Restarting SQL Server via SQL's WMI objects returns $true too quickly. 
		This function waits until the SQL service has started or a timeout occurs.

		.EXAMPLE
		$result = Start-WmiSqlService $sqlservice $clustered
		
		.OUTPUTS
		 $true or $false
				
	 #>
			[CmdletBinding()]
			param(
				[Parameter(Mandatory = $true)]
				[ValidateNotNullOrEmpty()]
				[Microsoft.SqlServer.Management.Smo.Wmi.WmiSmoObject]$service	
			)

		$timeout = new-timespan -Seconds 60
		$sw = [diagnostics.stopwatch]::StartNew()

		do {
			$service.Refresh()
			try { $service.Start() } 
			catch { 
				Write-Warning "Having trouble starting the SQL service. Let's try again." }
				Start-Sleep 2
		} 
		until ($service.ServiceState -eq "Running" -or $sw.elapsed -gt $timeout) 
		$status = $service.ServiceState
		
		if ($status -eq "Running") { return $true }
		else { return $false }
	}

	Function Stop-WmiSqlService {
	 <#
		.SYNOPSIS
		Restarting SQL Server via SQL's WMI objects returns $true too quickly. 
		This function waits until the SQL service has stopped or a timeout occurs.

		.EXAMPLE
		$result = Stop-WmiSqlService $sqlservice
		
		.OUTPUTS
		 $true or $false
				
	 #>
			[CmdletBinding()]
			param(
				[Parameter(Mandatory = $true)]
				[ValidateNotNullOrEmpty()]
				[Microsoft.SqlServer.Management.Smo.Wmi.WmiSmoObject]$service
			)

		$timeout = new-timespan -Seconds 60
		$sw = [diagnostics.stopwatch]::StartNew()

		do {
			$service.Refresh()
			try { $service.Stop() } catch { Write-Warning "Having trouble stopping the SQL service. Let's try again." }
			
			Start-Sleep 2
		} 
		until ($service.ServiceState -eq "Stopped" -or $sw.elapsed -gt $timeout) 
		$status = $service.ServiceState
		
		if ($status -eq "Stopped") { return $true }
		else { return $false }
	}

	Function ConvertTo-PlainText {
	<#
		.SYNOPSIS
		Converts a SecureString to plain text

		.EXAMPLE
		ConvertTo-PlainText $password
		
		.OUTPUT
		String
				
	 #>
			[CmdletBinding()]
			param(
				[Parameter(Mandatory = $true)]
				[Security.SecureString]$Password
			)
			
		$marshal = [Runtime.InteropServices.Marshal]
		$plaintext = $marshal::PtrToStringAuto( $marshal::SecureStringToBSTR($Password) )
		return $plaintext
	}

	}

	PROCESS {
		# Check for SMO
		if ([Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") -eq $null )
		{ throw "Quitting: SMO Required. You can download it from http://goo.gl/R4yA6u" }

		[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
		
		# Is login a Windows login? If so, does it exist? 
		# Note: Windows logins do not need passwords set, because that's controlled by Windows.
		if ($login -match "\\") {
			$objUser = New-Object System.Security.Principal.NTAccount($login)
				try { $strSID = $objUser.Translate([System.Security.Principal.SecurityIdentifier]) 
			} catch { 
				throw "Not a valid Windows Login."
			}
		} else {
			# If it's not a Windows login, it's a SQL login, and needs a password.
			do {$Password = Read-Host -AsSecureString "Please enter a new password for $login" } while ( $Password.Length -eq 0 )
			$Password = ConvertTo-PlainText $Password
		}
		
		# Get instance name
		$instance = ($sqlserver.split("\"))[1]
		if ($instance -eq $null) { $instance = "MSSQLSERVER" }
		
		# Get IP address in case CNAME records were referenced in the SQL hostname
		Write-Output "Resolving IP address"
		try { 
			$hostentry = [System.Net.Dns]::GetHostEntry($sqlserver.Split("\")[0]) 
			$ipaddr = ($hostentry.AddressList | Where-Object { $PsItem -notlike '169.*' } | Select -First 1).IPAddressToString
		} catch { throw "Could not resolve SqlServer IP" }

		# If it's a clustered instance, Failover Clustering Management Tools are needed for restarting.
		if ($isclustered) {
			try { Get-ClusterResource -Cluster $sqlserver  | Out-Null }
			catch {
				throw "The SQL Server is clustered and you do not have Failover Clustering Management Tools installed or the cluster service cannot be accessed. `nTry: Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools"
			}
		}
		
		# Using the Single-User param on a cluster can stop it from working (this can be fixed in Startup Parameters in SQL Configuration Manager)
		# Make super sure $sqlserver isn't a cluster.
		
		try { $checkcluster = Get-Service -ComputerName $ipaddr | Where-Object { $_.Name -eq "ClusSvc" -and $_.Status -eq "Running" } }
		catch { throw "Can't check services. Check permissions and firewall." }
		
		if ($checkcluster -ne $null -and $isclustered -ne $true) {
			Write-Warning "This server has Cluster Services running but -IsClustered was not specified."
			Write-Warning "This script changes startup parameters and misconfigured parameters can prevent the service from starting."
			Write-Warning "Is $sqlserver a SQL Failover Cluster Instance? AlwaysOn doesn't count."
			$choices = New-Object Collections.ObjectModel.Collection[Management.Automation.Host.ChoiceDescription]
			$choices.Add((New-Object Management.Automation.Host.ChoiceDescription -ArgumentList '&Yes'))
			$choices.Add((New-Object Management.Automation.Host.ChoiceDescription -ArgumentList '&No'))
			$choices.Add((New-Object Management.Automation.Host.ChoiceDescription -ArgumentList '&Unknown'))
				
			switch ($Host.UI.PromptForChoice($null, $null, $choices, 2)) {
				0 { $isclustered = $true }
				2 { throw "Quitting." }
			}
		}
		
		# Get a managed computer instance and service accounts
		try { $sqlwmi = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $ipaddr } 
		catch  { throw "Cannot connect to ManagedComputer instance. Does a SQL Server exist on the destination? If so, is the firewall enabled?" }
		
		$sqlservice = ($sqlwmi.Services | Where-Object { $_.DisplayName -eq "SQL Server ($instance)" })
		$sqlagent = ($sqlwmi.Services | Where-Object { $_.DisplayName -eq "SQL Server Agent ($instance)" })
		
		if ($sqlservice -eq $null) { throw "Could not find SQL Service. Note that this script only supports SQL Server 2005 and above." }
		if ($sqlagent.ServiceState -eq "Running") { $startagent = $true }
		
		<#
		This is where the magic happens! 
		-T4022; Disables the startup procedures.
		-T3608: Recovers the master database only.
		-f: Starts an instance of SQL Server with minimal configuration. 
		-mReset-SqlSaPassword: Starts an instance of SQL Server in single-user mode and only allows this script to connect.
		
		Clusters don't support -m (since the cluster service itself connects immediately) or -f, so they are handled differently.
		#>
		
		Write-Output "Modifying SQL Server startup parameters"
		try {
			$startparams = $sqlservice.StartupParameters
			$newparams = '-T3608;-T4022'
			if ($isclustered -ne $true) { $newparams += ";-f;-mReset-SqlSaPassword" }
			$sqlservice.StartupParameters = "$newparams;$($startparams)"
			$sqlservice.Alter()
		} catch { throw "Could not modify startup parameters" }

		Write-Output "Attempting to restart SQL Services"
		
		if ($isclustered) {
			$clusteredservices = Get-ClusterResource -Cluster $ipaddr | Where-Object { $_.Name.StartsWith("SQL Server") -and $_.OwnerGroup -eq "SQL Server ($instance)" }
			try { $clusteredservices | Stop-ClusterResource -ErrorAction SilentlyContinue | Out-Null }
			catch {
				$sqlservice.StartupParameters = $startparams
				$sqlservice.Alter()
				$clusteredservices | Start-ClusterResource -ErrorAction SilentlyContinue | Out-Null
				return
			}
			$clusteredservices | Start-ClusterResource -ErrorAction SilentlyContinue | Out-Null
		} else {
			$stopped = Stop-WmiSqlService $sqlservice
			if ($stopped -eq $false) {
				Write-Warning "Could not stop the SQL Service. Reverting startup parameters and exiting."
				$sqlservice.StartupParameters = $startparams
				$sqlservice.Alter()
				return
			} else { Write-Output "Successfully stopped SQL service" }
			$started = Start-WmiSqlService $sqlservice
			if ($started -eq $false) {
				Write-Warning "SQL Server taking longer than 60 seconds to start. Reverting startup parameters, attempting restart and exiting."
				$sqlservice.StartupParameters = $startparams
				$sqlservice.Alter()
				Start-WmiSqlService $sqlservice | Out-Null
				return
			} else { Write-Output "Successfully started SQL service" }
		}

		Write-Output "Reconnecting to SQL instance"
		
		try {
			$connectionString = "Data Source=$sqlserver;Integrated Security=True;MultipleActiveResultSets=False;Application Name=Reset-SqlSaPassword"
			$server = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlserver
			$server.ConnectionContext.ConnectionString = $connectionString
			$server.ConnectionContext.Connect()
		} catch {
			Write-Warning "Could not connect. Server is in single-user mode and another user connected first. Try again."
			Write-Warning "Reverting startup parameters, restarting then exiting."
			$sqlservice.StartupParameters = $startparams
			$sqlservice.Alter()
			
			if ($isclustered) {
				$clusteredservices | Stop-ClusterResource  -ErrorAction SilentlyContinue | Out-Null
				$clusteredservices | Start-ClusterResource -ErrorAction SilentlyContinue | Out-Null
			} else {
				Stop-WmiSqlService $sqlservice | Out-Null
				Write-Output "Successfully stopped SQL service"
				Start-WmiSqlService $sqlservice | Out-Null
				Write-Output "Successfully started SQL service"
			}
			return
		}
		
		# Get login. If it doesn't exist, create it.
		
		Write-Output "Getting $login login"
		$sqllogin = $server.logins[$login]
		if ($sqllogin -eq $null) {
			Write-Warning "Login does not exist. Creating.."
			if ($login -match "\\") {	
				$sqllogin = New-Object Microsoft.SqlServer.Management.Smo.Login($server, $login)
				$sqllogin.LoginType = "WindowsUser"
				$sqllogin.Create()
			} else {
				$sqllogin = New-Object Microsoft.SqlServer.Management.Smo.Login($server, $login)
				$sqllogin.LoginType = "SqlLogin"
				$sqllogin.PasswordPolicyEnforced = $false
				$sqllogin.Create($password)
			}
		}
		
		# If $login is a SQL Login, Mixed mode authentication is required.
		# Also, here is where the password is actually reset.
		if ($sqllogin.LoginType -eq "SqlLogin") {
			Write-Output "Login is a SqlLogin, ensuring Mixed Mode Authentication is enabled"
			try {
				if ($server.LoginMode -ne "Mixed") { Write-Warning "Changing Login Mode to Mixed."; $server.LoginMode = "Mixed"; $server.Alter() }
				Write-Output "Setting new password"
				$sqllogin.PasswordPolicyEnforced = $false
				$sqllogin.Alter()
				$sqllogin.ChangePassword($password, $true, $false)
				Remove-Variable Password
			} catch { Write-Warning "Could not update password :|" }
		}
		
		if ($sqllogin.isDisabled) { Write-Warning "Login was disabled. Enabling."; $sqllogin.Enable(); $sqllogin.Alter() }
		
		Write-Output "Ensuring login exists within sysadmin role"
		$role = $server.roles["sysadmin"]
		if ($role.EnumMemberNames() -notcontains $login) { Write-Output "Adding login to sysadmins."; $role.AddMember($login) }
		
		Write-Output "Looking good!"
		Write-Output "Changing startup parameters back to normal"
		$sqlservice.StartupParameters = $startparams
		$sqlservice.Alter()

		Write-Output "Restarting SQL Server"
			if ($isclustered) {
				$clusteredservices | Stop-ClusterResource | Out-Null
				$clusteredservices | Start-ClusterResource | Out-Null
			} else {
				Stop-WmiSqlService $sqlservice | Out-Null
				Write-Output "Successfully stopped SQL service"
				Start-WmiSqlService $sqlservice | Out-Null
				Write-Output "Successfully started SQL service"
				if ($startagent = $true) { $sqlagent.Start() }
			}
		
		Write-Output "Success! You can now connect to $sqlserver now using $login"
	}

	END {
	if ($server.ConnectionContext.IsOpen -eq $true){
		$server.ConnectionContext.Disconnect()
	}
}
}